Normalizing question - tracking multiple surveys & attempts over time

[HeislerKurt]

I'm seeking advice on how to relate tables for a database to track
survey attempts for a teacher survey project. (I don't need to store
survey answers, which is what most of the survey database examples
focus on.)

Teachers at four schools will complete two surveys, SurveyABC and
SurveyXYZ. They will need to complete each one 2 times - once at the
beginning of the semester (Pre), and once at the end of the semester
(Post). To increase the response rate, each survey may be sent
multiple times (e.g., 2nd attempt, 3rd attempt, etc.) to non-
responders. This process will be repeated for 2 more years. For
example, in Year 1 Mr. Smith at Brown Elementary will complete:

1. Survey ABC (Pre)
2. Survey ABC (Post)
3. Survey XYZ (Pre)
4. Survey XYZ (Post)

He will complete these again in Year 2, and again in Year 3. And it
may have taken multiple attempts to get him to complete each one.

Over the course of the project, I need to record the following for
each survey attempt, for each teacher:

1. The survey year (Year 1, 2, or 3)
2. The type of survey sent (e.g, Survey ABC & Survey XYZ)
3. The version sent (e.g., Pre vs Post)
4. The history of attempts (e.g., 1st attempt, 2nd attempt, etc.)
5. The date sent
6. The outcome (returned/not returned) by a deadline

At the end of Year 1 Mr. Smith's data might look like this:

- Year 1 | Survey ABC | Pre | 1st Attempt | 1/1/08 | Not Returned
(send follow-up)
- Year 1 | Survey ABC | Pre | 2nd Attempt | 2/1/08 | Returned (done)
- Year 1 | Survey XYZ | Pre | 1st Attempt | 1/1/08 | Returned (done)
- Year 1 | Survey ABC | Post | 1st Attempt | 6/1/08 | Returned (done)
- Year 1 | Survey XYZ | Post | 1st Attempt | 6/1/08 | Not Returned
(send follow-up)
- Year 1 | Survey XYZ | Post | 2st Attempt | 7/1/08 | Returned (done)

This is a screenshot of the relationship structure I've come up with:

http://img132.imageshack.us/img132/3421/teachersurveydy2.jpg

Although I think this will meet the basic needs, it's not completely
normalized (the relationship between tblTeacherSurveys and
tblTeacherSurveyAttempts is indeterminate). This could cause problems
in the future with queries to track return rates for different phases
of the project, surveys, etc.. Any suggestions?

Thanks.

Kurt

 

[Jeanette Cunningham]

Please forgive me for not looking at your relationships picture.
I don't feel safe clicking on links to sites from people I don't know.

Looking at the data you want to collect, you have entities of surveys,
survey attempts and people.
There would be a many to many relationship between surveys and people
There would be a one to many relationship between people and survey attempts
There would be a one to many relationship between surveys and survey
attempts for each person.

Jeanette Cunningham

 

[HeislerKurt]

Please forgive me for not looking at your relationships picture.
I don't feel safe clicking on links to sites from people I don't know.

Looks like my link got disabled anyway.

Looking at the data you want to collect, you have entities of surveys,
survey attempts and people.
There would be a many to many relationship between surveys and people

Check. tblTeachers is linked to tblSurveys via a junction table,
tblTeacherSurveys. Visually:

tblTeachers
----------------------
TeacherID (PK)
TeacherName

tblSurveys
----------------------
SurveyID (PK)
SurveyName

tblTeacherSurveys
----------------------
TeacherID (FK to tblTeachers.TeacherID)
SurveyID (FK to tblSurveys.SurveyID)

There would be a one to many relationship between people and survey attempts

Hmm. As in ...

tblTeachers
----------------------
TeacherID (PK)
TeacherName

tblSurveyAttempts
----------------------
SurveyAttemptID (PK)
TeacherID (FK to tblTeachers.TeacherID)
Year
PrePost
Attempt (e.g., 1st, 2nd, 3rd)
DateSent
Outcome

?

There would be a one to many relationship between surveys and survey
attempts for each person.

As in:

tblSurveys
----------------------
SurveyID (PK)
SurveyName

tblSurveyAttempts
----------------------
SurveyAttemptID (PK)
TeacherID (currently a FK to tblTeachers.TeacherID)
SurveyID (FK to tblSurveys.SurveyID)
Year
PrePost
Attempt (e.g., 1st, 2nd, 3rd)
DateSent
Outcome

###

Is that correct? If so, I'll try it out ...

Thanks.

 

[HeislerKurt]

Your suggestion appears to work well.

The only issue is that, when a survey attempt is entered for a given
survey for a given teacher, the TeacherID and SurveyID fields in
tblSurveyAttempts are not populated. The attempt data is still linked
to the appropriate survey and teacher, but if I delete a teacher a
cascade delete will have no effect (because the TeacherID FK field is
blank). I could force a copy of TeacherID into tblSurveyAttempts at
the form level.

Perhaps this is due to my record source for my attempt subform?

Here is the form setup:

1) frmTeacher (based on tblTeachers) - where teacher info is entered

2) fsubTeacherSurveys
- a continuous form which lists the surveys assigned to the teacher
(when a new teacher is entered, an update query assigns the surveys to
the teacher, thus populating tblTeacherSurveys)
- Record Source: an inner join between tblSurveys & tblTeacherSurveys
via SurveyID
- linked to frmTeachers with TeacherID

3) fsubSurveyAttempt
- linked to frmTeachers with a hidden text box TeacherSurveyID, which
is populated according to which survey the user selected in
fsubTeacherSurveys
- Record source: tblSurveyAttempts (perhaps I should change this?)

Hmm...

 

[Jeanette Cunningham]

Heisler,
I haven't set this up and tested it, but here is how I think it would work.

tblTeachers is linked to tblSurveys via a junction table,

tblTeachers
----------------------
TeacherID (PK)
TeacherName

tblSurveys
----------------------
SurveyID (PK)
SurveyName

tblTeacherSurveys
----------------------
TeacherID (FK to tblTeachers.TeacherID)
SurveyID (FK to tblSurveys.SurveyID)

The classic way to set up forms for this is:

Use a main form (single view) for one of the Many side tables. Within the
main form, use a continuous subform bound to the junction table with the
Link Master set to the main form's PK field. This subform has at least one
visible control that is a combo box bound to the second table's PK field.
This form's Current event and the combo's AfterUpdate event sets an
invisible text box on the main form to the value of the combo box.

Along side the continuous subform is another subform bound to the other many
side table with its Link Master property set to the invisible text box.

With this arrangement, you can navigate or search for the main form record.
The continuous subform displays the items assigned to the main form record.
Clicking on a record in the continuous subform automatically displays the
related
record in the other subform.

Jeanette Cunningham