Lookup Values

[Guest]

Scenario:
Relational Database with following structure:
Tables: Residents
Resident Next of Kin
Resident Medical Details

Each of these tables has a [ResidentName] field. The [ResidentName] field in
the Residents Table is unique and has an associated ID (autonumber). The
relationship of the Residents table to the Resident Next of Kin and Resident
Medical Details tables is of ‘One’ to ‘Many’. The field [ResidentName] in the
Residents Table is the ‘One’ and the fields [ResidentName] in the Resident
Next of Kin and Resident Medical Details Tables are the ‘Many’ side of the
relationship (primary key to foreign keys).

Objective:
I have created Forms based on each of these tables. To speed things up I
configured the [ResidentName] fields in the Resident Next of Kin and Resident
Medical Details table as Lookup fields:
The Lookup Properties are as follows:
Display Control: Combo Box
Row Source Type: Table/Query
Row Source: SELECT tblResidents.ResidentName, tblResidents.FormerResident
FROM tblResidents WHERE (((tblResidents.FormerResident)=No)) ORDER BY
tblResidents.ResidentName;
This had the desired result where I can quickly select the name from a
drop-down list on the Form.

New Problem:
In a separate posting (Values with number and text 5/26/20005) Rick Brandt
replied to my query with these additional comments: “…if you have set your
current ValueList up in the Table design you should get rid of that. Lookup
tools like ComboBoxes belong in forms only, not in tables.â€

An aside Observation:
While I have read the ‘Evils of Lookup Fields in Tables’ I am not sure I
fully understand it. Illustrated examples associated with each of the points
made might help, particularly for access novices like me.

Revision:
To apply Rick Brandt’s advice I applied the following steps:
1.Opened the Table ‘Resident Next of Kin’ and in the Lookup property of the
‘ResidentName’ field reverted to Text box as the Display Control.
2.Created a new query, added the ‘Resident’ Table to the Design grid and
dragged the [ResidentName] field and the [FormerResident] (datatype: Yes/No)
field onto the field display grid.
3.In the ‘Resident Next of Kin’ Form I deleted the [ResidentName] field.
4.Executed the Combo Box Wizard:
- selected the query to provide the values for the combo box
- selected the Field [ResidentName] which contains the values
- selected to store the selected value of the [ResidentName] field in the
Resident Next of Kin Table.
Result: Successful.
Is this the correct procedure?

 

[John Vinson]

Scenario:
Relational Database with following structure:
Tables: Residents
Resident Next of Kin
Resident Medical Details

Each of these tables has a [ResidentName] field.

They shouldn't.

Names are NOT unique. ID's are unique or can be made so.

The [ResidentName] field in
the Residents Table is unique

So if you happen to have two residents both named Jane Smith you must
store a "fake" name like Jane Smith_1? Why? Names are not unique;
that's reality, and you should accommodate your database to reality,
not vice versa!

and has an associated ID (autonumber).

I take it you're using Microsoft's misdesigned, misleading, obnoxious
so-called Lookup Wizard? See

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

for some reasons to avoid it. Your tables DO NOT CONTAIN names. They
contain long integer ID's, which are concealed from your view by the
lookup misfeature.

The relationship of the Residents table to the Resident Next of Kin and Resident
Medical Details tables is of ‘One’ to ‘Many’. The field [ResidentName] in the
Residents Table is the ‘One’ and the fields [ResidentName] in the Resident
Next of Kin and Resident Medical Details Tables are the ‘Many’ side of the
relationship (primary key to foreign keys).

Note that if you have used the lookup wizard, it will create a
duplicate, redundant relationship even if you have defined the
relationship in the Relationships window.

Objective:
I have created Forms based on each of these tables. To speed things up I
configured the [ResidentName] fields in the Resident Next of Kin and Resident
Medical Details table as Lookup fields:
The Lookup Properties are as follows:
Display Control: Combo Box
Row Source Type: Table/Query
Row Source: SELECT tblResidents.ResidentName, tblResidents.FormerResident
FROM tblResidents WHERE (((tblResidents.FormerResident)=No)) ORDER BY
tblResidents.ResidentName;
This had the desired result where I can quickly select the name from a
drop-down list on the Form.

New Problem:
In a separate posting (Values with number and text 5/26/20005) Rick Brandt
replied to my query with these additional comments: “…if you have set your
current ValueList up in the Table design you should get rid of that. Lookup
tools like ComboBoxes belong in forms only, not in tables.”

An aside Observation:
While I have read the ‘Evils of Lookup Fields in Tables’ I am not sure I
fully understand it. Illustrated examples associated with each of the points
made might help, particularly for access novices like me.

You should NOT BE STORING the resident's Name in any of these tables.
You should instead be storing the unique numeric resident ID in all of
the child tables.

Revision:
To apply Rick Brandt’s advice I applied the following steps:
1.Opened the Table ‘Resident Next of Kin’ and in the Lookup property of the
‘ResidentName’ field reverted to Text box as the Display Control.
2.Created a new query, added the ‘Resident’ Table to the Design grid and
dragged the [ResidentName] field and the [FormerResident] (datatype: Yes/No)
field onto the field display grid.
3.In the ‘Resident Next of Kin’ Form I deleted the [ResidentName] field.
4.Executed the Combo Box Wizard:
- selected the query to provide the values for the combo box
- selected the Field [ResidentName] which contains the values
- selected to store the selected value of the [ResidentName] field in the
Resident Next of Kin Table.
Result: Successful.
Is this the correct procedure?

Not if you're actually storing the text name, no.

John W. Vinson[MVP]

 

[Guest]

Thank you, John, for your feed-back.

You should NOT BE STORING the resident's Name in any of these tables.
You should instead be storing the unique numeric resident ID in all of
the child tables.

I have been re-structuring my Tables, Queries, Forms and Reports. Could you
please verify that I am doing the right thing based on the following
information:

Tables:
1. Added a new field to the Resident Next of Kin Table = [ResidentID] with
field datatype = number
2. Established a relationship (with enforced integrity) between the
[ResidentID] (autonumber and Primary key) field of the Resident Table and the
[ResidentID] field of the Resident Next of Kin Table.
3. Deleted the field [ResidentName] (Lookup value was here) in the Resident
Next of Kin Table

Forms:
1. In Design View of the Resident Next of Kin form I deleted the field
[ResidentName] and replaced it with a combo box control. In the Combo box
wizard, I selected the table Residents and the [ResidentName] field. I then
opted to store that value in the [ResidentID] field of the Resident Next of
Kin Table.
2. In Form View the control displayed a drop down list of names. I selected
to enter a number of new records.

Result: In the Resident Next of Kin table these new records display the
unique resident id number only in the [ResidentID] field. Is this correct?

Scenario:
Relational Database with following structure:
Tables: Residents
Resident Next of Kin
Resident Medical Details

Each of these tables has a [ResidentName] field.

They shouldn't.

Names are NOT unique. ID's are unique or can be made so.

The [ResidentName] field in
the Residents Table is unique

So if you happen to have two residents both named Jane Smith you must
store a "fake" name like Jane Smith_1? Why? Names are not unique;
that's reality, and you should accommodate your database to reality,
not vice versa!

and has an associated ID (autonumber).

I take it you're using Microsoft's misdesigned, misleading, obnoxious
so-called Lookup Wizard? See

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

for some reasons to avoid it. Your tables DO NOT CONTAIN names. They
contain long integer ID's, which are concealed from your view by the
lookup misfeature.

The relationship of the Residents table to the Resident Next of Kin and Resident
Medical Details tables is of ‘One’ to ‘Many’. The field [ResidentName] in the
Residents Table is the ‘One’ and the fields [ResidentName] in the Resident
Next of Kin and Resident Medical Details Tables are the ‘Many’ side of the
relationship (primary key to foreign keys).

Note that if you have used the lookup wizard, it will create a
duplicate, redundant relationship even if you have defined the
relationship in the Relationships window.

Objective:
I have created Forms based on each of these tables. To speed things up I
configured the [ResidentName] fields in the Resident Next of Kin and Resident
Medical Details table as Lookup fields:
The Lookup Properties are as follows:
Display Control: Combo Box
Row Source Type: Table/Query
Row Source: SELECT tblResidents.ResidentName, tblResidents.FormerResident
FROM tblResidents WHERE (((tblResidents.FormerResident)=No)) ORDER BY
tblResidents.ResidentName;
This had the desired result where I can quickly select the name from a
drop-down list on the Form.

New Problem:
In a separate posting (Values with number and text 5/26/20005) Rick Brandt
replied to my query with these additional comments: “…if you have set your
current ValueList up in the Table design you should get rid of that. Lookup
tools like ComboBoxes belong in forms only, not in tables.â€

An aside Observation:
While I have read the ‘Evils of Lookup Fields in Tables’ I am not sure I
fully understand it. Illustrated examples associated with each of the points
made might help, particularly for access novices like me.

You should NOT BE STORING the resident's Name in any of these tables.
You should instead be storing the unique numeric resident ID in all of
the child tables.

Revision:
To apply Rick Brandt’s advice I applied the following steps:
1.Opened the Table ‘Resident Next of Kin’ and in the Lookup property of the
‘ResidentName’ field reverted to Text box as the Display Control.
2.Created a new query, added the ‘Resident’ Table to the Design grid and
dragged the [ResidentName] field and the [FormerResident] (datatype: Yes/No)
field onto the field display grid.
3.In the ‘Resident Next of Kin’ Form I deleted the [ResidentName] field.
4.Executed the Combo Box Wizard:
- selected the query to provide the values for the combo box
- selected the Field [ResidentName] which contains the values
- selected to store the selected value of the [ResidentName] field in the
Resident Next of Kin Table.
Result: Successful.
Is this the correct procedure?

Not if you're actually storing the text name, no.

John W. Vinson[MVP]

 

[John Vinson]

I have been re-structuring my Tables, Queries, Forms and Reports. Could you
please verify that I am doing the right thing based on the following
information:

Give the man a gold star. <g>

Exactly correct! Is the Form working as you would expect?

John W. Vinson[MVP]

 

[Guest]

Thanks John for the "virtual" gold star.
All Forms are working fine!
Is the Northwind sample database incorrect in having its Lookup values in
the tables?

 

[John Vinson]

Thanks John for the "virtual" gold star.
All Forms are working fine!
Is the Northwind sample database incorrect in having its Lookup values in
the tables?

There is absolutely NOTHING wrong with having lookup values in tables.
I do it all the time, and so do most Access developers.

What a lot of us DO find objectionable is the use of the Lookup field
type *IN TABLE DATASHEETS*. Lookups - combo boxes - should be used
routinely *on Forms*; table datasheets should be kept under the hood,
and when they're opened at all for debugging or development, I feel
that they should show the actual content of the table. The Lookup
feature conceals the actual data in the table behind the lookup.

John W. Vinson[MVP]