Database Design

[edwardcmorris]

I a new to access 2003 and teaching myself as I go. I'm getting pretty good
with the easy stuff but am now running into problems. I think my design may
be incorrect and am looking for some help on the best way to set it up.
I want to record job requests and subsequent search statistics arising from
those jobs.
I have the following information I want to record: Date Request Received,
Job Status, Date Request Required, Section Requesting Job, Searchers
Details, Search Category, Operation Type, Search Type, Objects Located Types,
Notes and Date Job Completed.
In original database I designed I had one table recording all these results
with many column headings. I came across trouble however when trying to
allocate two or more searchers to the one job or to produce reports or of job
details.

Any help on how to better design my database would be appreciated

 

[Duane Hookom]

Any time you feel compelled to enter multiple values into a single field, you
should consider creating a related table that contains the primary key value
from the initial table and then a record for each of the multiple values.

There are many excellent (and a few bad) resources on the web regarding
normalization.

 

[John W. Vinson]

I a new to access 2003 and teaching myself as I go. I'm getting pretty good
with the easy stuff but am now running into problems. I think my design may
be incorrect and am looking for some help on the best way to set it up.
I want to record job requests and subsequent search statistics arising from
those jobs.
I have the following information I want to record: Date Request Received,
Job Status, Date Request Required, Section Requesting Job, Searchers
Details, Search Category, Operation Type, Search Type, Objects Located Types,
Notes and Date Job Completed.
In original database I designed I had one table recording all these results
with many column headings. I came across trouble however when trying to
allocate two or more searchers to the one job or to produce reports or of job
details.

Any help on how to better design my database would be appreciated

As Duane says, you really want to avoid multivalue fields: they can ALWAYS be
handled with one-to-many relationships between tables. (The A2007 Multivalue
Field misfeature actually has such a table, concealed by the software).

Try some of the suggestions in these resources - Crystal's chapter on
normalization would be helpful.

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

A free tutorial written by Crystal (MS Access MVP):
http://allenbrowne.com/casu-22.html

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials