Basic Form/Subform question

[Guest]

My problem is a basic one of trying to understand how to construct drop-down
lists. I am very confused about what Access does with drop-down lists. Let
me state my understanding, and somebody please tell me if I am right or
correct me if I am wrong.
In Access Tables, the use of the Lookup Wizard causes problems because an ID
number is referenced instead of the actual data value, and at some point this
might cause a problem if queries are dependent upon the data in a Lookup
field (this is regardless of whether it is a Lookup Field or a Value List.)
On the other hand, Subforms within a Main Form can be used to enter data
from a drop down list but require an underlying Relationship between two
tables.
However, relationships need to be created with a junction table if the
tables exist in a many-to-many relationship.
An example may help. In a patient database, the gender is asked for. How
to create a drop down list in a subform with “Male†or “Female†as the
choices? There are two underlying tables, “tblDemographics†and “tblGenderâ€.
The two tables will obviously have a many-to-many relationship, and
according to what I have read, will need a junction table to establish this
relationship.
However, in my reading it seems that Forms and Subforms relate on a ‘one’
(the MainForm†to ‘many’ (the SubForm) relationship.
If everything I have stated is true, how can a Form/Subform be constructed
for the example given?
The same question holds for other similar drop down lists. Another example,
a drop down list for a clinical score consisting of 6 choices of clinical
condition. Again obviously a many-to-many relationship.
Also, please let me know if there is some easier way to perform this task
that I am overlooking.
Tx

 

[John Vinson]

My problem is a basic one of trying to understand how to construct drop-down
lists. I am very confused about what Access does with drop-down lists. Let
me state my understanding, and somebody please tell me if I am right or
correct me if I am wrong.

Ok... will try...

In Access Tables, the use of the Lookup Wizard causes problems because an ID
number is referenced instead of the actual data value,

No. It's the opposite. The actual value stored in your table *IS* the
ID number; that basic fact is concealed from your view by the combo
box, which is displaying a DIFFERENT value (usually text) from a
DIFFERENT table. What you *see* is not what is *there*.

and at some point this
might cause a problem if queries are dependent upon the data in a Lookup
field (this is regardless of whether it is a Lookup Field or a Value List.)

Not if the query is done correctly. Whether or not there is a Lookup
Field in the table, you can choose to include the lookup table in a
Query. If you need to see the looked-up text in a Report, say, simply
join your main table to the lookup table (or tables), and include the
field from there.

On the other hand, Subforms within a Main Form can be used to enter data
from a drop down list but require an underlying Relationship between two
tables.

Well... yes, but Combo Boxes ("drop down lists" proper name) can be
used on a mainform, on a subform, or both. There really is no
connection between using a subform and using a combo box; they're just
two different tools.

However, relationships need to be created with a junction table if the
tables exist in a many-to-many relationship.

Relationships need to be created if you have a ONE to many
relationship, too! Creating a Lookup Field actually DOES create a
relationship, and then conceals it from view; one of the criticisms of
the feature is that it creates such a relationship, and the
accompanying indexes, even if a relationship already exists!

I would recommend creating all your Tables, *WITHOUT* lookup fields;
creating all your Relationships, in the relationships window; and
using Combo Boxes liberally as needed on forms. I will confess that
the one advantage of having a Lookup Field in a table is that it's a
mite easier to put a combo box on a form - but it's dead easy even
without.

An example may help. In a patient database, the gender is asked for. How
to create a drop down list in a subform with “Male” or “Female” as the
choices? There are two underlying tables, “tblDemographics” and “tblGender”.
The two tables will obviously have a many-to-many relationship, and
according to what I have read, will need a junction table to establish this
relationship.

Ummm...

No.

One person will not have many genders (unless you're in a very
particular medical specialty). You have a very simple one to many
relationship here: each record in tblGender will be related to many
recrods in tblDemographics, but each person in tblDemographics will
have only one gender. In fact, unless you're dealing with multiple
gender identities, I wouldn't bother having a tblGender at all; just
use a Combo Box on the form with a List of Values "Male";"Female".

However, in my reading it seems that Forms and Subforms relate on a ‘one’
(the MainForm” to ‘many’ (the SubForm) relationship.

That is correct. In a real many to many relationship (for instance the
Northwind Sample databases OrderDetails table) there is are three
tables involved: Orders (an Order can consist of many Products),
Products (each Product can be part of many Orders), and OrderDetails.
There is a one to many relationship from Orders to OrderDetails (used
on the form, using OrderID as the linking field), and another one to
many relationship from Products to OrderDetails.

If everything I have stated is true, how can a Form/Subform be constructed
for the example given?
The same question holds for other similar drop down lists. Another example,
a drop down list for a clinical score consisting of 6 choices of clinical
condition. Again obviously a many-to-many relationship.

This is a very different issue from Gender. Each patient has only one
gender, but (I presume) each patient might have zero, one, two or more
conditions.

There you need a THIRD TABLE. If each Patient may have multiple
Conditions, and each Condition may affect many Patients, then you need
a PatientConditions table with fields for the PatientID and the
ConditionID (linked to the respective tables' Primary Keys). The
Subform would be based - not on Conditions - but on PatientConditions;
you'ld have a Combo Box on the subform based on Conditions allowing a
different condition to be selected for each record. You might have
another field in PatientConditions, say a Severity value (which again
might use a lookup table - though not a Lookup Field - listing the
valid severities), perhaps a Memo or Text field for comments, date of
onset, etc. as needed for your application.

Also, please let me know if there is some easier way to perform this task
that I am overlooking.
Tx

See if this description helps...

John W. Vinson[MVP]

 

[Guest]

John,
I'm reading your response late at night, and it seems to make sense, but
I'll let you know tomorrow if it really did!
thank you for your time and effort
Rocky