I'm developing a database to manage survey data collected for a study.

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

I'm going to store the survey responses in a normalized fashion: tblQuestions, tblResponsesList, tblResponses, and tblSrvRspns, where tblSrvRspns holds data about the individual respondent.

How should I model the requirement of multiple visits and multiple surveys (some of which are repeated)?

Here's what I have so far:

1. A many-to-many between tblVisits and tblSurveys:

tblVisits
VisitID (PK)
VisitNumber

tblSurveys
SrvID (PK)
SrvName

tblVisitsSurveys
VisitSurveyID
VisitID (FK to tblVisits)
SrvID (FK to tblSurveys)

Attached is a screenshot of what I have so far:

http://img200.imageshack.us/img200/9600/modeliu.jpg (http://img200.imageshack.us/i/modeliu.jpg/)

My question is how to model which survey each person has completed, for each visit? I suspect it involves adding a link between tblVisitsSurveys and tblSrvRspns. Also, at some point I need a table whose PK consists of 3 fields: VisitID, SurveyID, and RspnsID (at the very least in tblResponses).

Any advice is much appreciated!

Any ideas?

You might want to take a look at "At Your Survey".
see http://www.rogersaccesslibrary.com/forum/at-your-survey_topic3.html