If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
1 to many query - trying to identify missing records
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. |
#2
|
|||
|
|||
1 to many query - trying to identify missing records
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" wrote in message ... 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. |
#3
|
|||
|
|||
1 to many query - trying to identify missing records
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" wrote in message ... 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. |
#4
|
|||
|
|||
1 to many query - trying to identify missing records
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. |
#5
|
|||
|
|||
1 to many query - trying to identify missing records
"Miskacee" wrote in message
... 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 |
Thread Tools | |
Display Modes | |
|
|