SubQuery

  • Thread starter Thread starter Abe Katz
  • Start date Start date
A

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
 
hi Abe,

Abe said:
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.
Click to expand...
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 <--
 
Back
Top