Query: Which employee had the most orders... for each day

[Bill]

Wow... here's an SQL2k/Access2k query that sounds like it "should" be short,
easy (and very common):

My table contains records with fields of:

EomKey (The record ID #)
EomDateSort (The date of the order)
EomEmp (The employee name)

I can get the total number of orders processed by each employee... per
day...
with something like:
SELECT EomDateSort, EomEmp, COUNT(EomKey)
FROM EomTable
GROUP BY EomDateSort, EomEmp
ORDER BY EomDateSort ASC

Giving results like:
01-Mar-2004 Bob 12
01-Mar-2004 Jan 11
01-Mar-2004 Kim 21

02-Mar-2004 Jim 17
02-Mar-2004 Bob 13

05-Mar-2004 Tim 12
05-Mar-2004 Sam 23
05-Mar-2004 Pat 22
05-Mar-2004 Bob 30

06-Mar-2004 Sue 11

But... now I only want to see "which employee had the most orders... for
each day".

Why can't I get results like this:
01-Mar-2004 Kim 21
02-Mar-2004 Jim 17
05-Mar-2004 Bob 30
06-Mar-2004 Sue 11

I've tried (and failed) with some VERY long, complicated 2-3 level nested
sub-queries...
and can't seem to get one that works.

Help!

 

[John Viescas]

SELECT EomDateSort, EomEmp, COUNT(EomKey)
FROM EomTable
GROUP BY EomDateSort, EomEmp
HAVING COUNT(EomKey) =
(SELECT Top 1 COUNT(EomKey) FROM EomTable AS E2
WHERE E2.EomDateSort = EomTable.EomDateSort
GROUP BY E2.EomEmp
ORDER BY COUNT(EomKey) Desc)
ORDER BY EomDateSort ASC

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)