1

I'm trying to create an Access 2013 database query to display all insurance policy holders that have more than one type of insurance. I am not interested in policy holders that have only one type of insurance. If a policy holder has more than one type of insurance I need a list showing the policy holder “NAME” and each unique insurance “TYPE”.

The database table is named “INSURANCE” and contains a large number of record fields but I need to isolate only two record fields. Here is a sample of 15 records:

enter image description here

So, in the above example, I am not interested in “Jones” or “Miller” since they have only a single type of insurance. Since “Smith’, “Brown”, "Davis" and "Wilson" have multiple types of insurance I need a list showing their names and each unique type of insurance. For the above example, the query would produce the following result:

enter image description here

Thank you for your help.

0
-- this should work for a starter
select distinct MyTable.Name, MyTable.Type
from MyTable
where Name in (
        select t1.Name
        from (
            select t0.Name, t0.Type
            from (
                select distinct MyTable.Name, MyTable.Type
                from MyTable
                ) as t0
            group by t0.Name, t0.Type
            ) as t1
        group by t1.Name
        having count(*) > 1
        );
  • Great answer byrdzeye!! It worked perfectly with the large data file I had!! I had actually found a solution but it took me 3 separate queries to accomplish what you did with only one query. Thank you! – Ernie Sep 13 '15 at 1:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.