reporting a value that isn't in the query

[amandabf]

I'm doing a report that counts (for example) the number of bananas, apples, and oranges sold in a given month. My query is a crosstab query. So, of course, if I didn't sell any oranges the category wouldn't show up on my report at all. How would I make it so my report would say 0 oranges were sold?

 

[Databaseguy]

Right click in the query design. Under Column Headings, type in the name of the vaues that will be returned, enclosed in quotation marks, seperated by semi-colons.

Make sure that the values typed EXACTLY match the names of the values. his can be a pain to administer, however, when the column names are frequently being changed, added to or delted from the query.... Tyrone Lumley
augerinn@gte.net

http://server47.hypermart.net/databaseguy

 

[amandabf]

I've thought about doing that, but I need oranges to be a row because I'm counting more than just how many were sold. Anymore ideas?

 

[MichaelRed]

I don't like using the ColumnHeadings property, as it then ALSO limits the results. The Alternative is to have a simple select query for the Source of the columns. It just includes the group of possible column headings. Do an Outter join of this to the actual data source and use THIS as the piviot column. ALL values listed here will be in the columns -regardless of wheather there is any data to report- so the totals column will have your Zero.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over