Multiple entries in one field

[ynezllapa]

I have a form with one field which may have multiple entries (for example, cause of accident: weather, road hazard, mechanical failure, driver error, etc.). I want people to be able to choose as many as apply. I am a very new designer, not doing any kind of programming, so please keep your answer "For Dummies". Thank you.

 

[Trevor G]

Welcome to the Forum,

You could use a listbox that shows all of the variables and allow multiple selection. Or you could set up some checkboxes so the user can click on the ones they want.

But what is the purpose of the multi selection, is it to filter what has been selected some where like a query or report?

 

[jzwp22]

Welcome to the forum!

so please keep your answer "For Dummies"

There is some basic terminology for databases, so that will be unavoidable. Please ask if you do not understand anything below.

Having multiple pieces of information should not be contained in 1 field. In fact, what you describe is that an accident can have many causes. This in database jargon is a one-to-many relationship. A one-to-many relationship is handled with 2 tables. In fact there are some basic rules when working with relational databases such as Access. These rules are know as normalization.

So you need a table to record the basic accident information

tblAccident
-pkAccidentID primary key autonumber (I include an autonumber primary key field in every table)
-dteAccident (date of accident)
other fields related to the accident

I also use prefixes in my field names, those prefixes are as follows:

txt=text datatype field
dte=date/time datatype field
pk=primary key field, I always use an autonumber datatype for the primary key; the autonumber is a special case of the long integer number
fk=foreign key, this has to be a long integer number datatype since it has to match the autonumber datatype of the related table
long= a long integer number field that is not a foreign key field
sp=single precision number field
curr=currency field
log=logical datatype i.e. a yes/no field

Now you need a table to hold the multiple causes related to the accident. Each cause would be a separate record (i.e. a row) in the following table, not as multiple causes within 1 field (i.e. column) nor as multiple fields.

tblAccidentCauses
-pkAccCauseID primary key, autonumber
-fkAccidentID foreign key to tblAccident: this field provides the link to the accident listed as a record in tblAccident
-txtCause

Technically speaking, since a cause may apply to many accidents, you do have another one-to-many relationship. When you have 2 one-to-many relationships between the same two entities (accidents and causes in your case), you have what is called a many-to-many relationship in database jargon.

To properly handle a many-to-many relationship, you actually need 3 tables:

The accident table stays the same

tblAccident
-pkAccidentID primary key autonumber
-dteAccident (date of accident)

Now we need a table to hold all possible causes. Each cause would be a record in the table

tblCauses
-pkCauseID primary key, autonumber
-txtCause

the tblAccidentCauses will change a little as follows:

tblAccidentCauses
-pkAccCauseID primary key, autonumber
-fkAccidentID foreign key to tblAccident: this field provides the link to the accident listed as a record in tblAccident
-fkCauseID foreign key to tblCauses

Here are a few other general recommendations:

1. Do not use spaces or special characters in your table or field names. I carry that over to query, form and report names as well.
2. Do not use lookups (combo/list boxes) in your tables. See this site for issues that table level lookups can cause. Lookups are best left for your forms.
3. Do not duplicate any data in a second table that is already stored in another table except for the foreign key value.
4. Do not used Access reserved words. This site has a list.

This site has more on normalization, but it may be somewhat hard to follow, so you may want to look at this site which has some good tutorials for someone just starting out in Access.