Design help - simple ?

[Brian]

Apologies upfront as this is extremely basic

I need to design a tracking database - to track the various stages of
startup of a study.

The main form (based on a current Excel sheet) contains the following:

Study Number
Title
Name of person responsible

then for each of the various stages (for example)
Initiated by - date started, date completed, name of person, comment
Intial Review - date started, date complete, name of person, comment

There are about 20 stages that need the same info as above.

My question is:
Should I just have 1 table to capture all of this info or have it split into
several tables?

Thanks

Brian

 

[Jeff Boyce]

Brian

Are you saying that one study can have one to many stages? This is what
relational databases "do for a living".

If you try to bring your Excel data/model into Access, you will only
frustrate yourself and Access!

One table holds studies. One table holds studies' stages. (and if you are
using the same set of stages -- e.g. 'initiation', 'preliminary discussion',
'preliminary design', etc.) a third table that holds stages.

Take a look in Access HELP on normalization and relational design.

Good luck

Jeff Boyce
<Access MVP>

 

[Sharkbyte]

I guess my first question would be, is this db going to be used for more than
one study? Would there be more than 1 record, for a stage, for a given
study? If either one is a "Yes", then you want separate tables.

If it is 1-to-1, all the way through, I suppose it doesn't really matter
which way you go. Although I (personally) would probably give each stage its
own table, as you may find reason to add multiple records to a stage, as you
progress through the study, and this structure gives you that flexibility
with little to no extra effort.

Sharkbyte

 

[Brian]

Thanks to Jeff and Sharkbyte for the prompt response

 

[Jeff Boyce]

One down-side to creating a table per study is when you want to compare
across studies (e.g., how many studies have achieved stage "3"?).

Jeff Boyce
<Access MVP>

 

[Brian]

Hi Jeff

Basically, there are going to be multiple studies

Each study will have multiple stages (but the same number of stages for each
study)

Each stage has multiple records (but each stage has the same records - Start
Date, End date, Name of Person, Comment).

At the moment I have got:
Staff Table (StaffName)
Study Table (StudyNum, StudyTitle, StaffName

Then should I go with:
Stage1 Table (StageRef, Descrip, StartDate, EndDate, Person, Comment)
Stage2 etc...

Then a linking Table
StudyNum, StageRef

 

[Brian]

Still struggling:

There will be sveral studies all capturing the same info

I need to capture the following:

StudyNumber ..............................
StudyTitle: ..............................
MainContactPerson .........................
2ndContactPerson ..........................

Then there are 20 different Stages
Stage DateStarted DateEnded Person
Comment
1 1/1/05 1/2/05 John
XXXXX
2 4/1/05 6/1/05 Fred
YYYYYY

There will be several Studies, but each study will capture the same
information.

Will it be better to have 20 Tables for the 20 Stages or separate Tables for
Stage, DateStarted, DateEnded, Person, Comment

I don't know if that makes sense

Brian

 

[Sharkbyte]

Brian:

Try this:

tblStudies
StudyID
StudyName (PK)
StudyDesc (if necessary)
Contact1 (Can use (FK) from tblStaff table, if appropriate, otherwise
have stand-alone)
Contact1Phone (if needed)
Contact2 (Same as Contact1)
Contact2Phone

tblStaff
StaffID
StaffLastName (PK)
StaffFirstName (PK)

tblStages
StageID (PK) (Not an AutoNumber field. Number stages in order.)
StageName
StageDesc

tblStudyStages
StudyStagesID
StudyID (PK) (FK)
StageID (PK) (FK)
StartDate
EndDate

tblStageComments
StudyStagesID (PK) (FK)
Comment# (PK) (This will allow you to accept multiple comments for a
single study/stage/staff.)
Comment

Anyway, that should be close to where you want to go, with this.

HTH

Sharkbyte

 

[John Vinson]

Will it be better to have 20 Tables for the 20 Stages or separate Tables for
Stage, DateStarted, DateEnded, Person, Comment

Neither.

See Sharkbyte's suggestion. You have a Many (studies) to Many (stages)
relationship; the proper way to model such a relationship is with a
third table linked one-to-many to both Studies and Stages.

NEVER store data in tablenames or fieldnames!

John W. Vinson[MVP]

 

[Brian]

Thanks for the help.

If I use this design. Is there a simple way to set up a user form that will
display the study info and all of the various stages (1-20). can I get the
form to default to display all the stages?

When I try to create a form it only displays the stage after I type in the
StageID. So every time a create a new record there are no stages displayed

 

[John Vinson]

Thanks for the help.

If I use this design. Is there a simple way to set up a user form that will
display the study info and all of the various stages (1-20). can I get the
form to default to display all the stages?

When I try to create a form it only displays the stage after I type in the
StageID. So every time a create a new record there are no stages displayed
on the form until a StageID is entered.

You can use a Form based on the Study table, with a continuous Subform
based on the StudyStages table.

John W. Vinson[MVP]

 

[Brian]

When I use the continuous subform I still do not get all of the stages
displayed. I have to tab to create the next entries for the next Stage.

I think I'll stick with my very basic Excel sheet and the 'paste links'!!!!