When I create my Crosstab query, the default is that any field without the row/column combination shows up as a blank value. Is it possible to have those fields show up as zero?
Thanks.
When I create my Crosstab query, the default is that any field without the row/column combination shows up as a blank value. Is it possible to have those fields show up as zero?
Thanks.
You have to handle those values before you turn it into a crosstab query. Usually you do this with the Nz function.
HTH
I understand how the NZ function would work if I had blank values in my first table, but I'm not sure how it will work with the crosstab.
For example, if this is my table:
Section Product Sales
East Footballs 10
East Baseballs 6
West Footballs 5
South Baseballs 4
My crosstab query returns this:
Product East South West
Baseballs 6 4
Footballs 10 5
The intersection of South and Footballs is null because that combination didn't exist in the table. Without seeding the table with all possible section/product combinations, how can I get that to appear as zero on my crosstab query?
Hope this makes my question more clear. Thanks again for your help.
Sorry, it doesn't appear my texted displayed well enough to explain my thoughts. I've enclosed an attachment to make it more clear.
Without seeding the table you can fake that with a query using empty values which you can UNION with your query before you turn it into a crosstab query.
HTH