Query1 contains two queries in a Union join
Query2 references Query1
The result of Query2 is not as expected.
If I use the result of Query1 to make a table then
use query2 against the table the result is correct.
Are you unable to refernce a Union Join query.
Is referencing a query was the same as referencing a temp table.
SELECT
Itmm.PROD_GROUP,
Itmm.CONVEY_FLAG,
Sd.IN_STORE_DATE,
Sd.STORE_NBR,
0 as Orig_Orders,
Sum(([Sd]![REQD_QTY]/[Itmm]![STD_PACK_QTY])) AS Purged
FROM ITEM_MASTER AS Itmm
INNER JOIN STORE_DISTRO AS Sd
ON Itmm.SKU_ID = Sd.SKU_ID
WHERE (((Sd.IN_STORE_DATE)>Date())
AND ((Sd.INVN_TYPE)=”F”)
AND ((Sd.PRTY_CODE)<"50000000")
AND ((Sd.STAT_CODE)=0)
AND ((Sd.RETL_PRICE)=1))
GROUP BY Itmm.PROD_GROUP,
Itmm.CONVEY_FLAG,
Sd.IN_STORE_DATE,
Sd.STORE_NBR,
Sd.INVN_TYPE,
Sd.STAT_CODE,
Sd.RETL_PRICE;
UNION ALL
SELECT
Itmm.PROD_GROUP AS prod,
Itmm.CONVEY_FLAG AS convey,
Sd.IN_STORE_DATE AS [date],
Sd.STORE_NBR AS store_nbr,
Sum(CInt([Sd]![REQD_QTY]/[Itmm]![STD_PACK_QTY])) AS Orig_Orders,
0 as Purged
FROM ITEM_MASTER AS Itmm
INNER JOIN STORE_DISTRO AS Sd
ON Itmm.SKU_ID = Sd.SKU_ID
WHERE (((Sd.PRTY_CODE)<"50000000")
AND ((Sd.IN_STORE_DATE)>Date()))
and (((Sd.INVN_TYPE)=”F”))
GROUP BY
Itmm.PROD_GROUP,
Itmm.CONVEY_FLAG,
Sd.IN_STORE_DATE,
Sd.STORE_NBR,0;
Query2
SELECT [100_100_012 Union].PROD_GROUP, [100_100_012 Union].CONVEY_FLAG,
[100_100_012 Union].IN_STORE_DATE,
Sum([100_100_012 Union].Orig_Orders) AS SumOfOrig_Orders,
Sum([100_100_012 Union].Purged) AS SumOfPurged
FROM [100_100_012 Union]
GROUP BY
[100_100_012 Union].PROD_GROUP,
[100_100_012 Union].CONVEY_FLAG,
[100_100_012 Union].IN_STORE_DATE,
[100_100_012 Union].STORE_NBR;