Running a report based on a crosstab query

[chrisaroundtown]

Hi,

I have a Table called CSRLog. The table contains orders taken by my users. The fields in the table include [date], [program], [order no.], [product], [supplier] etc.

I have created a crosstab query called OrdersByProgram. The query tells me how many orders there are for each date by program. The row headings are dates, the column headings are the programs and the data is a simple count of orders. The query will only show row and column headings based on the data in the table. For example, if there are no orders for the program Bluefish the query will not display a column called Bluefish.

Based on this query I have a report. I know that there are five programs and the report should tell me how many orders each program has recieved per day. The problem is that in the report I have fields for each program but where there is no orders for one program I get an error message as it doesn't appear in the query. For example, if there are no orders for the program Bluefish in the table, there will be no column in the query called Bluefish (as there are no orders). Therefore the report (which has a field in in for Bluefish) doesn't recognise the name Bluefish in the query. I get the error 'Microsoft Access cannot recognise the field [Bluefish] etc.'

Please help, let me know if what I am saying makes no sense at all.

Thanks
Chris.

 

[67peshawar294]

Chris,
Same thing happened to me. But I only have three fields to worry about. My report covers the present week, and on Monday the previous week (because the Friday sales are not posted until Monday, but they still have Fridays date.)
The only way I found to solve this was to enter a "dummy" entry with $0.00 as the purchase. Each Tuesday I change the date to the day before (Monday). And I subtract 1 from the "count" for each Doctor.
jim

 

[Darrylles]

Hi,

You will get this error, because the crosstab is using field 'values' as column/row headers.

It is like you saying:

"SELECT 'null' from tablename WHERE 'null' = 'xyz'"

instead of...

"SELECT fieldx from tablename WHERE fieldx = 'xyx'"

I think I got around this by feeding the crosstab with 'correct' data from a straight select query (filtering out the fields that had null values).

Regards,

Darrylle

"Never argue with an idiot, he'll bring you down to his level - then beat you with experience." darrylles@totalise.co.uk

 

[PWise]

in you crosstab query properties under column headings enter "program1","program2","program3", to force the query to display the column heading even if there ane no entrys for that column

 

[chrisaroundtown]

Thanks pwise,

That worked great, exactly what I was after.

Thanks
Chris.

 

[67peshawar294]

pwise,
Thanks for the tip, it also worked great on mine.
jim