A many-to-many challenge

Discussion in 'Access Table Design' started by 0 1, Mar 7, 2011.

  1. 0 1

    0 1 Guest

    I'm developing a database to manage survey data collected for a study.
    I don't need to analyze survey responses so I'd rather not normalize
    the survey tables/data as is commonly done in questionnaire
    databases.

    Individuals are seen for four visits and they complete a batch of
    surveys at each visit. Some surveys are
    repeated at each visit; some are new (but the survey schedule is
    predetermined).

    I have a table for Subjects, Visits, and Surveys.

    tblSubjects
    SubjectID (PK)
    etc.

    tblVisits
    VisitID (PK)
    VisitNumber

    tblSurveys
    SrvID (PK)
    SrvName

    I also have a separate table for each survey (tblSurveyABC,
    tblSurveyXYZ, etc.)

    Finally, I have a many-to-many between tblVisits and tblSurveys
    (tblVisitsSurveys), which shows which surveys are given at which
    visit:

    tblVisitsSurveys
    -----------------
    VisitSurveyID
    VisitID (FK to tblVisits)
    SurveyID (FK to tblSurveys)

    .... and a many-to-many between tblSubjects and tblVisits
    (tblSubjectsVisits), which shows which subjects completed which visit:

    tblSubjectsVisits
    -----------------
    SubjectVisitID
    SubjectID (FK to tblSubjects)
    VisitID (FK to tblVisits)

    For every record in tblSubjectsVisits, there is a one-to-one to a
    record in the survey table (e.g., tblSurveyABC) that corresponds to
    that subject's visit. e.g.:

    tblSurveyABC
    ------------
    SurveyABCID
    SubjectID (FK) to tblSubjectsVisits
    VisitID (FK) to tblSubjectsVisits
    q1
    q2

    The one thing missing is a table (like 'tblSubjectsVisitsMeasures')
    that stores which subjects completed which surveys at which visits?
    Any idea how to incorporate this into the model?

    The data entry interface plan is to select a subject (frmSubjects),
    select the Visit for that subject (fsubSubjectsVisits), and then
    select a survey (or surveys) to enter for that visit
    (fsubVisitsSurveys) (via a popup form for the survey selected).

    Thank you.
     
    0 1, Mar 7, 2011
    #1
    1. Advertisements

  2. 0 1

    0 1 Guest

    Any thoughts?
     
    0 1, Mar 9, 2011
    #2
    1. Advertisements

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.
Similar Threads
  1. CAD Fiend

    Confused about one-to-many or many-to-many relationships

    CAD Fiend, Jul 6, 2005, in forum: Access Table Design
    Replies:
    4
    Views:
    345
    CAD Fiend
    Jul 7, 2005
  2. Bill R via AccessMonster.com

    Many to Many to Many

    Bill R via AccessMonster.com, Aug 24, 2005, in forum: Access Table Design
    Replies:
    7
    Views:
    113
    Tim Ferguson
    Aug 25, 2005
  3. Eric Cathell

    Many to Many to Many Normalization

    Eric Cathell, Sep 26, 2006, in forum: Access Table Design
    Replies:
    3
    Views:
    152
    Eric Cathell
    Sep 26, 2006
  4. Debra Farnham

    How many one to many relationships are too many?

    Debra Farnham, Oct 24, 2006, in forum: Access Table Design
    Replies:
    7
    Views:
    405
    Graham Mandeno
    Oct 25, 2006
  5. Nanette

    Many To Many To Many

    Nanette, Nov 27, 2006, in forum: Access Table Design
    Replies:
    4
    Views:
    124
    Nanette
    Nov 28, 2006
  6. Nanette

    Many to Many to Many Relationship DB

    Nanette, Jan 3, 2007, in forum: Access Table Design
    Replies:
    0
    Views:
    206
    Nanette
    Jan 3, 2007
  7. tedzbug

    Many, Many, Many........ One? I'm lost

    tedzbug, Jan 4, 2007, in forum: Access Table Design
    Replies:
    9
    Views:
    291
    Graham Mandeno
    Jan 8, 2007
  8. an

    Many to many to many

    an, Mar 9, 2007, in forum: Access Table Design
    Replies:
    7
    Views:
    133
Loading...