General discussion

Locked

Query from a Query

By paul.daniel ·
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;

This conversation is currently closed to new comments.

2 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

by Kiltie In reply to Query from a Query

It would help if you said what you are talking about

OS? Program?

I have absolutely no idea

Collapse -

Acsess presumably....yuck

by Tony Hopkinson In reply to Query from a Query

As prod, as convey, as [date] and as store_nbr
in the second part of the union aren't used in query2

Also you are grouping by in_store_nbr but not selecting it

Access is doing something funny here to help you out, either your column aliases are being ignored (I'd put them on the first part of the union, not the second) or query2 should fail for unknown column names.

I'd run each bit separately and make the thing as unambiguous as possible, I've a feeling this is a wood for trees problem.
Have a look at what you've actually got not what you thought you'd asked for.

Back to Software Forum
2 total posts (Page 1 of 1)  

Related Discussions

Related Forums