SubQuery

[Abe Katz]

Hello All,

I need a list from All the Items in the Inventory, the Last Date each item
was sold, and the qty sold on that date.
Doug Steele told me to use a subquery, it works fine with one table but when
I have a Header and a Detail table (the date is in the Header) I cant get it
to work.

The following code works fine with one table (date and StyleNo, Qty are in
the same table).
I need the same query with an Invoice table, where the Date is in the Header
and StyleNo and Qty is the Detail table.
Can any body please figure out how to do it?
Thank you very much I really appreciated.

SELECT A.StyleNo, A.DateRcv AS DateLastPur, Sum(A.Qty) AS QtyPur
FROM [POrder RcvdDT] AS A
GROUP BY A.StyleNo, A.DateRcv
HAVING (((A.StyleNo) Between "A100" And "B100") AND ((A.DateRcv) In (SELECT
Max(DateRcv) FROM [POrder RcvdDT] WHERE StyleNo=A.StyleNo)));

Abe

 

[Stefan Hoffmann]

hi Abe,

Doug Steele told me to use a subquery, it works fine with one table but when
I have a Header and a Detail table (the date is in the Header) I cant get it
to work.

Create a query to combine these two tables, e.g.

SELECT *
FROM Header H
INNER JOIN Detail D ON D.IdHeader = H.Id

Use this query as you have used the table before in Doug's approach.
Instead of saving it as an extra query, you may simple substitute the
table name in your working query with

(the query from above) Q

The parentheses are necessary.



mfG
--> stefan <--