Display all results, even zeros, nulls, and blanks

[ryguy7272]

I am trying to do something that not be possible. I posed a question a few
days ago, and received some good help, but couldn’t quite figure out how to
get a solution worked out. Anyway, I guess the best way to explain the issue
is to say that I have a table with sales data for Q3 and Q4. I have another
table with names of regions (All, East, West, etc.). I am trying to find a
way to display all regions in a query. If there are some sales reported,
they are displayed in the query results, but if there are no sales (i.e., a
value of zero), the region is not displayed in the sales results. I want to
figure out a way to display all the results, even the zeros, or nulls, or
blanks, or whatever, because this will be exported to Excel and in Excel I
need to see these zeros. Can anyone offer any suggestions as to how to
display all the results from a table when the results may be zero. By the
way, I tried a left join and that doesn’t do what I wanted to do.

SQL Here:
SELECT tblWandaRegion.SalesRegion, Sum(tblWandaRegion.Q308) AS SumOfQ308,
Sum(tblWandaRegion.Q408) AS SumOfQ408, tblWandaRegion.inventoryClass
FROM RegionTable LEFT JOIN tblWandaRegion ON RegionTable.Regions =
tblWandaRegion.SalesRegion
GROUP BY tblWandaRegion.SalesRegion, tblWandaRegion.inventoryClass
HAVING (((tblWandaRegion.SalesRegion)="All" Or
(tblWandaRegion.SalesRegion)="Alliance" Or
(tblWandaRegion.SalesRegion)="East" Or (tblWandaRegion.SalesRegion)="Inside
Sales" Or (tblWandaRegion.SalesRegion)="Unassigned" Or
(tblWandaRegion.SalesRegion)="West") AND
((tblWandaRegion.inventoryClass)="Class2"));

Thanks so much,
Ryan---

 

[bcap]

Right idea, wrong implementation. Something like this should do it
(warning: untested!):

SELECT RegionTable.Regions, Sum(tblWandaRegion.Q308) AS SumOfQ308,
Sum(tblWandaRegion.Q408) AS SumOfQ408, tblWandaRegion.inventoryClass
FROM RegionTable LEFT JOIN tblWandaRegion ON RegionTable.Regions =
tblWandaRegion.SalesRegion
GROUP BY RegionTable.Regions, tblWandaRegion.inventoryClass
HAVING (((RegionTable.Regions)="All" Or
(RegionTable.Regions)="Alliance" Or
(RegionTable.Regions)="East" Or (RegionTable.Regions)="Inside
Sales" Or (RegionTable.Regions)="Unassigned" Or
(RegionTable.Regions)="West") AND
(tblWandaRegion.inventoryClass="Class2" OR tblWandaRegion.inventoryClass IS
NULL));

Or, logically equivalent but neater:

SELECT RegionTable.Regions, Sum(tblWandaRegion.Q308) AS SumOfQ308,
Sum(tblWandaRegion.Q408) AS SumOfQ408, tblWandaRegion.inventoryClass
FROM RegionTable LEFT JOIN tblWandaRegion ON (RegionTable.Regions =
tblWandaRegion.SalesRegion AND tblWandaRegion.inventoryClass="Class2")
GROUP BY RegionTable.Regions, tblWandaRegion.inventoryClass
HAVING RegionTable.Regions IN ("All","Alliance","East","Inside
Sales","Unassigned","West")

 

[John W. Vinson]

I am trying to do something that not be possible. I posed a question a few
days ago, and received some good help, but couldn’t quite figure out how to
get a solution worked out. Anyway, I guess the best way to explain the issue
is to say that I have a table with sales data for Q3 and Q4. I have another
table with names of regions (All, East, West, etc.). I am trying to find a
way to display all regions in a query. If there are some sales reported,
they are displayed in the query results, but if there are no sales (i.e., a
value of zero), the region is not displayed in the sales results. I want to
figure out a way to display all the results, even the zeros, or nulls, or
blanks, or whatever, because this will be exported to Excel and in Excel I
need to see these zeros. Can anyone offer any suggestions as to how to
display all the results from a table when the results may be zero. By the
way, I tried a left join and that doesn’t do what I wanted to do.

The problem is that if there is no record in tblWandaRegion, then all of the
fields to which you are assigning criteria will be NULL - and therefore the
criterion will fail, and you won't see the record. You'll need to add the
option that the field IS NULL to pick up the records from RegionTable which
don't have a match.

You should also move the HAVING clause into a WHERE; WHERE is applied before
the records are summed, HAVING sums all the records in the entire table and
then discards the results that don't match the criteria. For multiple OR's the
IN() criterion is shorter, more efficient, and easier to read. Try


SELECT tblWandaRegion.SalesRegion, Sum(tblWandaRegion.Q308) AS SumOfQ308,
Sum(tblWandaRegion.Q408) AS SumOfQ408, tblWandaRegion.inventoryClass
FROM RegionTable LEFT JOIN tblWandaRegion ON RegionTable.Regions =
tblWandaRegion.SalesRegion
WHERE
(tblWandaRegion.SalesRegion
IN ("All","Alliance","East","Inside Sales","Unassigned","West")
OR tblWandaRegion.SalesRegion IS NULL
AND
(tblWandaRegion.inventoryClass="Class2"
OR tblWandaRegion.InventoryClass IS NULL)
GROUP BY tblWandaRegion.SalesRegion, tblWandaRegion.inventoryClass;

 

[ryguy7272]

Thanks John! Your help and insight was invaluable. When I saw the solution
I recognized my mistake immediately -- I never looked for Is Null. Also,
thanks bcap! The second SQL statement worked (somehow the first didn't
work). I had to change my RegionTable a bit too. That was another thing
that was preventing it from working right. I got that straightened out and
everything is fine now. For others who encounter a similar problem, search
for your requisite criteria and Is Null!!

Thanks guys,
Ryan---