LOOKUP Fields inTables

[Anita Taylor]

I have two tables: EMPLOYEES and DEPARTMENTS

Departments has only two fields: department numbers and
department names.

I want to have two fields in the Employees table - one
where I can select the department number from Departments
(I can do this with a LOOKUP field); the other displays
the actual name of the department based on what I selected
for Department Number.

Can anyone explain how to do this? I keep thinking it
should be relatively easy and I'm just making it harder
than it seems.

Thanks in advance.

 

[Rick B]

Well, you would not add two fields to the employee table, just one. Adding
two is a duplication of effort and makes the other table unneeded. Using a
lookup will show the user both fields, but will only store the shorter
number entry. Since the tables are then tied together, you can print or
display the department name in reports and forms.

If you go into your employee table and add a new field called "department"
or similar and in the type field select "Lookup Wizard..." it should walk
you right through it!


Take a look at your table relationships as well.


Hope that helps.

Rick B


I have two tables: EMPLOYEES and DEPARTMENTS

Departments has only two fields: department numbers and
department names.

I want to have two fields in the Employees table - one
where I can select the department number from Departments
(I can do this with a LOOKUP field); the other displays
the actual name of the department based on what I selected
for Department Number.

Can anyone explain how to do this? I keep thinking it
should be relatively easy and I'm just making it harder
than it seems.

Thanks in advance.

 

[Tim Ferguson]

I want to have two fields in the Employees table - one
where I can select the department number from Departments
(I can do this with a LOOKUP field); the other displays
the actual name of the department based on what I selected
for Department Number.

No you don't. You need one field, holding the DepartmentNumber[1]. When you
want to see the DepartmentName next to an Employee, then you can use a
query to join the tables, or get the form or report to carry out a
DLookup() to get the value.

Access is a relational database: use it relationally! (c) John Vinson 1973.

[1] Can anybody actually suggest a reason why it's better to have tables
like this, rather than something like

Departments
FullName(PK)
=============
English
Humanities
Classics
Sciences

Employees
EmpNum(PK) FullName BelongsTo(FK)
========== -------- -------------
120 Eric English
122 Samantha Classics
129 Tristram Sciences


.... which of course eliminates the need to join anything to anything?


B Wishes


Tim F

 

[Lynn Trapp]

Access is a relational database: use it relationally! (c) John Vinson
1973.

John must be a fortune teller, if he could copyright that statement in
1973... <g>

 

[Douglas J. Steele]

1973.

John must be a fortune teller, if he could copyright that statement in
1973... <g>

Actually, I think that's a count of how many times John's posted that
statement this month. (Note that it's only July 2nd...)

 

[Lynn Trapp]

Actually, I think that's a count of how many times John's posted that

statement this month. (Note that it's only July 2nd...)

<snork>