Show Zeros in Query

[ryguy7272]

I did a google search and didn't get an answer to my question. Also, did a
search in the Queries group of the Access DG, still didn't find a SOLUTION.
My question is as follows... How can I display all records, even those that
are zero?

My SQL is here:
SELECT tblRegion.SalesRegion, Sum(tblRegion.Q308) AS SumOfQ308,
Sum(tblRegion.Q408) AS SumOfQ408
FROM tblRegion
GROUP BY tblRegion.SalesRegion, tblRegion.inventoryClass
HAVING (((tblRegion.SalesRegion)="All" Or (tblRegion.SalesRegion)="Alliance"
Or (tblRegion.SalesRegion)="East" Or (tblRegion.SalesRegion)="Inside Sales"
Or (tblRegion.SalesRegion)="Unassigned" Or (tblRegion.SalesRegion)="West")
AND ((tblRegion.inventoryClass)="Class2"));



Thanks so much,
Ryan---

 

[KARL DEWEY]

How can I display all records, even those that are zero?
I do not follow - your query will show those that sum to zero IF they are a
region you specified AND inventoryClass="Class2".

Post sample data with results and expected results.

 

[ryguy7272]

Thanks for looking at my post Karl. Well, I guess the best way to explain,
is that sometimes a region, such as East or West, will not have any data, and
in the query results, these items will not be displayed. I was hoping to
display an item, with a zero in the query field, even if there is no data
there, kind of like Excel would do.

Thanks,
Ryan---

 

[KARL DEWEY]

The easiest way due to your large number of stated regions is to build a
table of regions with a Yes/No field to check is it is to be included in the
report.

Then LEFT JOIN it in your query as below --
SELECT tblRegion.SalesRegion, Sum(tblRegion.Q308) AS SumOfQ308,
Sum(tblRegion.Q408) AS SumOfQ408
FROM RegionTable LEFT JOIN tblRegion ON RegionTable.Region =
tblRegion.SalesRegion
WHERE tblRegion.inventoryClass)="Class2" AND RegionTable.Include = -1
GROUP BY tblRegion.SalesRegion, tblRegion.inventoryClass;

 

[ryguy7272]

I'm back to square one Karl. Can you give me detailed instructions about
creating the table and doing a left-join from the original table to the new
table? I tried a bunch of things, including adding the new table and using
Yes/No fields, but nothing worked. Alternatively, I was trying to use the
Expression Builder to do something like this:
Sum(NZ(tblRegion!inventoryClass,0))

I didn't get very far with that method either.

Can you assist with one of these options?

Thanks so much!
Ryan---

 

[ryguy7272]

Thought about it more over past few days, and I got a new perspective on
things with some help from others on this DG. Solution is here:
http://www.microsoft.com/office/com...a03-1caee796cf70&cat=&lang=en&cr=US&sloc=&p=1