Tricky Query

Posted on 2006-05-19
Last Modified: 2008-03-10
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
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...

Question by:lucas911
    LVL 65

    Expert Comment

    something like this perhaps?

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

    helps if name field is indexed

    LVL 44

    Accepted Solution

    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);
    LVL 13

    Author Comment

    Rockiroads' query returns all the records with an indicator of 27.
    LVL 13

    Author Comment

    GRayL, you're *BANG* on the money....

    LVL 65

    Expert Comment

    did mine return all records?

    did u remember to put table aliases?
    as we are matching by name
    LVL 44

    Expert Comment

    Thanks, glad I could help.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    This article is a continuation or rather an extension from Cascading Combos ( and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
    Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
    Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    8 Experts available now in Live!

    Get 1:1 Help Now