Maximum values from a date range

[FatLane]

I am using Access 2010 and I'm having trouble creating a query to generate the desired output. Any tips would be appreciated.



My database contains tournament results. Each tournament contains five events. Each player receives a score for each event.

I am looking to create a query to return one single value for each player. This value is the maximum score from one event category, from all tournaments within a specific month.

As an example... In May of 2011 there were 8 tournaments (each of these tournaments had 5 scoring events).

I need to find out what each player's highest score was for one event from all tournaments in May of 2011. So if Joe Blough entered three tournaments, and his three scores for one of the events were 226, 488 and 112. Then I need the query to return the value of 488 for Joe Blough.

If there were 688 different players who entered those 8 tournaments, then I will need 688 results returned from the query.

Here is my structure with the pertinent tables. (*) indicates key.

PLAYERS
PlayerID (*)
First Name
Last Name
Club
Country

Tournaments
TouarnamentID (*)
divID
Start Date
Tournament Name
Venue

Results
ResultID (*)
TournamentID
PlayerID
Level
Event1
Event2
Event3
Event4
Event5

I can create one query for each of the five tournament events. The information from these queries will be manipulated using VBA and then spit back out into a new table.

Thanks again. -Brad

[pbaldy]

The fields in the last table aren't normalized. If you leave it like that you'll need something like this to find the highest score:

http://support.microsoft.com/KB/182760

[FatLane]

I'm not actually trying to get the maximum value from across fields. To clarify, I've changed the name of my fields to something more meaningful. Swimming, gymnastics, cycling, lifting, running.

Here is my results table with some data:

Result ID	TournamentID	PlayerID	Level	Swimming	Gymnastics	Cycling	Lifting	Running
3772	39	2607	2	58.00	116.00	12.50	69.00	10.23
3773	40	2607	2	77.00	122.00	7.76	71.00	11.42
3774	41	2607	2	57.00	0.00	18.65	74.00	11.05
3791	42	2611	2	67.00	116.00	10.10	68.00	10.48

I'm simply trying to get the maximum value from one of those events from a specific month.

If I want to find the maximum cycling score score for a specific player I can run a simple query to get this result:

TournamentID	StartDate	PlayerID	Cycling
41	3/16/2012	2610	18.50
39	2/6/2012	2610	18.40
40	2/22/2012	2610	17.40
42	3/26/2012	2610	17.10

What I'm trying to figure out is how to limit this to a single result which would be the highest cycling score for player #2610 during the month of February 2012. The desired result would be: 18.40



As for the normalization problem... I'm doing it this way because the tournament results for all events of a single player are presented to me in a single line. I then take those results, format them and append them to the results table.

US Open
2/22/2012
LAST NAME	FIRST NAME	COUNTRY	DIVISION	SWIMMING	GYMNASTICS	CYCLING	LIFTING	RUNNING
Fulford	Cam	USA	2	58	116	12.5	69	10.226
Blough	Joe	USA	2	60	113	11.8	67	10.732

[pbaldy]

In general terms:

SELECT PlayerID, Max(Cycling) AS MaxCycling
FROM TableName
WHERE DateField Between #2/1/12# And #2/28/12# And PlayerID = 2610
GROUP BY PlayerID