Easy One: Lookup Column

[ant1983]

So im starting to build my database and starting with the tables (obviously)...

So lets say i create a database and start with my tables. Lets say the
first table is called "tblEmployee" with the following fields:

- ID (Auto Num and Primary Key)
- FirstName (Text)
- Surname (Text)
- DateOfBirth (Date)
- Department (Lookup Field)

....and the 2nd tableis called "suptblDepartment" with the following fields:

- ID (Auto Num and Primary Key)
- Department (Text)

Now in the 2nd table i populate the Department field with the following
values:

- Human Resources
- IT
- Finance
- Credit Control
- Operations

************************************************

So my questions are in terms of best practice:

1 - When selecting the Lookup Wizard option under Field Type (for Department
in the first table); i select the first option on the first screen; then i
select my table (suptblDepartment) on the 2nd screen; - Then what should i
do? Should i be selecting the ID or Department field?

2 - On the last screen it promts you for a label - what label is this?

3 - On the last screen it also asks you if you want to store multiple values
- What does this mean?

i'm using 2007...

Thanks!

 

[tina]

the best practice is to *not* use Lookup fields in your tables. for more
information, see http://www.mvps.org/access/lookupfields.htm.

you can use combobox controls or listbox controls in your *forms* with no
problem; and forms are where you should be
entering/editing/deleting/reviewing data, not in tables.

hth

 

[John W. Vinson]

So im starting to build my database and starting with the tables (obviously)...

So lets say i create a database and start with my tables. Lets say the
first table is called "tblEmployee" with the following fields:

- ID (Auto Num and Primary Key)
- FirstName (Text)
- Surname (Text)
- DateOfBirth (Date)
- Department (Lookup Field)

Before you get too much deeper here read:

http://www.mvps.org/access/lookupfields.htm

Lookup fields are useful primarily to make it easier to enter and edit data in
table datasheets - but in a properly designed Access application users should
never even SEE a table datasheet, much less use one for data entry! They
should use Forms (with combo boxes, natch) instead.

...and the 2nd tableis called "suptblDepartment" with the following fields:

- ID (Auto Num and Primary Key)
- Department (Text)

Now in the 2nd table i populate the Department field with the following
values:

- Human Resources
- IT
- Finance
- Credit Control
- Operations

************************************************

So my questions are in terms of best practice:

1 - When selecting the Lookup Wizard option under Field Type (for Department
in the first table); i select the first option on the first screen; then i
select my table (suptblDepartment) on the 2nd screen; - Then what should i
do? Should i be selecting the ID or Department field?

2 - On the last screen it promts you for a label - what label is this?

3 - On the last screen it also asks you if you want to store multiple values
- What does this mean?

Well, I would never use a lookup field *nor* would I ever use a multivalue
field. If you need help doing so, please ask again, maybe someone without my
biases will help. I will note that neither of these features is ever
*necessary*; you can use a Form with a Combo Box to get the "lookup"
capability, and a related Table with a Subform to get multiple values.