Need top record for each contract

[Guest]

Hi there, using A02 on XP. Not a programmer. Have a problem figuring out
how to get the data I need.

I want to find all the contract records I need from tInfo and also see the
most recent [PYE] date for each contract from tRPSAdminBills. The 2 tables
have a one to many relationship.

I tried Top Values. Problem with the Top Values is I need all the
contracts to show but with only the top value from the second table. Let me
explain better.

My tInfo table query will give me 600 records, one for each contract number
[GP] that fits the criteria listed below.

Records return:
[GP] [Contact] [Phone]
GP0321 John Doe 555-111-2222
GP1922 Jim Smith 555-888-7777
GP2843 Fran Rice 555-444-6666

The second query I created on the tRPSAdminBills table pulls multiple
records (1371 total). Each [GP] may have anywhere from none to 6 or more
records. For example, my output looks like:

[GP] [PYE] [ParticipantCount]
GP0321 12/31/2003 56
GP0321 12/31/2004 52
GP1922 12/31/2002 96
GP1922 12/31/2003 88
GP1922 12/31/2004 103
GP2843 12/31/2003 21
GP2843 12/31/2004 26
GP2843 12/31/2005 31
etc, etc.

The results I need from the second query are:

GP0321 12/31/2004 52
GP1922 12/31/2004 103
GP2843 12/31/2005 31
etc. etc.

My final query results should show this:

GP0321 John Doe 555-111-2222 12/31/2004 52
GP1922 Jim Smith 555-888-7777 12/31/2004 103
GP2843 Fran Rice 555-444-6666 12/31/2005 31
etc. etc.

Hope I've described it well and you have time to help. Thanks in advance
for your help.

Thanks!

 

[Guest]

One query will do it. Create a query using both tables in design view.
If the two tables are joined then edit the join (if not joined - line
connecting the key field of tInfo to the matching field in tRPSAdminBills
table then click on key field and drag to tRPSAdminBills table.).
To edit click on the line then double click to open another window. Select
for all records of tInfo and only matching records of tRPSAdminBills table.
Drag down your fields [GP] [Contact] [Phone] from tInfo.
Drag down your fields [PYE] [ParticipantCount] from tRPSAdminBills
table.

On the menu click on the icon that is the Greek letter Epsilon. The design
grid change to the following row labels –
Field:
Table:
Total:
Sort:
Show:
Criteria:
Or:

All of the Total row will be Group By. In the grid for [PYE] column and
Total row select Max.

Save. Run.