I agree with Ken Snell's solution up to a point, but I used a slightly
different approach, got different answers, and thought the instructions
were a bit ambiguous (still think so).
I start with a trimmed-down Table (omitting some of the losers):
[Scores] Table Datasheet View:
Date Name Points
--------- ------ ------
11/4/2005 Daisy 9
11/4/2005 Jim 10
11/6/2005 Sally 10
11/7/2005 Daisy 9
11/7/2005 Jim 9
11/8/2005 Sally 8
11/8/2005 Jim 10
Since my first two Queries were isomorphic to Ken's, I'll skip to my third
one. My [Q_2_HighPersonEachDay] is the same as Ken's [qry_2], and that's
the alias I use here. This Query identifies, from among the high daily
scores, the highest of these scores for each person.
[Q_3_HighPointsForHighPerson] SQL:
SELECT qry_2.Name,
Max(qry_2.MaxPoints) AS MaxOfMaxPoints
FROM Q_2_HighPersonEachDay AS qry_2
GROUP BY qry_2.Name;
[Q_3_HighPointsForHighPerson] Query Datasheet View:
Name MaxOfMaxPoints
------ --------------
Daisy 9
Jim 10
Sally 10
(By contrast, I got the following results from [qry_3], because Jim 9
didn't match Jim 10, and both got reported:
TheName Points MinOfTheDate
------- ------ ------------
Daisy 9 11/7/2005
Jim 9 11/7/2005
Jim 10 11/4/2005
Sally 10 11/6/2005
)
Knowing the high score for each person, we now look for the earliest of a
set of dates on which that person got that score:
[Q_4_EarliestDateForHighPerson] SQL:
SELECT Q3.Name, Min(qry_2.Date) AS MinOfDate
FROM Q_3_HighPointsForHighPerson AS Q3
INNER JOIN Q_2_HighPersonEachDay AS qry_2
ON (Q3.Name = qry_2.Name)
AND (Q3.MaxOfMaxPoints = qry_2.MaxPoints)
GROUP BY Q3.Name;
[Q_4_EarliestDateForHighPerson] Query Datasheet View:
Name MinOfDate
----- ---------
Daisy 11/7/2005
Jim 11/4/2005
Sally 11/6/2005
So now we have a date (not necessarily unique) to report for each person
on our short list. Finally we pull it together, invoking the original
Table, [Scores], to supply the specifics:
[Q_5_FirstHighestScore] SQL:
SELECT Scores.Date, Scores.Name, Scores.Points
FROM Q_4_EarliestDateForHighPerson AS Q4
INNER JOIN Scores
ON (Q4.Name = Scores.Name)
AND (Q4.MinOfDate = Scores.Date)
ORDER BY Scores.Name;
[Q_5_FirstHighestScore] Query Datasheet View:
Date Name Points
--------- ------ ------
11/7/2005 Daisy 9
11/4/2005 Jim 10
11/6/2005 Sally 10
What I thought wasn't clear was how to report duplicates, such as Jim and
Daisy both with high scores on the same day. Nothing here appears to
preclude all the high-scoring persons from having the same score on the
same day and all being reported.
-- Vincent Johns <
[email protected]>
Please feel free to quote anything I say here.
First, do not use Date and Name as the names of fields. They are reserved
words in ACCESS, and your use can lead to all kinds of problems and
confusion. See these articles for more information:
List of reserved words in Access 2002 and Access 2003
http://support.microsoft.com/default.aspx?scid=kb;en-us;286335
List of Microsoft Jet 4.0 reserved words
http://support.microsoft.com/?id=321266
Special characters that you must avoid when you work with Access
databases
http://support.microsoft.com/?id=826763
I am hopeful that you have a primary key for your table; you don't have
an obvious one shown, although it may be "Name"? If this is the primary
key, you may find a problem when you have two Ed people, or three Jim
people -- which one is which?
To do the queries you wish, break them down into individual data parts
and build each query to give that result. I am changing Name to TheName,
and Date to TheDate in my examples.
For example, you want the records from the table with the highest point
value for each date. So build a query to get the highest point total for
each date (save this query as qry_1):
SELECT TheDate, Max(Points)
FROM YourTableName
GROUP BY TheDate;
Now you want a query to return the name and points data for each of the
above records (save this query as qry_2), which uses a join to qry_1:
SELECT Y.TheDate, Y.TheName, Y.Points
FROM YourTableName AS Y
INNER JOIN qry_1 AS Q
ON Y.TheDate = Q.TheDate And
Y.Points = Q.Points
Then, a third query will weed out the duplicate "name" values (you say
the "first" date, so I assume you mean the earlier date), so we'll use
qry_2 as the basis for the data records:
SELECT TheName, Points, Min(TheDate)
FROM qry_2
GROUP BY TheName, Points;
