Hi all,
I've searched for what I think is probably a simple answer, but so far I'm lost...
I have a table with project, model and product information. Every time the price of a product changes, it's recorded in this table, so for every product, I could have many lines:
Project,Model,Product,Date,Price
TUC,Willow,Countertop,01/01/06,$40.00
TUC,Willow,Countertop,01/10/06,$35.00
TUC,Willow,Cabinet,02/01/06,$420.00
TUC,Willow,Cabinet,03/05/06,$400.00
TUC,Willow,Countertop,06/01/06,$41.00
I want to run a query to extract only the most current price for every product. I can get as far as creating a query that includes all fields except Price and use MAX on the date field, but how do I make the query include the price that goes with the most recent date? If I've formatted this post correctly, the records that would be returned in this example should be in bold.
Thanks in advance.
I've searched for what I think is probably a simple answer, but so far I'm lost...
I have a table with project, model and product information. Every time the price of a product changes, it's recorded in this table, so for every product, I could have many lines:
Project,Model,Product,Date,Price
TUC,Willow,Countertop,01/01/06,$40.00
TUC,Willow,Countertop,01/10/06,$35.00
TUC,Willow,Cabinet,02/01/06,$420.00
TUC,Willow,Cabinet,03/05/06,$400.00
TUC,Willow,Countertop,06/01/06,$41.00
I want to run a query to extract only the most current price for every product. I can get as far as creating a query that includes all fields except Price and use MAX on the date field, but how do I make the query include the price that goes with the most recent date? If I've formatted this post correctly, the records that would be returned in this example should be in bold.
Thanks in advance.