query to find most recent price change

MrHaggis

New Member
Joined
Oct 6, 2006
Messages
18
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.
 

Some videos you may like

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

Andrew Fergus

MrExcel MVP
Joined
Sep 9, 2004
Messages
5,432
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

New Member
Joined
Oct 6, 2006
Messages
18
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

Well-known Member
Joined
Feb 20, 2002
Messages
1,796
You can do this with a sub-query:
Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,113,850
Messages
5,544,650
Members
410,627
Latest member
georgealice
Top