Tricky Query

Below is a data set from MS Access:

Cert#                      Name                      Coverage                      Pay't Mode                    indicator
3503490991      AHUVA WOODS      DRUG ONLY MEMBER      Annual      17
3493487341      ALAIN BELLEHUMEUR      INCOME PROTECTION 120 DAY      Monthly      24
3493487341      ALAIN BELLEHUMEUR      COLA RIDER 120 DAY      Monthly      10
3493487341      ALAIN BELLEHUMEUR      EXTENDED HEALTH      Monthly      22
3493487340      ALAIN BELLEHUMEUR      TERM LIFE      Monthly      1
3493487340      ALAIN BELLEHUMEUR      PERSONAL ACCIDENT      Monthly      5
3493487481      ALAIN BOILY      OFFICE OVERHEAD 30DAY/12MON      Monthly      23
3493487481      ALAIN BOILY      INCOME PROTECTION 30 DAY      Monthly      26
3493488090      ALAIN DUVAL      PERSONAL ACCIDENT FAMILY      Monthly      3
3493488091      ALAIN DUVAL      INCOME PROTECTION 30 DAY      Monthly      26
3493488090      ALAIN DUVAL      TERM LIFE      Monthly      1
3493488091      ALAIN DUVAL      EXTENDED HEALTH COUPLE      Monthly      21
3493492620      ALAIN LAFLEUR      TERM LIFE      Monthly      1
3493889501      ALAIN LAFLEUR      EXTENDED HEALTH COUPLE      Monthly      21
3503493151      ALAIN LAFOND      DRUG ONLY FAMILY      Monthly      18
3503665361      ALAIN LEMAY      DRUG ONLY FAMILY      Monthly      18
3503490761      ALAIN LEPAGE      DRUG ONLY MEMBER      Annual      17
3503699271      ALAIN M LETUVE      DRUG ONLY FAMILY      Annual      18
3493488981      ALAN MACNAUGHTON      DENTAL RIDER FAMILY      Monthly      15
3493488980      ALAN MACNAUGHTON      PERSONAL ACCIDENT FAMILY      Monthly      3
3493488981      ALAN MACNAUGHTON      INCOME PROTECTION 120 DAY      Monthly      24
3493488981      ALAN MACNAUGHTON      EXTENDED HEALTH FAMILY      Monthly      20
3493488980      ALAN MACNAUGHTON      TERM LIFE      Monthly      1

-----------------------

This data has the same people with different cert#s. The only difference is the indicator, last column.  I want to pull out distinct records that have the highest indicator.  

So for example:

3493488981-----ALAN MACNAUGHTON-----DENTAL RIDER FAMILY---Monthly---15
3493488980-----ALAN MACNAUGHTON-----PERSONALACCIDENTFA--Monthly---3
3493488981-----ALAN MACNAUGHTON-----INCOME PROTECTION----Monthly---24
3493488981-----ALAN MACNAUGHTON-----EXTENDED HEALTH F-----Monthly---20
3493488980-----ALAN MACNAUGHTON-----TERM LIFE-----------------Monthly---1

I only want the record that says 24.

This is kind of urgent...



LVL 13
LucasMS Dynamics DeveloperAsked:
Who is Participating?
 
GRayLConnect With a Mentor Commented:
How about:

Select a.[Cert#], a.[Name], a.Coverage, A.[Pay'tMode], a.Indicator from myTable AS a
INNER JOIN myTable AS b ON a.[Name] = b.[Name] Having a.Indicator = Max(b.Indicator);
0
 
rockiroadsCommented:
something like this perhaps?

select a.*
from table a
where indicator = (select max(indicator) from table where name = a.name)


helps if name field is indexed


0
 
LucasMS Dynamics DeveloperAuthor Commented:
Rockiroads' query returns all the records with an indicator of 27.
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
LucasMS Dynamics DeveloperAuthor Commented:
GRayL, you're *BANG* on the money....

Thanks...
0
 
rockiroadsCommented:
did mine return all records?

did u remember to put table aliases?
as we are matching by name
0
 
GRayLCommented:
Thanks, glad I could help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.