1 to many query - trying to identify missing records

[Miskacee]

I have a table with the following (this is a short example as there are
actuall 16 types, not 2)
1=dental
a=aetna
b=metlife
2-medical
c=metlife
d:=aetna

My employee record has 1 type of dental plan (a or b), 1 type of medical (c
or d). SOme of the records in this plan is missing, i.e. dental (1) for
example. I am trying to identify what is missing on each employee's record.

The problem is I can't do a 1:many since there is no identifier (employee
name/number) to tell me which employee has a particular benefit missing.

I created a crosstab query and it does show what is missing from each
employee but doesn't really work for what I need it to do.

Does anyone have any suggestions on how I can identify what is missing? I
have a query to identify those employees that have < 16 benefits. I then
filtered further down to 'link' up the list of 16 benefits to the list of
benefits of those employees that have less than 16.

Thank you for any assistance, I'm brain dead, trying to figure this one out.

 

[Jeff Boyce]

I couldn't tell from your example what data elements and structure you are
using.

"How" depends on "what"...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Clif McIrvin]

If I'm following you correctly, seems like you could write an outer join
between your employees table and your benefits table which would give
you a query showing every employee with all sixteen possible benefits.
Call it AllEmployeesAllBenefits.

Then, use the find unmatched query wizard between your employees table
and AllEmployeesAllBenefits to generate a list of missing combinations
in your employees table.

 

[Miskacee]

Thanks for your help. I tried what you suggested prior to sending out this
information. I figured it out. I had to add tbl_enrolled and tbl_enrolled1
and then the tbl_benefits in order to get the list of missing data. The
unmatched wouldn't work because there are thousands of enrolled employees and
the unmatched couldn't 'link' up the proper employee # with what was missing.

 

[Clif McIrvin]

Thanks for your help. I tried what you suggested prior to sending out
this
information. I figured it out. I had to add tbl_enrolled and
tbl_enrolled1
and then the tbl_benefits in order to get the list of missing data.
The
unmatched wouldn't work because there are thousands of enrolled
employees and
the unmatched couldn't 'link' up the proper employee # with what was
missing.

Glad you got it sorted.

Often 2/3s of the battle is figuring out what criteria one needs to use
to select the data one is looking for. Coding the query quite often is
really the easiest part.