Forcing 0s in Access SQL

Hi.  I have a mini- app written in Access (VBA) in which I loop through about 15 queries and write the results to an Excel template.  The template reads the raw output on a back tab and displays it on visible tabs in a presentable type way through cell references to the data tab.

My issue is this:

In a query similar to this

SELECT
“1” AS Sort,
"HC " AS [Fmt],
Sum(Iif([Trim Code] IN ('A'),(Actl_MR_Bind+Actl_MR_Mfg_Print),0))  AS dPa,
Sum(Iif([Trim Code] IN ('B'),(Actl_MR_Bind+Actl_MR_Mfg_Print),0))  AS dPb,
Sum(Iif([Trim Code] NOT IN ('A','B'),(Actl_MR_Bind+Actl_MR_Mfg_Print),0))  AS dPo
FROM HDB
WHERE …

UNION SELECT
“2” AS Sort,
"TR " AS [Fmt],
Sum(Iif([Trim Code] IN ('A'),(Actl_Run_Bind+Actl_Run_Mfg_Print),0))  AS dPa,
Sum(Iif([Trim Code] IN ('B'),(Actl_Run_Bind+Actl_Run_Mfg_Print) ,0))  AS dPb,
Sum(Iif([Trim Code] NOT IN ('A','B'),(Actl_Run_Bind+Actl_Run_Mfg_Print),0))  AS dPo
FROM HDB
WHERE …

UNION SELECT
“3” AS Sort,
"MM " AS [Fmt],
Sum(Iif([Trim Code] IN ('A'),(Actl_CtnPal_Bind),0))  AS dPa,
Sum(Iif([Trim Code] IN ('B'),(Actl_CtnPal_Bind) ,0))  AS dPb,
Sum(Iif([Trim Code] NOT IN ('A','B'),(Actl_CtnPal_Bind),0))  AS dPo
FROM HDB
WHERE …

When run  it for a vendor who produces HC (1) and MM (3) but no TR (2) the results write out as:
1  HC    100   200  100
3  MM   200   100  100

And if a vendor has no data, the query results do not write out to the spreadsheet at all and writes the next query over its intended destination cells.

So, my question is:  Is there a way to instead output the query results like this:

1  HC    100   200  100
2  TR      0        0       0
3  MM   200   100  100

Or do I need to find a better way to do this all around?

Many thanks.
bishopkdAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
als315Connect With a Mentor Commented:
All where should be in separate query. Create additional query for Products
0
 
als315Commented:
Yes!
Add query (or table) with all products you like to see (HC,TR,MM) and join it in query with your union query (All from created query/table and only existed from union query)
0
 
bishopkdAuthor Commented:
I love the simplicity of this.  I'm not getting it to work though.  What am I doing wrong?

SELECT
tblFMT.format,
"HC-disP" AS [Fmt],
Sum(Iif([Trim Code] IN ('A'),(Actl_Plt_Bind+Actl_Plt_Mfg_Print) * ([Enter HCPa]),0))  AS dPa,
Sum(Iif([Trim Code] IN ('B'),(Actl_Plt_Bind+Actl_Plt_Mfg_Print) * ([Enter HCPb]),0))  AS dPb,
Sum(Iif([Trim Code] NOT IN ('A','B'),(Actl_Plt_Bind+Actl_Plt_Mfg_Print) * ([Enter HCPo]),0))  AS dPo
FROM HDB RIGHT JOIN tblFMT
ON HDB.fmt = tblFMT.format
WHERE Year([ProdOrdr Del Date]) = [Enter Year] And
Month([ProdOrdr Del Date]) BETWEEN ([Enter SMonth]) and ([Enter EMonth]) And
HDB.Binder In ([Enter Binder]) AND
HDB.[Contract Narrow Web] In ('Y') AND
HDB.fmt in ('HC')
GROUP BY tblFMT.format,HDB.fmt
ORDER by [fmt]

UNION

SELECT
tblFMT.format,
"TR-disP" AS [Fmt],
Sum(Iif([Trim Code] IN ('A'),(Actl_Plt_Bind+Actl_Plt_Mfg_Print) * ([Enter TRPa]),0))  AS dPa,
Sum(Iif([Trim Code] IN ('B'),(Actl_Plt_Bind+Actl_Plt_Mfg_Print) * ([Enter TRPb]),0))  AS dPb,
Sum(Iif([Trim Code] NOT IN ('A','B'),(Actl_Plt_Bind+Actl_Plt_Mfg_Print) * ([Enter TRPo]),0))  AS dPo
FROM HDB RIGHT JOIN tblFMT
ON HDB.fmt = tblFMT.format
WHERE Year([ProdOrdr Del Date]) = [Enter Year] And
Month([ProdOrdr Del Date]) BETWEEN ([Enter SMonth]) and ([Enter EMonth]) And
HDB.Binder In ([Enter Binder]) AND
HDB.[Contract Narrow Web] In ('Y') AND
HDB.fmt in ('TR')
GROUP BY tblFMT.format,HDB.fmt
ORDER by [fmt]
0
 
als315Commented:
Resulting query - qry1, table qry - your union query, products - list of all products
DB26828504.mdb
0
 
bishopkdAuthor Commented:
This works when I do not have a WHERE Statement.
If the Qry table had a Printer field and your SQL had to look like this:

SELECT
Qry.Product,
nz(sum([Qry]![F1]),0) AS F1,
nz(Sum([Qry]![F2]),0) AS F2,
nz(Sum([Qry]![F3]),0) AS F3
FROM Products LEFT JOIN Qry ON Products.Product = Qry.Product
WHERE Products.Printer IN ('OPM')
Group by Qry.Product;

It no longer pulls in the TR with 0s...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.