Max plus additional field

[Lorna_Jane]

Hello,
I've got 3 tables in a many to many relationship:
Table 1: Catchments
(AllCSL)
Table 2: Regions
(ProvincialEcoDistrictAttributes)
Table 3: Lookup table with percentage of each catchment falling within each
region
(lkp_ProvincialDist)

I have a query using tables 1 and 3 that selects the maximum percentage
grouped by Catchment ID. (Dominant Provincial Eco District1)
All I need to do is add to this is the Region ID corresponding to the
maximum percentage as well as the Catchment ID.
There are a couple of ways I have tried:
1. Adding Region ID to the query and selecting "first". This doesn't return
the correct Region ID.
2. Creating another query using the first one. I use the Maxofpercent field
and the Region ID. This gives me the following result:
Maxofpercent RegionID
80 34
80 54
80 78
70 32
70 13
70 34
It lists all of the regions within the catchment, listing the same
percentage for all, when all i want is:
Maxofpercent RegionID
80 54
70 13
I also want to add the Catchment ID.

Thanks

Here is the SQL I have so far that returns all the regions within the
catchment:
SELECT [Dominant Provincial Eco District1].UID, [Dominant Provincial Eco
District1].MaxOfpercent, ProvincialEcoDistrictAttributes.FMF_OBJECT_ID
FROM [Dominant Provincial Eco District1] INNER JOIN (AllCSL LEFT JOIN
(ProvincialEcoDistrictAttributes RIGHT JOIN lkp_ProvincialDist ON
ProvincialEcoDistrictAttributes.FMF_OBJECT_ID = lkp_ProvincialDist.OBJECT_ID)
ON AllCSL.UID = lkp_ProvincialDist.UID) ON [Dominant Provincial Eco
District1].UID = AllCSL.UID;

 

[Allen Browne]

Answered in your later post.