Prev: bookmark bug access 2003
Next: must have 2 records in subform
From: Abe Katz on 9 Nov 2009 15:42
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 [tblPOrder RcvdDT] AS A
GROUP BY A.StyleNo, A.DateRcv
HAVING (((A.StyleNo) Between "A100" And "B100") AND ((A.DateRcv) In (SELECT
Max(DateRcv) FROM [tblPOrder RcvdDT] WHERE StyleNo=A.StyleNo)));

Abe


 | 
Pages: 1
Prev: bookmark bug access 2003
Next: must have 2 records in subform