| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Subquery Confusion | Excel Programming | |||
| Sum with Parameters in a Subquery | Excel Programming | |||
| Subquery after Left Join | Excel Programming | |||
| Subquery | Excel Worksheet Functions | |||
Hybrid Mode
