Query to identify added dependents

[brickballer]

Hello- new to the forum and new-ish to Access as well, so forgive me if this is an easy question. I've been searching for a while and haven't been able to get this query to work, so here we go:



I have two tables with identical fields, each giving the enrollment data for a given month. Each employee has several lines, e.g. one for medical, another for dental, and another for vision. Additionally there are lines for medical, dental, and vision for each dependent. Each employee may have a dozen or more lines to cover every policy for every dependent.

Both tables have the following fields:

Group#, Insured SSN, Insured Last Name, Insured First Name, Mbr SSN, Mbr Last Name, Mbr First Name, Relationship, Birth Date, Sex, Coverage

To make matters a bit more complicated, Mbr SSN is not present for all records (in fact is missing for most).

The two tables are named PMD and TMD (Previous Month's Data, This Month's Data)

What I'm trying to identify are any new enrollments, e.g. someone adding a spouse or domestic partner. I was able to design a query to pull any new employees:

SELECT TMD.*
FROM TMD LEFT JOIN PMD ON TMD.[Insured SSN]=PMD.[Insured SSN]
WHERE (((PMD.[Insured SSN]) Is Null));

But I'm having difficulty picking up any added spouses or domestic partners (identified by the relationship field = "SPOUSE" or ="PARTNER". I would think that this would work:

SELECT TMD.*
FROM TMD LEFT JOIN PMD ON TMD.[Insured SSN]=PMD.[Insured SSN]
WHERE (((PMD.[Relationship]) Is Null));

But it's pulling the same set as the first query and is not finding the added dependents.


Could anyone kindly point me in the right direction? As you can see my skills are still a work in progress



Finally, I am only working with data from one company (Group# is same for all rows) but would like to eventually include tables with multiple Group#s. This gets complicated (or does it?) because an employee may be working at multiple companies and would of course have the same InsuredSSN.

Would it require changing anything to include multiple groups?

Thanks in advance!

[brickballer]

I may have found a solution just after posting (figures )

I haven't tested this yet for completeness, but a premliminary glance seems to indicate it's working...

SELECT TMD.*
FROM TMD
WHERE NOT EXISTS ( SELECT 1 FROM PMD WHERE TMD.[Insured SSN] = PMD.[Insured SSN] AND TMD.Relationship = PMD.Relationship);

I'll verify it's pulling correctly after lunch.

[brickballer]

Marked 'solved' as the initial question has been answered - I may come back with another post/thread if I can't get it to work with multiple group#s.

If anyone is interested I found the code here:
http://stackoverflow.com/questions/1...e-for-new-rows
and adapted it to my table names.