Group By, Max, many to many

[Lorna_Jane]

I tried posting this before, but it hasn't shown up, sorry if this ends up
being a duplicate.

I have the following tables and fields:
Catchments (UID)
Districts (DistID, Name)
Lookup (UID, DistID, percent)

UID, DistID, UID and DistID are the primary keys

To give you an idea of what the data mean; the percent field refers to the
percentage of each catchment that is made up of a particular district.
Catchments and Districts have a many to many relationship.

I would like to create a query that will take the Max percentage for each
catchment UID and return the name of the District that makes up that
percentage. Like this:
UID DistID Name maxpercent
0001 13 District13 100
0002 14 District14 100
0003 15 District15 85

I can retrieve the UID and maxpercent without problems, but when I try to
add DistID and Name it either adds the wrong one (if I choose 'first' under
totals) or adds all of the Districts associated with a catchment and gives
all of them the same maxpercent value (if I use a separate query to first
retrieve maxpercent and UID)

Any help is appreciated, let me know if you need any further clarification.

 

[Allen Browne]

You'll need to do this in 2 stages.

You could create a query to Group By DistID, and choose Max of percent. This
returns just 1 record per district (unless there are multiples the same
size.) Save that query. Then create another one that uses both that one and
the lookup table (joined), so that it returns the other details you want.

If you want to try to do it all in one query, you could use a subquery.
Here's some links to get you started with that:
http://allenbrowne.com/subquery-01.html
http://www.mvps.org/access/queries/qry0020.htm