Selecting the maximum value for each primary key

[BGlover]

I am doing an outline design of a database system to handle the results from an archery competition in a couple of weeks.
Currently I have a table with a primary key to hold the information on the competitors (name, age etc.) and this is linked to a table via a foreign key (the archers name) to enable multiple scores to be recorded against each archer.
For the purposes of the competition only the first score shot each day is valid and so I am using a yes/no box to filter out these results.

What I would like to do is then filter the results such that only the largest of these first session scores is displayed for each archer. (i.e if archer A shoot 578 on day 1 and 585 on day 2 i would like the database to only report the 585, and similarly for the other entries)

Hopefully this make sense,
BGlover

 

[Mihail]

Currently I have a table with a primary key to hold the information on the competitors (name, age etc.) and this is linked to a table via a foreign key (the archers name)...

This is a big mistake: The FK should to point on a PK (ID in your case).

Can you upload the DB ? (Access 2003 version)

 

[BGlover]

Thank you for the reply.
I may have not been clear in my original post, I haven't started creating the database yet so the structure can still change.
My proposed idea is that I use the Archers name as the primary key to make searching easier ( we know the names of the people as opposed to assigning an arbitrary number- possibly we could use a more typical numerical PK and still search by the name associated?).
Attached is the scheme I described earlier, with the ideal outcome of the query 1 being that instead of 2 results for each archer only their highest score is displayed.