Union or better way.

[kevin28]

Hi,

I did created some queries that are helping me pull some information.
Can someone have a look and advice if it's a way to do this same in one query or i need have at least two?

I need find all SKU values that are not showing in table tblDC2Pick and the difference between "Date_Pick" and today's date Date() is bigger then 2.

I believe that it's a better way to achieve that in more clean way as below is total mess...


Thank you


The structure of the table

tblDC2Pick
SKU
Pick_Units
Date_Pick

tblIO
SKU
PO
Qty
Putaway_Time
Av


Query1

SELECT tblIO.SKU, qry_SumQty.CountOfQty, tblIO.PO, tblIO.Putaway_Time, tblIO.AV
FROM qry_SumQty INNER JOIN (tblDC2Pick RIGHT JOIN tblIO ON tblDC2Pick.SKU = tblIO.SKU) ON qry_SumQty.SKU = tblIO.SKU
WHERE (((tblDC2Pick.Date_Pick) Is Null));

Query2

SELECT DISTINCT tblIO.SKU, qry_SumQty.CountOfQty, tblIO.PO, tblIO.Putaway_Time, tblIO.AV
FROM tblIO INNER JOIN qry_SumQty ON tblIO.SKU = qry_SumQty.SKU
WHERE (((Exists (SELECT tblDC2Pick.SKU
    FROM tblDC2Pick
    WHERE tblDC2Pick.SKU = tblIO.SKU
        AND tblDC2Pick.SKU = tblIO.SKU
))=False))
GROUP BY qry_SumQty.CountOfQty, tblIO.PO, tblIO.Putaway_Time, tblIO.AV;

qry_SumQty

SELECT tblIO.SKU AS SKU, Sum(tblIO.[Qty Stock]) AS CountOfQty
FROM tblIO
GROUP BY tblIO.SKU;

[Robeen]

If you have not figured this out yet, can you explain in different words what you need to do?

[kevin28]

Ok Robeen,

I need have as results in this same query:
- All SKU's that are not showing in table tblDC2Pick
- SKU's that get not picked for over two day's. Difference between today's date and "Date_Pick" value is bigger then two.

I hope that it's more clear for you... I can't combine this two things into one query.