Varying Number of columns

[scotland]

Hey,

I have set up a report powered by a crosstab query that will grab the points scored by people in a month

Now the problem is this links to a points table so i want to have the points displayed below the relavent date.

the table is set up so that there is 1 record for every entry made. So if somebody did not show then there would be no entry for them on that date.

This is fine but if say i run the report for next month then there will be no entries for that date so i get an error message saying.

The Microsoft Jet database engine does not recognize '[05/12/06]' as a valid field name or expression

Which is becasue the crosstab query does not make the field as there are no enreiws for this date. What can i do short of making lots of blank entries with all the dates in?

Thanks in advance

 

[gemma-the-husky]

I don't quite understand how your final query is structured, but you probably need to change your join type to include everyting on one side of the join. Right click the join line, and select the appropriate join type.

not sure which join type this is, there are left right inner and outer - better men than me here on sql statements.

 

[pbaldy]

I use a method from the Access Developer's Handbook that I think is better, but there's also this:

http://support.microsoft.com/kb/328320/en-us

 

[scotland]

cheers pbaldy that worked great. the only problem i have is that i have some grouping (people are in different groups) and i want all the peole to be grouped together.

Is there a clever way of doing this or just make seperate quires for each group?

 

[pbaldy]

Presuming the group is already in the query, doesn't putting it in the report's Sorting and Grouping make it come out the way you want?

 

[scotland]

No, i now have it working with a bit of jiggery pokery and sorting the query. However if i select "keep together" then it does not work and after a few entries it jus repeats the same record again untill the end.

 

[pbaldy]

It repeats records? I can't duplicate that. Can you post a sample db?

 

[scotland]

Yea as I say it only happens when I select keep together in the grouping options.

I have modified the code slightly so maybe i did something wrong.

Also please excuse the sloppy code im fairly new to VBA