I know - that SQL statement will work in your macro. How does your current
macro get the data? If it uses a SQL statement you can modify it there.

Otherwise the easiest way is to use a macro to sort by Milestone, then by
date. The macro can run through the list one row at a time and delete rows
that have the same milestone as the row above.

Sam

"Richard" wrote:

Sam,
Thanks, but the question was not how to do in Access, but how to do this
same type of think in an Excel macro.
I'm trying to not use Access in this case - to simplify my Excel programming.
--
Richard


"Sam Wilson" wrote:

On second thoughts, that sub-query's slightly wrong, it groups on the wrong
field!

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

That should work, you can paste it into access to check.

"Sam Wilson" wrote:

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