I have a database in Access.  I have used a make-table query to create
a table like this (I've put in some sample data):

Tract ID       Lessee         WI Total
-------------------------------------
1               Joe             1
2               Sally           .5
2               Joe             .2
2               Bill            .3
3               Dave            .8
3               Mandy           .2
4               Joe              1

What I want to do now is to design a query (or series of queries) to
do each of the following two things:

1. Return, for each tract ID, the Lessee who has the highest interest.
 The resulting table (I'm assuming a make-table query, because I need
to then link it into another table) should look like this, for the
data above:

Tract ID      Major Lessee
--------------------------
1               Joe
2               Sally
3               Dave
4               Joe

(I don't care about the column headings, of course, just that the data
is there.)

2. Return, for each tract ID, how much interest the Major Lessee has. 
The results for the table above would be as follows:

Tract ID      WI of major lessee
--------------------------------
1                 1
2                 .5
3                 .8
4                 1

It would be okay to do it all together as well, resulting in this:

Tract ID           Major Lessee            Interest
---------------------------------------------------
1                     Joe                   1
2                     Sally                 .5
3                     Dave                  .8
4                     Joe                   1

Help!

Hello again, Tamkins!

Here's your query. It does both pieces of information at once.

SELECT tblTracts.[Tract ID], Max(tblTracts.[WI Total]) AS Interest,
First(tblTracts.Lessee) AS MajorLessee
FROM tblTracts
GROUP BY tblTracts.[Tract ID]
ORDER BY tblTracts.[Tract ID];


Note: If your table is not named tblTracts, substitute your table's
name in the above query.

This query selects the record with the highest interest number for
each tract id using Max. It fills in the Lessee name using First.
Since there is only ever one Max record, the first Lessee name is
simply the name on that record. The GROUP BY clause tells Access that
you want the largest WI Total for each TractId (as opposed to some
other field).

Paste this query into the Access Query designer's SQL View. You can
then look at it in Design and Datasheet View to get a better idea of
what it does.

Please let me know if you need additional clarification. Good luck
with your Access project.

- Hammer

---

Request for Answer Clarification by tamkins-ga on 14 Jul 2003 14:28 PDT

Okay.  I put the query in as you suggested, but it's not working quite
correctly.  It's basically not grouping by the max interest.  What
it's giving me is the tract id (that's fine), the max interest (also
good), and the first lessee listed.

Ah, never mind.  I got the first make-table query to sort by tract id
and then by interest so that the first lessor listed is always the max
interest, and that solved it.  I'm still posting this for educational
reasons (basically, to note that your query doesn't work quite as
described, but only works if the original table is sorted by
interest).

Thanks for your help again, and once you reply to this I'll do the
rating stuff :-)

---

Clarification of Answer by hammer-ga on 14 Jul 2003 15:10 PDT

Much egg on my face! You are absolutely correct. I just was not paying
attention. Let me start over...

There are several methods for accomplishing what you need, however,
there is one problem with using any of them. Your table does not
include anything obvious that can be used as a unique id for the
record. Is the combination of TractID and Lessee unique?

- Hammer

---

Clarification of Answer by hammer-ga on 14 Jul 2003 15:34 PDT

Try this one...

SELECT Q.[Tract ID] AS TractID, Q.Lessee, Q.[WI Total]
FROM tblTracts AS Q
WHERE (((Q.[WI Total])=(SELECT Max(T.[WI Total])
                FROM tblTracts As T
                WHERE (T.[Tract ID] = Q.[Tract ID]))))
ORDER BY Q.[Tract ID];

This uses a subselect query to select the fields you need from the
record with the largest Interest value for each Tract. I did some
testing with your sample data, and this is providing the correct
results. Let me know if it works for you. Again, substitute your table
name for tblTract.

- Hammer

---

Clarification of Answer by hammer-ga on 17 Jul 2003 03:53 PDT

Hi Tamkins,

I don't see an RFC or a rating on this question, so I'm not sure if
the second solution I provided has helped you or not. In any case,
I'll provide the additional resources. The second solution was
selected from an FAQ page showing four ways to do what you need.

MVPS.org Access FAQ
Getting a related field from a GroupBy (total) query
http://www.mvps.org/access/queries/qry0020.htm

Please let me know if you need addional information and/or
clarification.

Best regards,

- Hammer

It worked like a charm, hammer.  Thanks!  (I'm sorry I took so long to
get back, but I was trying to do a bunch of other queries in the
meantime, and since I had figured out that work-around, I just didn't
have the energy to come back here and go through another round :-)

Great! I'm glad the second go-round worked. I didn't mean to push you.
I was just worried that you had given up after I botched the first
attempt. Again, my apologies...

- Hammer

Oh, don't worry about it, it's such a touchy thing.  I learn from the
mistakes too.  (And I didn't feel pushed - I just got energy to come
back and futz with it again.)

Incidentally, even though I didn't really understand the resultant
query, I was able to mimic it to do another, similar thing in the same
database using different tables.  So that was great!