I am trying to get data to display in this fashion.

----------------Sept--Oct--Nov--Dec
Joe Smith -----12----34---56----24

Under each months name I have a text box linked to a CrossTab Query. The CrossTab Query allows me to put the scores from each month on the same line as a students name, however the Query only has data from months that were tested.

If only one child took a test in November, that month becomes a Column Heading on the CrossTab Query. In the Table or Report that references the Query, ever other child who didn't take the test will have a Null value for November. I like and want that.

If no child took a test in December, however, December doesn't even show up in the CrossTab Query. Therefore, my Form or Report that references December gets an error message. :(

A CrossTab Query has been the only way I have found to have a years worth of results show up on one record line with a child's name. Is there a better way to get the information?

Is there a way to supress the error message and just show a Null value?

Attached are two examples of the type of reports I am trying to generate. Hopefully this will help you to better understand what I want.
Tim

Hi Tim,

Make this table:

Numbers
Num, integer

records:
1
2
3
4
5
6
etc .... to 31

this way, you can use the table to show all months in a year, all days in a month, print multiple lables, etc

then, make a query that pulls the information by month

Name --> qry_StudentScores
SELECT PersonID, Month(TestDate) as Mnth, Format(TestDate, "mmmm") as MnthName, TestDate, Score, etc
FROM YourTablename

Then, make a query on top of that, something like this:

SELECT Numbers.Num, qry_StudentScores.*
FROM Numbers LEFT JOIN qry_StudentScores
ON Numbers.Num = qry_StudentScores.Mnth

use this query for your crosstab

Your crosstab could actually be based on Numbers and qry_StudentScores without need for this last query but I thought it may be easier to understand if you built these queries in order and then built your crosstab on the last one.

this document has a section on SQL and some crosstab examples:

Access Basics
http://www.utteraccess.com/forums/showflat...;Number=1220772
30-page Word document on Access Basics (for Programming) -- it doesn't cover VBA, but prepares you for it because it covers essentials in Access

Thanks for the idea, but I came up with an easier way to do this thanks to a friend of mine, without needing to use a CrossTab Query.

In the Query field, I put in:

Oct: (select studentScore from tblScores where InstructionLink = IDInstruction and TrimesterLink = 40)

Then I create one column for each month only needing to change the month name and the TrimesterLink Number. I also need to put the word DISTINCT in the SQL view.

This approach, unlike a cross tab querry allows me to put all the other fields I want in the Query so I can use it for a form.

Tim

you're welcome, Tim

Thanks for sharing your solution!

Welcome to Utter Access!

As the amount of data increases, the performance of all those subqueries is going to dramatically impact the query. It will get slower and slower.

I'd definitely use a crosstab here. Using the Column Headings property of the query will allow you to display all the months in any order that you want. Using the crosstab query with other queries will allow you to show 'all the fields you want' without the negative impact from the 12 subqueries.

Don't get me wrong, subqueries are great, but they can greatly diminish the performance, so be aware of what to expect down the road.