Query problem!

[Kagsy]

Dear All

I have a problem designing a query to extract the
information I want:

Original information (three fields): Name, Town, Total

I have repeating data in this table, e.g.
sam, Glasgow, 10
sam, Glasgow, 15
sam, Edinburgh, 12

(I know this is not denormailsed data but it is a simpler
version for dicussion purposes.)

I would want record 2 returned because it returned the
highest total and ignored the other 2, importantly there
must be only one record for sam returned and it should be
the one with the highest total. If there are two records
with two different towns with the same Total I do not mind
which record is returned, BUT only one record for sam must
be returned. Can anyone help. I suppose I could sort it
through messy code but there must be a simpler way.

Please put this girl out of her misery and thanks for your
help.

Kagsy

 

[Kevin]

If you don't care about the city in which the maximum
occurs, switch to Totals (View, Totals) from Query Design
view and set the following criteria in the Totals row:

Name Group By
Total Max

HTH
Kevin Sprinkel
Becker & Frondorf