If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Design help - simple ?
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 |
#2
|
|||
|
|||
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 "Brian" wrote in message ... 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 |
#3
|
|||
|
|||
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" wrote: 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 |
#4
|
|||
|
|||
Thanks to Jeff and Sharkbyte for the prompt response
"Sharkbyte" wrote: 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" wrote: 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 |
#5
|
|||
|
|||
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 "Sharkbyte" - wrote in message ... 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" wrote: 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 |
#6
|
|||
|
|||
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 "Jeff Boyce" wrote: 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 "Sharkbyte" - wrote in message ... 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" wrote: 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 |
#7
|
|||
|
|||
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 "Jeff Boyce" wrote: 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 "Sharkbyte" - wrote in message ... 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" wrote: 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 |
#8
|
|||
|
|||
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 "Brian" wrote: 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 "Jeff Boyce" wrote: 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 "Sharkbyte" - wrote in message ... 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" wrote: 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 |
#9
|
|||
|
|||
On Thu, 9 Jun 2005 09:46:01 -0700, "Brian"
wrote: 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] |
#10
|
|||
|
|||
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. "Sharkbyte" wrote: 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 "Brian" wrote: 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 "Jeff Boyce" wrote: 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 "Sharkbyte" - wrote in message ... 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" wrote: 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 |
|
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Table design & VB | AHopper | Database Design | 2 | November 10th, 2004 10:54 PM |
Design Templates don't apply font sizes consistantly | Greg H | Powerpoint | 1 | September 15th, 2004 02:07 PM |
design master problem | J. Vermeer | General Discussion | 0 | September 8th, 2004 03:23 PM |
Action queries changing when reopened in design view | Kendra | Running & Setting Up Queries | 2 | August 31st, 2004 12:34 AM |