Ms-access – Query – Match Name to Multiple Unique Types

distinctms access

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:

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:

Thank you for your help.

Best Answer

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

Related Solutions

Ms-access – Additional info on some rows in the table

It sounds like your categories are a case of the subtypes pattern. Accordingly, I've added the tag subtypes to your question. There are two design patterns that you may want to look into.

The first one is called "Single Table Inheritance". This design involves collecting all the subtypes (or, if you prefer, subclasses) into one table, and leaving values missing (NULL) in places where they do not pertain. Views can be used to project this table onto subclass virtual tables. You generally need a "category" field to make the projections. It's simple, but it doesn't address all the problems.

The second one is called "Class Table inheritance". This design involves creating one table for the superclass and one table for each subclass. Attributes are placed in the appropriate table. Often, the PK for the subclass table is a copy of the PK for the superclass table. This enforces the one-to-one nature of the IS-A relationship, and provides for easy joins. Views can be used to join each subclass table with the relevant subset of the superclass table.

Your solutions #2 and #1 are somewhat like these two design patterns.

If you want to look up more detail on the web, I suggest searching for Martin Fowler's presentation of these two techniques.

Ms-access – How to create a database with “polymorphism”

Your best bet is likely going to be to create a single child table that references all three of the other tables onto a single GAC_id. So you'd end up with something more like this:

create table gac (
    GAC_id,
    accelerometer_id,
    loadcell_id,
    potentiometer_id,
    primary key (GAC_id),
    foreign key (accelerometer_id) refrences (accelerometer.accelerometer_id)
    foreign key (loadcell_id) references (loadcell.loadcell_id)
    foreign key (potentiometer_id) references (potentiometer.potentiometer_id)
)

Here, each foreign key should reference the primary from the appropriate table:

create table accelerometer (
    accelerometer_id,
    name,
    range,
    serial,
    primary key (accelerometer_id))
create table loadcell (
    loadcell_id,
    name,
    description,
    primary key (loadcell_id))
create table potentiometer (
    potentiometer_id,
    product_id,
    tag_id,
    primary key (potentiometer_id))

This will work from a relational standpoint if I understand what you are looking for correctly. Then you just make sure that all items in gac have a unique index associated with them, they cannot be null, and that each entry has to have a corresponding entry in the appropriate parent table. For Access specific SQL, use the MSDN Access SQL Reference to see Access specific queries to perform each task.

From a design standpoint, it would be a horrible idea to have three different tables with identical primary keys (GAC_ids in this case). Theoretically you could do it, but there are all sorts of issues that it can and will cause from a practical standpoint.

Theoretically you could also do something like what you had originally:

create table accelerometer (GAC_id, name, range, serial, primary key(GAC_id, <insert one of the other fields here>))
create table loadcell (GAC_id, name, description, primary key(GAC_id, <insert one of the other fields here>))
create table potentiometer (GAC_id, product_id, tag_id, primary key(GAC_id, <insert one of the other fields here>))

Then you could just create a view that joins them all on the GAC_id. Of course, this will only work if the GAC_ids correlate one-to-one:

create view my_view as select * from accelerometer a
    full outter join loadcell lc ON a.GAC_id = lc.GAC_id
    full outter join potentiometer p ON a.GAC_id = p.GAC_id

That would create a view that accomplishes the same task as the child table in the first explanation i gave, but it has the advantage of auto updating whenever an item is added to any table. the only catch is that if there is no corresponding entry in any of the other tables, you will see null values where those entries should be.

look into the MSDN Access SQL Reference for the specifics on how to do the different things i've mentioned, in Access SQL.

One thing that is worth mentioning is that Access SQL may not support full outter joins. I know that is a quirk with MySQL, but i am not familiar with Access SQL, so you will have to use the reference, and probably this website(dba.stackexchange.com) and google to see exactly how you can/should build your create queries in Access SQL.

Best Answer

Related Solutions

Ms-access – Additional info on some rows in the table

Ms-access – How to create a database with “polymorphism”

Related Question