Re: Crosstab-force rows
- From: Phil Smith <phil@xxxxxxxxxxx>
- Date: Wed, 31 Jan 2007 13:11:20 -0800
For instance, you want sales of grapes, apples and oranges.
Grapes 500
Apples 400
Oranges 300
but last week, you sold no apples. You get
Grapes 500
Oranges 300
You WANT
Grapes 500
Apples 0
Oranges 300
When I have done it, I have created a dummy record for each fruit, with a bizzare date, like 1/1/2095, and a count of zero.
Then in addition to all of my criteria, (pulled from a select form or some such, on a seperate Criteria line I put that date.
This guarentees the query will find the dummy record, giving you your row even if there were no sales that week, and the zero does not otherwise efect your sums. You have to make sure that your Dummy selector, like the bizzzare date, is not part of a group by.
Ugly, but it works. Maybe someone will have something prettier.
WSC wrote:
I have a crosstab set up but when the criteria doesnt match, it doesnt pull in any data which is correct. However, i would like to show zeroes instead of blanks. How can I force the zeroes to show in the results?.
I tried doing if statements in all the fields that if it = "", then put 0, but that didnt work.
thanks!
- Prev by Date: Update Query, updating wrong table
- Next by Date: Count as zero when no data
- Previous by thread: Update Query, updating wrong table
- Next by thread: Count as zero when no data
- Index(es):
Relevant Pages
|
|
