General Relationship Structure Question

[Brahio]

Hi

I am fairly new to access and need some table design advice. I have been
tasked with developing a very large database and after structuring, I am
finding myself stuck with some fundamental problems. (for the purpose of
this request, I’m using the term “form†for the type of data that will be
entered by the users, but I’m not yet at the point to create access form yet).

The database is essentially housing hundreds of fields from different forms
(kind of like tax forms) that are done manually in excel by users. My task
is transferring this into a database. Some of the existing manual forms have
over 300 fields, and little relationships. (was difficult to try and
normalize, as it mainly a way to capture data and store it for reporting)
Mostly yes/no, questionnaire, etc. I had to split each form into a few
tables with 70-90 fields each (with one-one relationships). I need to have
one ID number for each form that is incrementing with the autonumber. The ID
number increments one by one, regardless of the form type, so it needs to be
central number.

So I created a header table with a primary key of FormID, Date Created,
Created by, version, etc. This is joined with another table 2 (one to one)
that has all of the this particular forms #1 information, another joined by
form ID with form #2…….etc

My question is, when I create a new form to enter data in and it creates a
form number, the number is created in the header table, but not the table
2,….so how does it join if I did not enter the form ID in table 2 for it to
join?

I am used to creating many relational reports from large established system,
but not from scratch. Any advice?

Thanks.
Brahio

 

[John W. Vinson]

Hi

I am fairly new to access and need some table design advice. I have been
tasked with developing a very large database and after structuring, I am
finding myself stuck with some fundamental problems. (for the purpose of
this request, I’m using the term “form” for the type of data that will be
entered by the users, but I’m not yet at the point to create access form yet).

You're right in wanting to get the table design right first.

The database is essentially housing hundreds of fields from different forms
(kind of like tax forms) that are done manually in excel by users. My task
is transferring this into a database. Some of the existing manual forms have
over 300 fields, and little relationships. (was difficult to try and
normalize, as it mainly a way to capture data and store it for reporting)
Mostly yes/no, questionnaire, etc. I had to split each form into a few
tables with 70-90 fields each (with one-one relationships).

That's NOT the right way to go about it, I fear! A much better design is a
tall-thin structure:

Questionnaires
QID <Primary Key, maybe an autonumber>
<information about who answered, when, etc.>

Questions
QuestionNo <Primary Key, probably a manually entered integer>
Question <Text>

Answers
QID <Long Integer, link to QID>
QuestionNo <link to Questions>

I need to have
one ID number for each form that is incrementing with the autonumber. The ID
number increments one by one, regardless of the form type, so it needs to be
central number.

You *CANNOT* match one autonumber to a different autonumber. They'll have
gaps, they're not controllable. An autonumber in a "master" table must be
linked to a Long Integer in a "child" table, as above.

So I created a header table with a primary key of FormID, Date Created,
Created by, version, etc. This is joined with another table 2 (one to one)
that has all of the this particular forms #1 information, another joined by
form ID with form #2…….etc

My question is, when I create a new form to enter data in and it creates a
form number, the number is created in the header table, but not the table
2,….so how does it join if I did not enter the form ID in table 2 for it to
join?

Well... don't do it this way; your structure is still "wide-flat". If you have
to add or delete a question, you'll need to restructure your table, all your
forms, all your queries, all your reports... OUCH!

I am used to creating many relational reports from large established system,
but not from scratch. Any advice?

Normalize.

For a good worked out questionnaire example see Duane Hookum's "At Your
Survey":
http://www.rogersaccesslibrary.com/Otherdownload.asp?SampleName='At Your Survey 2000'

Also check out Roger Carlson's Training Registration database:
http://www.rogersaccesslibrary.com/download3.asp?SampleName=TrainingRegistration.mdb