Getting too many records 2

[k9handler]

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

 

[PHV]

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
FAQ181-2886

 

[k9handler]

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

 

[dhookom]

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

 

[PHV]

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
FAQ181-2886

 

[k9handler]

Here are some records.

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

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 [highlight]highlighted[/highlight] 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

 

[PHV]

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

 

[k9handler]

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.

 

[dhookom]

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

 

[PHV]

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
FAQ181-2886

 

[k9handler]

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

 

[dhookom]

Your second query needs to join on the MaxOfTranDate.

Duane
Hook'D on Access
MS Access MVP

 

[k9handler]

Thanks for the input. Both solutions worked.