Find Max date in a query

smiley1978

Board Regular
Joined
Sep 13, 2005
Messages
133
I have a table that is updated by an Excel Macro. The data is stored vertically. When the macro runs, one of the fields is given a date and time. This date and time is generated by the system clock and is stored in a variable in Excel. Every record that is updated by the macro is given the same date and time for each run. When I update this table, I have a field for State and one for Channel. I need to return the Max Date (the most recent run) for each combination of State and Channel. When I use the Totals option in the query, it returns everything. Can anyone help?
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

MyBoo

Board Regular
Joined
Aug 9, 2006
Messages
217
Have you tried using the MAX option when using Totals? Under Group By.
 

smiley1978

Board Regular
Joined
Sep 13, 2005
Messages
133
Yes I have. It returns everything. When I add State and Channel parameters to narrow the results to the Max date of that State Channel combo, it returns one of every date for the combo that is stored. That is the problem I am running into. For each state channel combo in the table, I need to return the most recent date. I will use this date in combination with the State Channel combo in another query to return the results that I am looking for.
 

smiley1978

Board Regular
Joined
Sep 13, 2005
Messages
133

ADVERTISEMENT

State Channel DateUpdated Measure Value
NC Ch1 10/10/2006 Premium 100
NC Ch1 10/10/2006 Premium 120
NC Ch1 10/15/2006 Premium 132
TX Ch1 10/16/2006 Premium 150
NC Ch2 10/14/2006 Premium 160
TX Ch1 10/15/2006 Premium 140
TX Ch2 10/19/2006 Premium 190

In this example I need to pull NC Ch1 date of 10/15, NC Ch2 date of 10/14, TX Ch1 date of 10/16 and TX Ch2 date of 10/19. For each combination of State and Channel, there is a max date. I need that date so that I can run it in another query. If I use the Max on the entire table, I will only receive the Max date, which is the last st to be updated. I need the most recent for each possible State Channel combo.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,303
Office Version
  1. 365
Platform
  1. Windows
This worked for me.

SELECT Sheet11.State, Sheet11.Channel, Max(Sheet11.DateUpdated) AS MaxOfDateUpdated
FROM Sheet11
GROUP BY Sheet11.State, Sheet11.Channel;

Note Sheet11 is the table name, which I copied from Excel.
 

Forum statistics

Threads
1,140,941
Messages
5,703,299
Members
421,289
Latest member
java

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top