subquery to keep maximum date

[Richard]

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?

 

[Sam Wilson]

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.

 

[Sam Wilson]

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.

 

[Richard]

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.

 

[Sam Wilson]

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]

I've never used SQL in an Excel macro before.
How do I set up tables for input and output, etc. to accomplish this?

 

[Sam Wilson]

There are many ways, as with most programming. I use the following:

In your code window, go to tools/references find "Microsoft ActiveX Data
Objects 2.7 Library" and tick it.

Sub Demo()

Dim c As ADODB.Connection
Dim rs As ADODB.Recordset
Dim s As String

Set c = New ADODB.Connection
c.Open "Type connection string here - goto www.connectionstrings.com if you
need help"

s = "SELECT... (eg your SQL statement)"

Set rs = c.Execute(s, , 1)

'Do whatever you want with the records here

rs.close
con.close

end sub

 

[Richard]

Sam,
Very good on executing SQL from Excel. But could you give me a little more
detail on how to set the input table (for the SQL query) equal to the data in
a Excel sheet; and how to put the query output into another Excel sheet?

Thanks

 

[Sam Wilson]

Hi - after the line

set rs = c.execute(s,,1)

the recordset will be held in memory - you can either use the command:

range("a1").copyfromrecordset rs

to dump the results starting in cell A!, or you can use the following:

do while not rs.eof
msgbox rs(0) & " - " & rs(1) & " etc"
rs.movenext
loop

to roll through the results one record at a time doing things.

If you want to include parameters in your SQL query then use something like:

s = "SELECT * FROM table1 where field1 = " & range("B2").value & " ORDER BY
field 2;"

ie you can join text strings with values from somewhere on a worksheet.

Hope that helps!

Sam

 

[Richard]

Sam,
How do I get Excel data into the SQL search?
This would be something opposite of
range("a1").copyfromrecordset rs
to get a range into c

Hopefully this is last question. Thanks for your patience.

 

[Sam Wilson]

The only way I can think of off the top is my head is the following:

Dim i as integer
With Range("a2")
do until isempty(.offset(i,0))
s = "INSERT INTO table1 VALUES (...)"
set rs = c.execute(s,,1)
i=i+1
loop
end with


This assumes you have data starting in cell A2 (column headers in row 1...)
and moves down column A until it gets to an empty cell. Where I've put
VALUES(...) you'll have to modify the string to contain the cell values eg

s="... " & .offset(i,0).value & ", " & .offset(i,1).value & ", ..."

Another thing you'll have to watch out for here is that if you're inserting
text you need to include single quotes ' ' that text.