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.

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

"Miskacee" <(E-Mail Removed)> wrote in message
news:B5494D9F-14E0-455F-8433-(E-Mail Removed)...
>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.

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.

--
Clif
Still learning Access 2003

"Miskacee" <(E-Mail Removed)> wrote in message
news:B5494D9F-14E0-455F-8433-(E-Mail Removed)...
>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.

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.


"Miskacee" wrote:

> 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.

"Miskacee" <(E-Mail Removed)> wrote in message
news:8A301C30-1CE8-49B9-82D5-(E-Mail Removed)...
> 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.

--
Clif
Still learning Access 2003