I've done something very similar with refurbishment projects:

SELECT temp.ID, Milestone, MaxDate, Cost, Project
FROM Milestone_table INNER JOIN
(SELECT Max(Date) as MaxDate, ID FROM Milestone_table as mt GROUP BY ID) as
temp on Milestone_table.ID = temp.ID
ORDER BY Milestone_Table.Milestone, Milestone_Table.Project,
Milestone_Table.ID

This creates a table using a sub-query and this works fine for me. I've
free-typed the SQL above so you may have to correct it slightly, but the idea
is right.


"Richard" wrote:

I have a table of project milestones with these fields:
milestone, date, cost, & project
"date" is the date the milestone was added or changed; so over
time a given milestone may have several entries which reflect changes in cost.
For example:
Milestone Date Cost Project
Build radio, 1 Jan 08, $100.00, Electronics
Build radio, 1 Mar 08, $110.00, Electronics
Build radio, 1 Apr 08, $90.00, Electronics

When my macro pulls the data, I want to only keep the Cost for the most
recent Date, i.e. Build radio, $90.00, Electronics

In Access I would use a sub-query, something like
SELECT ID, Milestone, Date, Cost, Project from Milestone_Table
Where (((ID) in (select top 1 ID
from Milestone_Table as dupe
where dupe.milestone = Milestone_Table.milestone
and dupe.project = Milestone_Table.project
and dupe.ID = Milestone_Table.ID
ORDER by dupe.date DESC, dupe.ID DESC)))
Order by Milestone_Table.Milestone, Milestone_Table.Project,
Milestone_Table.ID;

Is there a way I can do this directly in Excel so I don't have to export
data to Access, run subquery, port back into Excel?
--
Richard