Solved latest price (1 Viewer)

[FahadTiger]

Hello My Expert
I have tblPurchase with PurchaseID AS primary key and purchaseDate
and tblPurchaseDetail with PDID AS primary key and PurchaseID as number and ItemID AS number
and purchasePrice as currency...what I want the latest purchasePrice for each Item according to latest purchaseDate
any advise ...thank you

 

[June7]

Possibly with a correlated subquery using TOP N. Review http://allenbrowne.com/subquery-01.html#TopN
But I think your requirement may be more complicated.

Want to provide db for analysis?

I advise not to use exact same field name in multiple tables. Perhaps PurchaseID_FK in tblPurchaseDetail.

Try this.

Build and save a query that joins tables, name it Purchases_Details:

SELECT tblPurchaseDetail.PDID, tblPurchaseDetail.PurchaseID, tblPurchaseDetail.ItemID, tblPurchaseDetail.purchasePrice, tblPurchase.PurchaseDate
FROM tblPurchase INNER JOIN tblPurchaseDetail ON tblPurchase.PurchaseID = tblPurchaseDetail.PurchaseID;

Use that query in another:

SELECT Purchases_Details.*
FROM Purchases_Details WHERE PDID IN (SELECT TOP 1 PDID FROM Purchases_Details AS Dupe WHERE Dupe.ItemID=Purchases_Details.ItemID
ORDER BY Dupe.PurchaseDate DESC, Dupe.PDID DESC);

 

[XPS35]

And what if the item has not been purchased before?

 

[mike60smart]

Hello My Expert
I have tblPurchase with PurchaseID AS primary key and purchaseDate
and tblPurchaseDetail with PDID AS primary key and PurchaseID as number and ItemID AS number
and purchasePrice as currency...what I want the latest purchasePrice for each Item according to latest purchaseDate
any advise ...thank you

Can you upload a zipped copy of your database?

 

[FahadTiger]

Possibly with a correlated subquery using TOP N. Review http://allenbrowne.com/subquery-01.html#TopN
But I think your requirement may be more complicated.

Want to provide db for analysis?

I advise not to use exact same field name in multiple tables. Perhaps PurchaseID_FK in tblPurchaseDetail.

Try this.

Build and save a query that joins tables, name it Purchases_Details:

SELECT tblPurchaseDetail.PDID, tblPurchaseDetail.PurchaseID, tblPurchaseDetail.ItemID, tblPurchaseDetail.purchasePrice, tblPurchase.PurchaseDate
FROM tblPurchase INNER JOIN tblPurchaseDetail ON tblPurchase.PurchaseID = tblPurchaseDetail.PurchaseID;

Use that query in another:

SELECT Purchases_Details.*
FROM Purchases_Details WHERE PDID IN (SELECT TOP 1 PDID FROM Purchases_Details AS Dupe WHERE Dupe.ItemID=Purchases_Details.ItemID
ORDER BY Dupe.PurchaseDate DESC, Dupe.PDID DESC);

thank you so much @June7 ..its working perfect

 

[Mike Krailo]

It's always helpful to others when you post what you did exactly to fix your problem.

 

[FahadTiger]

And what if the item has not been purchase before?

i did that and enter a new item in subform and give the last price=0 ..and that what I need ..thank you

 

[FahadTiger]

It's always helpful to others when you post what you did exactly to fix your problem.

thank you ..its fixed from @June7

 

[June7]

Could JOIN second query to table of products to show all even if never purchased.

 

[Mike Krailo]

thank you ..its fixed from @June7

And what was that exactly. That's my point.

 

[FahadTiger]

Could JOIN second query to table of products to show all even if never purchased.

the user enter any new item from the combobox in the sub form and after update event I used function Dlookup to get me last price from the Query that you are did it for me..if new item i get value zero in the textbox of last price

 

[Mike Krailo]

I'm sorry FahadTiger, I was hoping you would actually post the working code that June7 helped you with. That makes it perfectly clear what your solution was. I'm glad you got things working the way you desire.