Google Answers Logo
View Question
Q: Access Query Design ( Answered 5 out of 5 stars,   2 Comments )
Subject: Access Query Design
Category: Computers > Software
Asked by: tamkins-ga
List Price: $15.00
Posted: 14 Jul 2003 12:26 PDT
Expires: 13 Aug 2003 12:26 PDT
Question ID: 229907
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

Subject: Re: Access Query Design
Answered By: hammer-ga on 14 Jul 2003 13:27 PDT
Rated:5 out of 5 stars
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

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. Access FAQ
Getting a related field from a GroupBy (total) query

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

Best regards,

- Hammer
tamkins-ga rated this answer:5 out of 5 stars
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 :-)

Subject: Re: Access Query Design
From: hammer-ga on 17 Jul 2003 08:39 PDT
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
Subject: Re: Access Query Design
From: tamkins-ga on 17 Jul 2003 08:47 PDT
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!

Important Disclaimer: Answers and comments provided on Google Answers are general information, and are not intended to substitute for informed professional medical, psychiatric, psychological, tax, legal, investment, accounting, or other professional advice. Google does not endorse, and expressly disclaims liability for any product, manufacturer, distributor, service or service provider mentioned or any opinion expressed in answers or comments. Please read carefully the Google Answers Terms of Service.

If you feel that you have found inappropriate content, please let us know by emailing us at with the question ID listed above. Thank you.
Search Google Answers for
Google Answers  

Google Home - Answers FAQ - Terms of Service - Privacy Policy