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?
 

Some videos you may like

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

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
75,913
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,113,849
Messages
5,544,647
Members
410,627
Latest member
georgealice
Top