A many-to-many challenge

[0 1]

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]

Any thoughts?