QRY To List Out MAX

  • Thread starter Thread starter carl
  • Start date Start date
C

carl

My data is like this:

Date Group Count
20071203 KX 5049
20071203 ME 2196
20071203 AB 2077
20071203 AU 1354
20071203 WM 1165
20071203 D1 881
20071204 UI 2864
20071204 AB 2643
20071204 AU 2499
20071204 GK 1259
20071204 W5 1161
20071205 WA 1343
20071205 SH 1340
20071205 8X 1232
20071205 HK 905

I am trying to build a qry that will list out the max "Count" for each Date.

Like this.

Date Group Count
20071203 KX 5049
20071204 UI 2864
20071205 WA 1343


Thank You in Advance.
 
SELECT [Date], [Group], [Count]
FROM YourTable
WHERE [Count] =
(SELECT MAX([COUNT])
FROM YourTable as Tmp
WHERE Tmp.[Date] = YourTable.[Date])

Date, Group, and Count are all reserved words, so you may have to surround
them all with [] to make this work

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
Back
Top