query to find most recent price change

[MrHaggis]

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.

 

[Andrew Fergus]

Hi

You could do this using an SQL subquery (if you want just the one query) but I find the easiest & quickest way of doing this is with 2 queries. Use your first query to find the last date (using the max function as you have) for each product (and project and model if required). You probably already have this query set up.

Create a second query that is based on the first query and the original table. Create links (by clicking and dragging) the fields from your first query to the corresponding fields on the table. Then in your query grid include all of the the fields from the first query and the price from the table. The output from this query should be what you want.

HTH, Andrew

 

[MrHaggis]

Thanks Andrew - that's the direction I was heading in, but didn't know if it was possible to get fancy with SQL to do it all in one statement. Your reply is appreciated.
Cheers,
H

 

[giacomo]

You can do this with a sub-query:

select
Project
,Model
,Product
,Date
,Price 
from someTable as X
where 
[date] = (
               select max([date])
               from someTable 
               where 
               Project = x.Project 
               AND Model = x.Model
               AND Product = x.Product
         );

hth,
Giacomo