Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2013

    Unhappy Unanswered: MS Access Design issue: probably a very basic problem!

    Hi there,

    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!



  2. #2
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    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 surveys
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts