select multiple items combo box

[Chris Governo]

Hi all,
I am having some trouble. I am creating a form that where for certain
categories the user needs to be able to choose multiple items. I originally
tried using a list box but after reading some of the posts here, I decided to
try using subforms as I need the data chosen to be saved in a table. I need
to use multiple sub forms as there are different categories that need to meet
this criteria. I am having trouble linking the data the way I want. I made
fields named ID in each "category" table and made relationships between the
primary key in the main table and these fields (I also gave each"category"
table a UID autonumber, don't know if this is wrong). I then made a query
with the fields in the main table and the ID and necessary fields from the
other tables. I then began to make sub forms in the main form. This seems
to work as when I enter data in the sub forms it shows up in the tables with
the ID field matching the ID in the main table. However, nothing shows up in
the query, and I will need to run reports from this query later.
Sorry to be long winded. I guess what I am asking is does this sound like
it is set up right (is the query necessary to make the sub forms work?)
Referrential integrity is enforced in the relationships.
Also- basically the tables for these sub forms have the fields remote ID, a
UID, and a field that contains lookup values for the user to choose from. Is
it better to type the values in using the wizard or create a table with the
values and reference it. I want to do whatever will keep me out of trouble
later on!
-Thank you for any advice or help you can lend

 

[Ken Sheridan]

Its not clear (to me at least) from your description whether you need
separate category tables or not as you have not said what these 'categories'
are in real world terms. If the multiple values you need to assign to each
record in the main table are all values of an attribute of a single entity
type (e.g. Categories), then you should have just the one Categories table,
with each category represented by a row in the table. Assuming the category
names are distinct you need just one column, Category, in the table. The
column will be of text data type and the table's primary key. If you need to
associate multiple values of different entity types then you need one table
for each entity type.

Firstly I'll assume scenario 1, where the values are all attributes of the
same entity type:

As each row in the main table underlying your form needs to have more than
one category assigned to it, what you have is a many-to-many relationship
type between the main table and the categories table. A many-to-many
relationship type is modelled by a third table which has two foreign key
columns each of which references the primary key of the two 'referenced'
tables. Do if the primary key of your main table is called MyID then the
third table would have columns MyID and Category. Note that MyID msust not
be an autonumber in this table, even if the primary key MyID column of the
main table is an autonumber.

The third table should be related to the two referenced tables in
many-to-one relationships and referential integrity enforced. In the
relationship between categories and the main table cascade updates should
also be enforced as a 'natural' key is used. If MyID in the main table is an
autonumber 'surrogate' key, then it is not necessary to enforce cascade
updates in its relationship with the third table, but if it’s a natural key,
then they should.

In the form bound to the main table you need just the one subform, based on
the third table and linked to the main parent form on the MyID columns. This
subform should be in continuous form view and contain just one control, a
combo box bound to the Category, column and with a RowSource of:

SELECT Category FROM Categories ORDER BY Category;

As few or as many categories can now be assigned to the current record in
the man form simply by inserting records in the subform, selecting from the
combo box's list in each case.


With scenario 2, where there is more than one entity type involved and you
need to associate multiple values of each with the main form's record then
you simply have a separate table for each entity type, a separate table to
model the many-to-many relationship between it and the main table, and a
separate subform, set up as above, bound to each of the tables modelling the
many-to-many relationship types.

In some cases you will not be able to use a single column table as in my
Categories example above. Lets say the main table is bound to a Companies
table and you want to record multiple contacts per company, but some contacts
may represent more than one company, e.g. a lawyer acting on behalf of many
companies. In this case you'd have a Contacts table with columns ContactID,
FirstName, LastName etc. The CompanyContacts table modelling the
relationship between Companies and Contacts would have columns CompanyID and
ContactID and the combo box in the subform would be set up as follows:

ControlSource: ContactID

RowSource: SELECT ContactID, FirstName & " " & LastName FROM Contacts
ORDER BY LastName, FirstName;

BoundColum: 1
ColumnCount: 2
ColumnWidths: 0cm;8cm

If your units of measurement are imperial rather than metric Access will
automatically convert the last one. The important thing is that the first
dimension is zero to hide the first column and that the second is at least as
wide as the combo box.

You can then select contacts by name but the value of the combo box, and
hence the column in the underlying table, would be the hidden ContactID. A
surrogate numeric key is necessary in this case as names can be duplicated.
In fact in this case you'd really need to show one or more other columns in
the combo box's list to distinguish between possible duplicate names.

With scenario 2 one issue you should consider carefully is whether what
might appear at first sight to be different entity types are really such, or
are they in fact different attributes of the relationship type between the
main table and the 'category' table. To give an example, I'm currently
helping out a contact of mine in Canada with a company registration database.
This requires each company to be have officers, directors and shareholders
associated with it. Often they are the same people for the company, and also
they can be associated with more than one company. These are not three
separate entity types, however, but different attributes of the relationship
type between Companies and Personnel (a relationship type is really just a
special kind of entity type so has its own attributes, just as a more
concrete entity type does), so the table which models the relationship type
has columns CompanyID, PersonnelID and Capacity, the last containing values
Officer, Director or Shareholder.

Ken Sheridan
Stafford, England

 

[Chris Governo]

Thank you Ken for taking the time to reply to my post. My answer may be in
your message but I will have to read it over a few times to fully comprehend
it. I fear however I was too confusing in my post, I'd like to elaborate to
be sure I have gotten my situation accross correctly. This database is for
collecting information about patient safety so the information is all text
and it is based on a form with checkoff boxes. I thought about using a
similar set up with the yes/no check off boxes in my database in which case
each item would have it's own column and be recorded on one row in one record
in one table, but I thought the form would be too large and not so user
friendly (although this might be the more simple solution. Which brought me
to using subforms to break down the data hierarchically and possibly save
space. For example if there was a communication issue the user could click a
button on the form and a sub form would open the user could select from
various communication issues (one or more) and that information would be
saved.
So far my set up is such:
Risks Identified table (main table where adverse events are logged contains
info like pt ID date, time, person entering data and such)
Then I have tables for the categories where the user might want to pick
multiple items i.e. the Communication table which includes a unique
identifier, a foriegn key with the same name as the primary key in the risks
identified table and with a relationship to that risks identified ID field,
and a field that gets data using the lookUp tool from values I typed in using
the wizard (or from a table set up with the values (text) I don't know which
is the better method).
I created a form based on the Risks Identified table and am in the
process of putting subforms in the table based on the tables for the
categories (of which there are about 7) where the user may want to pick
multiple items. I am trying to make these subforms look more like a list box
than a table.
I want to make sure I am on the right track. I will continue to examine
your reply.
Thank you

 

[Ken Sheridan]

Having multiple Boolean (Yes/No) columns is one of the seven deadly sins of
database design; its known as ‘encoding data as column headings’, so don’t be
tempted to do that. Data should only be stored as values at column positions
in rows in tables; its called the ‘information principle’. Generally
speaking a well designed table will be tall and skinny rather than short and
fat.

If we consider your RisksIdentified table in the context of the database
relational model, what it represents is a many-to-many relationship type
between two entity types Patients and Risks, for each of which would be
represented by a table. The Patients table is pretty straightforward, so I
won’t go into that other than to say it would have one row per patient and a
numeric primary key PatientID.

The Risks table would have one row per risk type. It sounds to me like this
table would have seven rows, each representing a risk type currently
represented by your separate tables.

The next question to be asked is whether the RisksIdentified table models
the only assessment of a patient’s vulnerability to a set of risks, or
whether it is one of a number of time differentiated assessments.

If we assume a single assessment then columns such as the date, time, person
entering data are attributes of the patient and would go in the Patients
table; in the language of the relational model they are said to be
‘functionally dependent’ on the key of Patients. The RisksIdentified table,
by virtue of its modelling the many-to-many relationship type between
Patients and Risks, would have PatientID and RiskID foreign key columns
referencing the primary keys of Patients and Risks. Any other columns in
this table would model attributes of the specific identification of the
particular risk in question for the patient in question, i.e. they’d be
‘functionally dependent’ on the key of the table.

Diagrammatically the model would look like this:

Patients----<RisksIdentified>----Risks

Where the < and > signs represent the ‘many’ ends of each relationship.

The appropriate set-up for this model would be form based on Patients with a
continuous view subform based on RisksIdentified in the way I described in my
previous reply.

The alternative model, of multiple time differentiated assessments of a
patient’s risks, each involving a set of risks would simply involve the
introduction of another table between Patients and RisksIdentified,
RiskAssessments say. In this model columns such as the date, time, person
entering data are attributes of this RiskAssessments table, and it would
include a foreign key PatientID column referencing the key of Patients.
RisksIdentified would now have a RiskAssessmentID column referencing the key
of RiskAssessments rather than a PatientID foreign key column.

Diagrammatically the model would look like this:

Patients----<RiskAssessments----<RisksIdentified>----Risks

In this model the main form would be based on RiskAssessments, but the
subfrom would be the same.

Ken Sheridan
Stafford, England

 

[Chris Governo]

Hi Ken,
Thank you. you have been very helpful. Of course I have further questions.
What if I want to allow the user to choose multiple items in a specific risk
category and have it have a similar interface to a list box where the user
chooses a few items? For example say you have the risk category
"Communication"
Further broken down into:
1-Communication problem MD to MD
2-Communication problem MD to RN
3-Communication problem RN to RN
In this scenario I would like to allow the user to be able to choose more
than one of these options.
Would it be set up like this:
Patients-----<RisksIdentified>----Risks>------communication

Also for the items where the answer might be yes or no like "Was MD
notified" yes or no. If I do not use the yes or no option in the data type
option, how should I go about organizing this data. Should I make a yes no
table and use lookup to reference that table.
Thanks again for your help

 

[Ken Sheridan]

Chris:

Rather than have a Communications table (which would amount to encoding data
as a table name, and is therefore verboten) I'd suggest a more generic
RiskDetails table, which would have two columns, one a foreign key RiskType
say, which references the primary key of the Risks table, the other
RiskDetail which would contain the values 'MD to MD', 'MD to RN' etc. The
RisksIdentified table would now reference this table rather than the Risks
table, so the model would look like this:

Patients-----<RisksIdentified>----RiskDetails>----Risks

As you see the Risks table is no longer directly referenced by
RisksIdentified, so if you have situations where you'd want to select a
Communication risk say, but not break it down into 'MD to MD', 'MD to RN'
etc, then you should include a row in the RiskDetails table with a RiskType
of Communication and a RiskDetail of something like 'Unspecified'. Do
similarly for other risk categories.

As regards the "Was MD notified" type of value you could simply include a
MDNotified column of Boolean (Yes/No) data type in the RisksIdentified table.
That would be fine if it’s the only 'notification' question you want to
answer in relation to a risk detail identified for a patient, but if there
are multiple 'notification' questions per risk detail identified per patient
the you'd have a separate Notifications table. This would be related to
RisksIdentified in a many-to-one relationship. The primary key of
Risksidentified is a composite one of PatientID and (with the amended model
as above) RiskDetail (this assumes a single set of time independent
identified risks per patient as discussed in my earlier reply; if there are
multiple sets of time differentiated identified risks per patient then the
key would include a column such as DateIdentified). The Notifications table
would thus have a composite foreign key of Notifications and RiskDetail, and
would also have a columns NotifiedTo containing a value such as MD. It would
not have a Boolean column, however, as it would be the existence of a row in
this table which would indicate that the notification had been undertaken.
The NotifiedTo column would also be a foreign key referencing another table,
which I'll call Staff for this example but you'll be able to think of a
better name I'm sure, with one row for each modifiable person So with this
multiple notification questions per risk detail identified per patient
scenario the model would include, in addition to the relationships shown
above, the following:

RisksIdentified----<Notifications>----Staff

When it comes to the interface you could use an unbound multi-select list
box to identify the risk details, but it would mean writing code in the
form's module to write data to the table when the list box is updated by the
user, and reading data from the table when the user moves to a patient record
in the form to show the identified risk details in the list box. It can be
done, but its not trivial and does require coding experience. Using a
continuous form view subform is far simpler and the commonly used interface
in this sort of situation. As you are now selecting a risk detail rather
than a risk category the RiskDetail combo box in the subform should now list
two columns RiskDetail and RiskType and be ordered by the latter so that the
user sees the risk details per category grouped together in the list when
making a selection. When a selection is made it will show the risk detail in
the list but the risk type can be shown in an unbound text box on the subform
with a ControlSource property of:

=[RiskDetail].[Column](1)

The Column property is zero-based, so Column(1) is the second column of the
combo box's RowSource.

A further fly in the ointment, however, is that if you have multiple
notification questions per risk detail identified per patient you can't have
a continuous view subform within another continuous view subform. You'd have
two possible solutions to this: (1) Use a single view risks identified
subform with a continuous form view notifications subform in it; (2) use
'correlated' continuous view subforms where you have two separate continuous
form view subforms in the parent form so when you select a row in the risks
identified subform, the notifications subform shows the notifications for
that risk detail record. The latter requires the two subforms to be linked
via hidden controls in the parent form, but we can come back to how to do
that later if necessary. For now I'd recommend concentrating n getting the
'logical model', i.e. the tables and the relationships between them sorted
out. A sound logical model is the key to a robust and efficient database;
get the model right and the interface will follow naturally from it; get it
wrong and you'll be jumping through hoops for evermore to work round its
deficiencies.

Ken Sheridan
Stafford, England

 

[tim williams]

Hi Ken,
Thank you. you have been very helpful. Of course I have further
questions.
What if I want to allow the user to choose multiple items in a specific
risk
category and have it have a similar interface to a list box where the user
chooses a few items? For example say you have the risk category
"Communication"
Further broken down into:
1-Communication problem MD to MD
2-Communication problem MD to RN
3-Communication problem RN to RN
In this scenario I would like to allow the user to be able to choose more
than one of these options.
Would it be set up like this:
Patients-----<RisksIdentified>----Risks>------communication

Also for the items where the answer might be yes or no like "Was MD
notified" yes or no. If I do not use the yes or no option in the data
type
option, how should I go about organizing this data. Should I make a yes
no
table and use lookup to reference that table.
Thanks again for your help