I have query to find the last transaction for the purchase of an item. The date is stored in a purchases header table and the item and the date are stored in a detail table. (This allowed the date to not have to be re-keyed on each item.) The query using MAX on the date field returns the last transaction just fine. But when I also add the cost field to the query, it returns other dates.

How can I get the cost to pull only from the transactions limited to the MAX criteria on the date field?

SQL code is:

SELECT PurchasesInvoiceDetail.ItemID, Max(PurchasesInvoice.TranDate) AS MaxOfTranDate, PurchasesInvoiceDetail.Cost
FROM PurchasesInvoice INNER JOIN PurchasesInvoiceDetail ON PurchasesInvoice.Reference = PurchasesInvoiceDetail.Reference
GROUP BY PurchasesInvoiceDetail.ItemID, PurchasesInvoiceDetail.Cost;

Thanks for the help

What about this ?
SELECT ItemID
, (SELECT Max(TranDate) FROM PurchasesInvoice WHERE Reference = PurchasesInvoiceDetail.Reference) AS MaxOfTranDate
, Cost
FROM PurchasesInvoiceDetail

Hope This Helps, PH.
FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?

Thanks for the idea PHV, but it did not work either.

I only have 751 unique items in the master table, so this desired query should only return a max of 751 records. Each item has at least one date; (1) the date of the beginning qty or (2) the date of a subsequent purchase.

I get 719 records when I perform the following query.

SELECT PurchasesInvoiceDetail.ItemID, Max(PurchasesInvoice.TranDate) AS MaxOfTranDate
FROM PurchasesInvoice INNER JOIN PurchasesInvoiceDetail ON PurchasesInvoice.Reference = PurchasesInvoiceDetail.Reference
GROUP BY PurchasesInvoiceDetail.ItemID;

I get 1,096 records when I add the cost field. I can see several instances where the item is listed at least twice and has two different dates with different costs. The query is

SELECT PurchasesInvoiceDetail.ItemID, Max(PurchasesInvoice.TranDate) AS MaxOfTranDate, PurchasesInvoiceDetail.Cost
FROM PurchasesInvoice INNER JOIN PurchasesInvoiceDetail ON PurchasesInvoice.Reference = PurchasesInvoiceDetail.Reference
GROUP BY PurchasesInvoiceDetail.ItemID, PurchasesInvoiceDetail.Cost;

I get 1,295 records with the query statement supplied by PHV.

Additional ideas?

Thanks

Please provide some sample records from both tables with the desired output. You only need to provide the relevant fields.

Duane
Hook'D on Access
MS Access MVP

And this ?
SELECT ItemID, [your date field] AS MaxOfTranDate, Cost
FROM PurchasesInvoiceDetail
WHERE [your date field]=(SELECT Max(TranDate) FROM PurchasesInvoice WHERE Reference = PurchasesInvoiceDetail.Reference)

Hope This Helps, PH.
FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?

Here are some records.

Item ID            Item Description        TranDate        Reference        Quantity        Cost
FBPC 1X.5            FBG PC 1 X 1/2         12/31/2011        Beg Bal        2544        0.79
FBPC 1X1            FBG PC 1 X 1            12/31/2011        Beg Bal        1035        0.92
FBPC 1X1            FBG PC 1 X 1            1/16/2012        6251409 SO        30        1.30
FBPC 1X1            FBG PC 1 X 1            2/8/2012            625943        1890        0.93
FBPC 1X1            FBG PC 1 X 1            4/9/2012            634279        1620        0.99
FBPC 1X1.5        FBG PC 1 X 1-1/2        12/31/2011        Beg Bal        666        1.91
FBPC 1X1.5        FBG PC 1 X 1-1/2        3/23/2012        6299385 SO        18        2.25
FBPC 1X1.5        FBG PC 1 X 1-1/2        3/9/2012            630308        600        1.62
FBPC 1X2            FBG PC 1 X 2            12/31/2011        Beg Bal        279        2.99
FBPC 1X2            FBG PC 1 X 2            1/16/2012        6251409 SO        42        3.57

Tables involved are
1. ItemMaster 2. PurchaseInvoice 3. PurchaseInvoiceDetail

ItemID appears in both ItemMaster and PurchaseInvoiceDetail
ItemDescription appears in ItemMaster
TranDate appears in PurchaseInvoice
Reference appears in PurchaseInvoice and PurchaseInvoiceDetail
Quantity and Cost appear in PurchaseInvoiceDetail

ItemMaster and PurchaseInvoiceDetail are joined with ItemID
PurchaseInvoice and PurchaseInvoiceDetail are joined with Reference

The query should produce the highlighted records

I was going to attach the actual tables, but I don't know how to attach file(s) to a post. Hope this will work.

Thanks

Here are some records
What is the SQL code producing this records ?
I wonder how same Reference has different Quantity and Cost values ....

The above were just some items selected from a "test file". The code is:


SELECT ItemMaster.ItemID, ItemMaster.ItemDescription, PurchasesInvoice.TranDate, PurchasesInvoice.Reference, PurchasesInvoiceDetail.Quantity, PurchasesInvoiceDetail.Cost
FROM ItemMaster INNER JOIN (PurchasesInvoice INNER JOIN PurchasesInvoiceDetail ON PurchasesInvoice.Reference = PurchasesInvoiceDetail.Reference) ON ItemMaster.ItemID = PurchasesInvoiceDetail.ItemID
WHERE (((ItemMaster.ItemID) Like "FBPC 1X*"));


The reason the same reference has multiple items is due to multiple items being purchased on same invoice. PurchaseInvoice is like an invoice header and the PurchaseInvoiceDetail is the detail to the invoice. The Reference links the two, but the unique record is the ItemID.

You can do this with two queries. The first selects only the ItemID and the Max of TranDate. Then combine this totals/group by query with your above SQL to pull the detail information about the most recent ItemID sale.

Duane
Hook'D on Access
MS Access MVP

What about this ?
SELECT I.ItemID, I.ItemDescription, P.TranDate, P.Reference, D.Quantity, D.Cost
FROM ((PurchasesInvoiceDetail D
INNER JOIN PurchasesInvoice P ON D.Reference = P.Reference)
INNER JOIN ItemMaster I ON D.ItemID = I.ItemID)
INNER JOIN (
SELECT B.ItemID, Max(A.TranDate) AS MaxOfTranDate FROM PurchasesInvoice A INNER JOIN PurchasesInvoiceDetail B ON A.Reference = B.Reference GROUP BY B.ItemID
) M ON D.ItemID = M.ItemID
WHERE P.TranDate = M.MaxOfTranDate

Hope This Helps, PH.
FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?

Thanks to both of you for the ideas. I'm trying dhookom's first. My first query is (with some unnecessary fields removed):

SELECT ItemMaster.ItemID, Max(PurchasesInvoice.TranDate) AS MaxOfTranDate
FROM ItemMaster INNER JOIN (PurchasesInvoice INNER JOIN PurchasesInvoiceDetail ON PurchasesInvoice.Reference = PurchasesInvoiceDetail.Reference) ON ItemMaster.ItemID = PurchasesInvoiceDetail.ItemID
GROUP BY ItemMaster.ItemID
HAVING (((ItemMaster.ItemID) Like "fbpc 1x*"));

That works for selecting the 4 desired records.

2nd query is

SELECT qryLastPurchaseDate.ItemID, PurchasesInvoiceDetail.Cost
FROM PurchasesInvoiceDetail INNER JOIN qryLastPurchaseDate ON PurchasesInvoiceDetail.ItemID = qryLastPurchaseDate.ItemID;

The result is all 10 records in this sample. I don't see how or where to incorporate the max date

Your second query needs to join on the MaxOfTranDate.

Duane
Hook'D on Access
MS Access MVP

Thanks for the input. Both solutions worked.