|
subquery to keep maximum date
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 |
subquery to keep maximum date
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 |
subquery to keep maximum date
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 |
subquery to keep maximum date
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 |
subquery to keep maximum date
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 |
subquery to keep maximum date
I've never used SQL in an Excel macro before.
How do I set up tables for input and output, etc. to accomplish this? -- Richard "Sam Wilson" wrote: 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 |
subquery to keep maximum date
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" wrote: I've never used SQL in an Excel macro before. How do I set up tables for input and output, etc. to accomplish this? -- Richard "Sam Wilson" wrote: 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 |
subquery to keep maximum date
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 -- Richard "Sam Wilson" wrote: 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" wrote: I've never used SQL in an Excel macro before. How do I set up tables for input and output, etc. to accomplish this? -- Richard "Sam Wilson" wrote: 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 |
subquery to keep maximum date
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" wrote: 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 -- Richard "Sam Wilson" wrote: 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" wrote: I've never used SQL in an Excel macro before. How do I set up tables for input and output, etc. to accomplish this? -- Richard "Sam Wilson" wrote: 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 |
| All times are GMT +1. The time now is 09:03 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com