Lets talk about the evil lookup fields again 2

[jazminecat]

oh I know it's been beaten to death. I know they're bad. But, how do I set up a table to use a foreign key from another table without using a lookup field? Or, how do I make a field that is a combination of the primary keys from two other tables, without using a lookup field with a query as it's row source? For example, look at the answer i received here:

http://www.tek-tips.com/viewthread.cfm?qid=1070071&page=1

doesn't this ask me to use a foreign key in a table, and if I do that, don't I have to plug that foreign key into the row source of a table, and in doing that, isn't that a lookup field?

 

[willir]

Lookup fields in Access are defined when designing the table.

To create your relationships, from the database, "Tools" -> "Relationships".

Add your tables that are to be "related".
Click and drag the primary key on one table to the foreign key in the related table.
A popup window will allow you to further define the relationship. Select "Enforce referential integrity". Read up on cascading updates and deletes before invoking. I personally do not use these features.

In the form, you can change the text box to a combo or list box and tweak the properties so the end user sees the text desciption of the foreign key but the foreign key is used for the linkage. The forum on forms will help you here.

My thoughts on lookup fields...
- They "hide" the real data so you can not see the "real" value for a field.
- The are not compatable if you upgrade to a more robust platform

The only thing "good" about Lookfields is when you use a wizard to create the form, Access automatically creates the combo box for you. Okay when you are in a hyrry -- perhaps -- but definitely not worth the pain later if you maintain the lookup field.

 

[jazminecat]

I posted this in my other question too, but I have the relationships set up like that here:

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

on my form, my user needs to select from the list of identification types, and she needs to select two for each employee (you have to show two forms of ID to be hired.)

I store that information the employee table, but it's just the numbers. I have nothing in that middle table but was told I needed it. and i can't run a query listing all employees and their two forms of ID, because the employee table and the identificationtype table aren't actually related.

 

[dhookom]

You would create a main form based on employee and a subform based on the employee identification table. The subform would use a combo box to select the IdentificationType from the identification types table.

This isn't much different than an order form with two orderdetail records. The employee table is similar to the order table. The employee identification table resembles the order details table. The identification type table is similar to the products table.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[willir]

What you have displayed is akin to a job skills table.
A person can have many skills
Many people have the same skill

For example,
a person may speak many languages.
Many people speak english (or french, or ...)

In your case, your relationship suggests...
One person has many identification types
Many people can have the same identification type

This is a many-to-many relationship

I am not sure of examples to use for identification types, but using my first example of skill set, it works like this...

tblEmployee
EmployeeID - pk
EmployeeLN - last name
EmployeeFN - first name

tblSkillp
SkillID - pk
SkillName
SkillLevel

tblEmployeeSkill
EmployeeID - fk to tblEmployee
SkillID - fk to tblSkill
DateAccessed - date

Primary key = EmployeeID + SkillID

Now with data
[tt]
tblEmployee
EmployeeID EmployeeLN EmployeeFN
1 Asimov Issac
2 Leguin Ursula
3 Jordan Robert

tblSkill
SkillID SkillName SkillLevel
1 TroubleShoot 1
2 TroubleShoot 2
3 TroubleShoot 3
4 BreakFix 1
5 BreakFix 2
6 BreakFix 3

tblEmployeeSkill
EmployeeID SkillID
1 3
1 2
2 1
2 3
3 2
3 2
[/tt]

So
Asimov is a level 3 trouble shooter and a level 2 break / fix.
LeGuin is a level 1 trouble shooter and a level 3 break / fix.
Jordoan is a level 2 for both trouble shooting and break / fix.

In this example, the tblEmployeeSkill is updated accordingly. There are several ways of implementing this in a form...
- Create a subform based on tblEmployeeSkill. If the subform is to be embedded in a form based on tblEmployee, EmployeeID is hidden and SkillID is set to a combo box pointing to tblSkill. If the subform is embedded in a form based on tblSkill, the SkillID is hidden, and EmployeeID becomes a combo box.
- Create a form on tblEmployeeSkill. EmployeeID is a combo box pointing to tblEmployee and SkillID is a combo box pointing to tblSkill.

Referencing your previous post and graphic...
1) The Identification Type does not seem to add much value as it stands. You can actually use a value list within a combo box.
2) EmpID in tblEmployeeIdentification is fine, but you have IdentificationType in tblEmployeeIdentification instead of the ID field from tblIdentification.
3) tblEmployeeIdentification has it's own ID (primary key). This may be necessary in some cases, but I suspect not in your situation. By making EmpID + Identification.ID as the primary key, you avoid creating duplicate records.
4) If you are dealing with identification, you may wish to use a couple of dates -- ValidUntilDate and DateEntered since old ID may have little value.

With this in mind...

tblEmployee
EmpID - pk
Emp_first
Emp_last
...etc

tblIdentification
IdentificationCode - pk
IdentificationName

Why use IndentificationCode instead of an ID? Using a string / text code allows you to use smart codes so that you will know the IdentificationName on sight based on the code. SSN = Social Security Number, PSPRT = Passport, etc.

tblEmployeeIdentification
EmpID - fk to tblEmployee
IdentificationCode - fk to tblIdentification

Primary key = EmpID + IdentificationCode

Sample data, similar to what LesPaul provided...
[tt]
tblEmployeeIdentification
EmpID IdentificationCode
1 SSN
1 PSPRT
2 SSN
2 DRVLICN
3 SSN
3 BRTHCERT
[/tt]

...Moving on, Dates
Well, what about validation dates? As dicussed, if you are capturing indentification numbers, it makes sense to capture meaningful date....

tblEmployeeIdentification
EmpID - fk to tblEmployee
IdentificationCode - fk to tblIdentification
ValidToDate - date
DateEntered - date

...Moving on, Two tables
PHV made an interesting comment on using two tables. The tblIdentification table has very little info on it.

In the sample data I provided, you can fairly easily guess what the indentification type is based on the IndentificationCode. So why even have a tblIdentification table? You can actually present the PickList by using a ValueList within the combo box.

Personally, I would keep the tblIdentification table. By using a code to identify the indentification document, you can exclue the tblIdentification from many of your queries -- you would see the code "SSN" instead of "Social Security Number". However, you still have a central point to reference for your combo boxes, and reports for the proper name.

You made an interesting point about what if the name of the document changes. Suppose "Social Security Number" -> "Social Insurence Number". At least two ways to handle this. Cascade updates in the relationship. Keep the SSN record, and enter a new record for the SIN number.

Enough rambling.
Richard

 

[dhookom]

I'm not the OP in this thread but I know Richard deserves a star for his "rambling".

Great response!

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[jazminecat]

Richard does indeed deserve a star for this! Now using the code, instead of a number in tblIdenfitication, I can make my queries behave properly as well. Thanks Richard, for a simple elegant solution to my problem of the week. 9 months away from programming and I seem to have forgotten everything I knew. But you've been an incredibly patient advisor, and I truly appreciate your expertise, and that of everyone else here who's been so willing to assist me! Have a great weekend!

 

[willir]

Shucks guys. Glad I helped jazminecat. And thanks Duane for your kind words -- it means a lot.