Multivalued Field Functionality in Access 2003

[Joseph Irvine]

Hello All,

I am in a bit of a dilemma. I am trying to design an Access Database
that quite frankly needs multivalued fields so that I can use a
checked listbox, but I am limited to Access 2003 and cannot use Access
2007, in which I already have a fully working model.

I am building a database with a central table, called GENERAL. There
are 5 additional forms, DRUGNAME, ADDITIONALMEDICATIONS,
INJURYINFORMATION, etc. Each one contains one field which has a list.
For example the DrugName table would contain a list of all the
prescription drugs that the person possibly could have been on.

In 2007 it was great to be able to go on and have the general form
with name fields, address fields, etc. Then to be able to have all the
checked list boxes on the general field, one for the DrugName, one for
InjuryInformation, etc. The person could create a record, select the
prescription drugs the person was on, select multiple options for
injuries he might have had, etc. All this information was recorded to
his record in comma-delineated format which was easily accessible from
a query.

However, we have to use 2003. I want to make this as simple as
possible as it will be a bunch of non-computer literate individuals
using it, who may need to make changes to it, etc. So I don't want to
have to write custom SQL statements for each query, custom VBA code
for each query, etc. I want to make this as much point-and-click as
possible, even at the sacrifice of perfect database design technique.
This has to be usable by somebody who has never touched Access before.

Suggestions? How do I port multivalued field techniques to Access
2003?

Thanks,
Joseph

 

[Douglas J. Steele]

There's no way to port multi-valued fields to Access 2003 or any other prior
version of Access.

You have no choice but to implement the proper multi-table approach, which
is what Access 2007 is doing under the covers (although they're not exposing
the additional tables, as we've repeatedly told them they should)

 

[Joseph Irvine]

Hello,

Still not sure that makes sense to me. So I go and have all the
multiple tables, great. How do I mimic the functionality of the
checked list box though in Access 2003 though? They need to be able to
dynamically update the checked list box which could contain 100+
entries that they can then choose from on the main GENERAL form. They
need to be able to select multiple options for each record.

Thanks,
Joseph

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

 

[Douglas J. Steele]

Add a boolean field to the table and use a continuous form (showing the
boolean field) as a subform, rather than listbox.

And you're not storing multiple values for each record: you're storing
multiple rows in the junction table.