11-28-13, 12:28 #1Registered User
- Join Date
- Nov 2013
Unanswered: MS Access Design issue: probably a very basic problem!
this is my first post here, and it is borne out of intense frustration and brain-hurt. I am trying to design a database in MS Access (2007, but must be compatible with 2003). It is not a complex thing, on the surface, but the specifics of the design are really foxing me. I've read through many different tutorials on database design, but none help with this details I need help with.
It is to hold the details of 25 participants in a study. The study collects data at 5 time points, over one year. The data comprises blood test results (around 60 variables), 4 different investigation results (around 20 variable for each), demographic info (collected once only) and 6 questionnaires (collected twice only). The data must be exportable to a statistics package. Data will be inputted by me and another colleague. Data will be extracted/exported by that same colleague.
My problem is of simple table design. I have tables for participants (Key = the unique code we give each participant) which includes the demographic data. My first design problem is the blood test & investigations tables: each parameter (autonumber key) is listed in one long field called TEST, with another called RESULT. So that each blood test, investigation result is in a unique row.
But here is my problem. I know I shouldn't duplicate info, so I rejected the idea of having a table for each timepoint. I just can't work out how to design this thing so it makes sense!! Does this question even make sense?
Many many thanks for any assistance you can give me!
11-29-13, 09:33 #2Jaded Developer
Provided Answers: 59
- Join Date
- Nov 2004
- out on a limb
if it were me I'd
have a table for participants
have a table for studypoints. probably using using a numeric code such as yyyyxx so yyyy would be 2013, XX would be either the month or study id, or store them as separate items in the table
if you chose to use a study id rather than month then I'd be tempted to have an junction or intersection table which identified a participant AND study and anything pertinent to hat (such as date samples taken). if you need to know when somebody (particiapnt) took part in the studyoint thenyou woudl deffo need an intersection table. if all you need is to know that a particiapnt has completed that study point (but nothing else) then the intersection table is optional
have a table for each of you main 4 samples (blood + 3 others)
a similar structure to handle surveys
same participant table
then a survey table which identified the survey (1 or 2) so your composite primary key would be XXXXY where XXXX is the participant ID, y is the survey id. you might want to allow moire than 10 surveys just in case who ever is designing this trial gets a rush of blod in the head and decides to have more than 10 surveysI'd rather be riding on the Tiger 800 or the Norton