Just want the top record

[Guest]

Hi there! Using A02 on XP, not a programmer but love learning in here.

I need to run a query that will show me all the records from a table called
tInfo where [SL]=2 and [ICSDateSent] Is Not Null. I have a table,
tRPSAdminBills, that contains annual records that include a
[ParticipantCount]. Each year the data is keyed in for each [GP] for the
Plan Year End [PYE]. My tables are joined on [GP].

I need the most recent [ParticipantCount] for each contract number [GP] in
my query. Unique Values only works if I'm drilling down to one contract
number.

From my base table, tRPSAdminBills, I need: [GP], [PlanName], [Contact],
[Phone], WHERE [SL]=2, and [ICSDateSent] Is Not Null

From my many table, tInfo, I need: [ParticipantCount] from the most recent
[PYE]

This would be a really big help for me. Thank you in advance for any help
or advice on this. I appreciate your time.

 

[Guest]

Create the query to gather up the records which you want to compare.

Sort them on the fields which would define the records from top to bottom.

Open up the query in design view and rigth click near the tables. Select
Properties and set Top Values to 1.

After reading the description of the problem, you may need to use
sub-queries to extract the exact record if the above Top solution does not
work. In that case please post your SQL that you have so far.

 

[John Spencer]

Possibly something like the following. If I've guessed correctly on which
table has which fields.

SELECT I.ParticipantCount, I.PYE, R.*
FROM tInfo as I INNER JOIN tRRPSAdminBills as R
ON I.GP = R.GP
WHERE R.SL2=2 and R.ICSDateSent is Not Null
AND I.PYE = (
SELECT MAX(I2.PYE)
FROM tInfo as I2
WHERE I2.GP = I.GP)

 

[Guest]

Hi Jerry,

Thanks for the prompt reply. Problem with the Top Values is I need all the
contracts to show with only the top value from the second query. Let me
explain better.

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

GP0321 John Doe 555-111-2222

The second query I created on the tRPSAdminBills table pulls multiple
records (1932 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 records 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.

Does that help? Hope so and hope you have time to help.

Thanks!
--
Bonnie

Create the query to gather up the records which you want to compare.

Sort them on the fields which would define the records from top to bottom.

Open up the query in design view and rigth click near the tables. Select
Properties and set Top Values to 1.

After reading the description of the problem, you may need to use
sub-queries to extract the exact record if the above Top solution does not
work. In that case please post your SQL that you have so far.
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Hi there! Using A02 on XP, not a programmer but love learning in here.

I need to run a query that will show me all the records from a table called
tInfo where [SL]=2 and [ICSDateSent] Is Not Null. I have a table,
tRPSAdminBills, that contains annual records that include a
[ParticipantCount]. Each year the data is keyed in for each [GP] for the
Plan Year End [PYE]. My tables are joined on [GP].

I need the most recent [ParticipantCount] for each contract number [GP] in
my query. Unique Values only works if I'm drilling down to one contract
number.

From my base table, tRPSAdminBills, I need: [GP], [PlanName], [Contact],
[Phone], WHERE [SL]=2, and [ICSDateSent] Is Not Null

From my many table, tInfo, I need: [ParticipantCount] from the most recent
[PYE]

This would be a really big help for me. Thank you in advance for any help
or advice on this. I appreciate your time.