If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
SubQuery
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 |
#2
|
|||
|
|||
SubQuery
hi Abe,
Abe Katz wrote: 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 -- |
Thread Tools | |
Display Modes | |
|
|