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

Ask a Question

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.