Using lookup table - how to create fields in parent 1

[jazminecat]

Hi, so in the interests of not repeating past mistakes, I'm trying to create my new db normalised from the start, and avoid using lookup fields in my table. But...since I've always done it that way (my bad) I'm having a brain freeze. I have 3 tables: tblEmployee, tlkpEmpEligibility, and tblIdentificationTypes

tblEmployee contains all employee information, including two fields to hold the two types of identification an employee must show to be employed. Eligibility1 and Eligibility2

tblIdentificationTypes contains a list of the types of identification that are accepted:
ID - autonumber primary key
IdentificationType - text containing list of types

the recordset for this is currently
1 Drivers License
2 Social Security #
3 Passport

etc.

tlkpEmpEligibility is a lookup table between the two with three fields

ID = autonumbered primary key
EmpID - the EmployeeID from the EmployeeTable
IdentificationTypeID - the ID from tblIdentificationTypes

my problem, each employee will have two records in the lookup table...I have created both one to many relationships, with the lookup table on the many side of both relationships.

and now my brain is stuck.
I need two fields - one for eligibility1 and one for eligibility2 in my employee table. But how do I code them without using a lookup field.

I have a form with all of the information from the employee table - how do I lookup the information from the tblIdentificationTypes table at the form level so that I can use it in a dropdown list when creating a new record? Do I need to create a query to be the source for those two fields then?

I know I am making this more complicated than it needs to be. And I know I know how to do this but I am drawing a complete blank. Any help is really appreciated.

 

[PHV]

Why aren't eligibility1 and eligibility2 FK referencing tblIdentificationTypes.ID ?
In a query you'll need 2 instances of tblIdentificationTypes:
SELECT E.ID, E.Name, I1.IdentificationType, I2.IdentificationType
FROM (tblEmployee AS E
INNER JOIN tblIdentificationTypes AS I1 ON E.eligibility1 = I1.ID)
INNER JOIN tblIdentificationTypes AS I2 ON E.eligibility2 = I2.ID

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jazminecat]

Qupte: Why aren't eligibility1 and eligibility2 FK referencing tblIdentificationTypes.ID ?

Hm, well I thought I needed a lookup table instead of a lookup field, so I made the tlkpEmpEligibility table to be the middleman. Should I actually eliminate the lookup table, and just keep the IdentificationTypes table? But then I would need a many to many relationship between the two, since each employee would have 2 Identification types, and each identification type could exist in any employee, and I thought the idea was to not have many-to-many relationships.

My question is still how to design the tblEmployee, and the employee data form, to show the two types of ID each employee presents without using a lookup field?

 

[jazminecat]

Perhaps I should clarify what's working instead of being so confusing.

In my form frmEmployee, I show all the information from the tblEmployee, including an eligibility1 and eligibilty2 field. They're text fields.

In my frmEmployee, for the eligibility fields, I have the following:
SELECT tblIdentificationTypes.ID, tblIdentificationTypes.EligType FROM tblIdentificationTypes ORDER BY tblIdentificationTypes.ID;

This allows me to select from a dropdown list the type of identification that's being shown. And it does store the information in the Employee table.

Where I am confused is this. There is no relationship between these two tables. If there is no relationship, how can I ensure that if I change something in the IDs list, like if for instance, social security isn't called that anymore. Yes, I know it's a stretch, but it's just an example.

Thanks everyone!

 

[lespaul]

Employees
EmployeID (PK) 1
EmployeeName Joe Blow
etc


Idenfications
ID (PK) 1 2
Type SSN DL

EmployeeIdentifications
EmployeeID (PK/FK) 1 1
ID (PK/FK) 1 2

Then you run a query:

SELECT EmployeeID, EmployeeName, Type
FROM Employees
INNER JOIN EmployeeIdentifications on Employees.EmployeeID = EmployeeIdentifications.EmployeeID
INNER JOIN Identifications on EmployeesIdentifications.ID = Idenfications.ID

you will get a result like:

1 Joe Blow SSN
1 Joe Blow DL

you will probably need to use a small subform to display the two matching identifications properly for each employee. If you need them on the same line, you can do a query similar to PHV's above where he joins to the same table twice.

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

 

[jazminecat]

hm, well I will try this but of course the query is empty when I run it. Because there are no identifications for the employees yet. SO I will make a subform on the main employee information form to enter and dislay this information.

Am I correct in assuming then, that by eliminating lookup fields, I necessarily have to use a separate query and therefore a subform for any information that would have previously been located directly in the table by using a lookup field? I'm sorry to keep on about this, but having always used them, i'm quite confused about how to avoid them, and now how avoiding them makes anyone's life easier. If my forms will now be full of subforms, that makes them clunkier for the end user, and for me as well.

 

[jazminecat]

please help me. I'm about out of patience with this, and am *this* close to just going back to the lookup field because really three days of trying to get something this simple to work, when I have written db's with huge backends full of VBA is just frustrating.

I now have three tables that are related like this;

http://img24.echo.cx/my.php?image=relationshipwindow6xw.png

but this doesn't seem to get me anywhere.

I can get the information onto the employee information form using two combo boxes, both with this property:

SELECT tblIdentificationTypes.ID, tblIdentificationTypes.IdentificationType
FROM tblIdentificationTypes
ORDER BY tblIdentificationTypes.IdentificationType;

for the combo box. It stores the type if ID that the employee presented, and another text box shows the number for each one. This information is stored in the employee table. Each employee has two forms if ID, and they both need to be in the table.

This query

SELECT tblEmployee.Emp_Last, tblEmployee.Emp_ID, tblEmployee.Emp_IDType1, tblEmployee.Emp_IDType2
FROM tblEmployee;

returns these results:

Last Name Emp_ID Emp_IDType1 Emp_IDType2
Smith 1 6 2
Jones 3 4 6
Pauli 4 1 2

Fine except it doesn't tell me which type each person has. rather useless.

I can't seem to

1. figure out how to store the information for each employee and their two forms of id in the lookup/linking/middle table shown in the picture. This was how I was told to set them up but it doesn't seem to really accomplish anything.

2.create a relationship either in the relationship window or in a query that will show me that information correctly.

3. figure out why I am going in circles with this thing.

I just need to be able to have my end user pick two forms of ID from a list, enter the numbers for each piece of ID that the employee gives her, and move on. <banging my head on my keyboard repeatedly.>

 

[PHV]

Simply reread my post.
You need only 2 tables.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886