bishopkd
asked on
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_M R_Mfg_Prin t),0)) AS dPa,
Sum(Iif([Trim Code] IN ('B'),(Actl_MR_Bind+Actl_M R_Mfg_Prin t),0)) AS dPb,
Sum(Iif([Trim Code] NOT IN ('A','B'),(Actl_MR_Bind+Ac tl_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_Pr int),0)) AS dPa,
Sum(Iif([Trim Code] IN ('B'),(Actl_Run_Bind+Actl_ Run_Mfg_Pr int) ,0)) AS dPb,
Sum(Iif([Trim Code] NOT IN ('A','B'),(Actl_Run_Bind+A ctl_Run_Mf g_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_Bin d),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.
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_M
Sum(Iif([Trim Code] IN ('B'),(Actl_MR_Bind+Actl_M
Sum(Iif([Trim Code] NOT IN ('A','B'),(Actl_MR_Bind+Ac
FROM HDB
WHERE …
UNION SELECT
“2” AS Sort,
"TR " AS [Fmt],
Sum(Iif([Trim Code] IN ('A'),(Actl_Run_Bind+Actl_
Sum(Iif([Trim Code] IN ('B'),(Actl_Run_Bind+Actl_
Sum(Iif([Trim Code] NOT IN ('A','B'),(Actl_Run_Bind+A
FROM HDB
WHERE …
UNION SELECT
“3” AS Sort,
"MM " AS [Fmt],
Sum(Iif([Trim Code] IN ('A'),(Actl_CtnPal_Bind),0
Sum(Iif([Trim Code] IN ('B'),(Actl_CtnPal_Bind) ,0)) AS dPb,
Sum(Iif([Trim Code] NOT IN ('A','B'),(Actl_CtnPal_Bin
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.
ASKER
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_Pr int) * ([Enter HCPa]),0)) AS dPa,
Sum(Iif([Trim Code] IN ('B'),(Actl_Plt_Bind+Actl_ Plt_Mfg_Pr int) * ([Enter HCPb]),0)) AS dPb,
Sum(Iif([Trim Code] NOT IN ('A','B'),(Actl_Plt_Bind+A ctl_Plt_Mf g_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_Pr int) * ([Enter TRPa]),0)) AS dPa,
Sum(Iif([Trim Code] IN ('B'),(Actl_Plt_Bind+Actl_ Plt_Mfg_Pr int) * ([Enter TRPb]),0)) AS dPb,
Sum(Iif([Trim Code] NOT IN ('A','B'),(Actl_Plt_Bind+A ctl_Plt_Mf g_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]
SELECT
tblFMT.format,
"HC-disP" AS [Fmt],
Sum(Iif([Trim Code] IN ('A'),(Actl_Plt_Bind+Actl_
Sum(Iif([Trim Code] IN ('B'),(Actl_Plt_Bind+Actl_
Sum(Iif([Trim Code] NOT IN ('A','B'),(Actl_Plt_Bind+A
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_
Sum(Iif([Trim Code] IN ('B'),(Actl_Plt_Bind+Actl_
Sum(Iif([Trim Code] NOT IN ('A','B'),(Actl_Plt_Bind+A
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]
Resulting query - qry1, table qry - your union query, products - list of all products
DB26828504.mdb
DB26828504.mdb
ASKER
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...
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...
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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)