Tables and Relationships - how would you do this?

[Keith Wilby]

I'm putting together a PC Workstation Assessment application, the idea being
dead simple - there are a number of predetermined questions for the user to
answer either "yes" or "no" to. My first stab, also dead simple, is just a
flat table with the user's details and a Y/N field for each question to
contain their responses. The questions themselves are not stored but just
displayed on a form next to the relevant check box for their response.

It works but of course I can't report against failing items because the
questions aren't stored. This leaves me wondering what the best table
structure might be to store the actual questions (this is always the hardest
part of a design for me). Here's my first idea:

tblUsers:
EmployeeNumber (PK)
Name
Location
.... etc

tblAnswers:
EmployeeNumber (FK)
QuestionNo (FK)
Response

tblQuestions:
QuestionNo (PK)
QuestionText

I think it's obvious where the joins are but of course I'd have to code the
app to populate tblAnswers with a set of question numbers for each user ...
wouldn't I? I think I need some fresh ideas having stared at this for quite
a while now, any comment and suggestions greatly appreciated.

Regards,
Keith.

[Allen Browne]

*Please* don't use the check boxes, Keith. :-(
As you realized, they are not queryable (i.e. unnormalized.)

The tables you have a good, but here's some other possibilities to consider:

a) Is it possible that employees may be asked to complete this survey
periodically (annually? on entry and exit?) If so, you need another
tblSurveyTaken table between tblUsers and tblAnswers. The EmployeeNumber
moves into this table, so fields are:
SurveyTakenID (PK)
EmployeeNumber (FK)
SurveyDate (Date/Time)

Now you have a main form for selecting the employee who completed the
survey, with a subform for their responses. In the AfterInsert event of this
form, you can execute an Append query statement to append all the questions
in the survey to tblAnswers, using the SurveyTakenID as the foreign key. Now
completing the survey is just a matter of running down the continuous
form/datasheet to give the answer to each question.

b) Could there be other questionnaires developed over time?
If so that might require a Survey table that defines the questions in this
questionnaire.

c) Any chance there will ever be a question that needs more than a yes/no?
Multiple choice? "Other" that requires a comment? If so, there's a sample
database by Duane Hookom here:
http://www.rogersaccesslibrary.com/Otherdownload.asp?SampleName='At%20Your%20Survey%2 02000'

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Keith Wilby" <he**@there.comwrote in message
news:45**********@glkas0286.greenlnk.net...

I'm putting together a PC Workstation Assessment application, the idea
being dead simple - there are a number of predetermined questions for the
user to answer either "yes" or "no" to. My first stab, also dead simple,
is just a flat table with the user's details and a Y/N field for each
question to contain their responses. The questions themselves are not
stored but just displayed on a form next to the relevant check box for
their response.

It works but of course I can't report against failing items because the
questions aren't stored. This leaves me wondering what the best table
structure might be to store the actual questions (this is always the
hardest part of a design for me). Here's my first idea:

tblUsers:
EmployeeNumber (PK)
Name
Location
... etc

tblAnswers:
EmployeeNumber (FK)
QuestionNo (FK)
Response

tblQuestions:
QuestionNo (PK)
QuestionText

I think it's obvious where the joins are but of course I'd have to code
the app to populate tblAnswers with a set of question numbers for each
user ... wouldn't I? I think I need some fresh ideas having stared at
this for quite a while now, any comment and suggestions greatly
appreciated.

Regards,
Keith.

[Keith Wilby]

"Allen Browne" <Al*********@SeeSig.Invalidwrote in message
news:45***********************@per-qv1-newsreader-01.iinet.net.au...

*Please* don't use the check boxes, Keith. :-(
As you realized, they are not queryable (i.e. unnormalized.)

The tables you have a good, but here's some other possibilities to
consider:

a) Is it possible that employees may be asked to complete this survey
periodically (annually? on entry and exit?) If so, you need another
tblSurveyTaken table between tblUsers and tblAnswers. The EmployeeNumber
moves into this table, so fields are:
SurveyTakenID (PK)
EmployeeNumber (FK)
SurveyDate (Date/Time)

Now you have a main form for selecting the employee who completed the
survey, with a subform for their responses. In the AfterInsert event of
this form, you can execute an Append query statement to append all the
questions in the survey to tblAnswers, using the SurveyTakenID as the
foreign key. Now completing the survey is just a matter of running down
the continuous form/datasheet to give the answer to each question.

b) Could there be other questionnaires developed over time?
If so that might require a Survey table that defines the questions in this
questionnaire.

c) Any chance there will ever be a question that needs more than a yes/no?
Multiple choice? "Other" that requires a comment? If so, there's a sample
database by Duane Hookom here:

http://www.rogersaccesslibrary.com/Otherdownload.asp?SampleName='At%20Your%20Survey%2 02000'

Many thanks Allen, I'll give your suggestions a go right now. I'm actually
using combo boxes for the responses but I just wanted to keep my question as
simple as possible, mainly for my benefit :-)

Regards,
Keith.

[Keith Wilby]

"Allen Browne" <Al*********@SeeSig.Invalidwrote in message
news:45***********************@per-qv1-newsreader-01.iinet.net.au...

>
a) Is it possible that employees may be asked to complete this survey
periodically (annually? on entry and exit?) If so, you need another
tblSurveyTaken table between tblUsers and tblAnswers. The EmployeeNumber
moves into this table, so fields are:
SurveyTakenID (PK)
EmployeeNumber (FK)
SurveyDate (Date/Time)

Having a bit of a no-brainer on this Allen, how did you see this new table
being populated?

Thanks.

Keith.

[Allen Browne]

Main form:
Employee: [ v]
Survey Date: [ ]

Subform has these columns visible:
[Question Num] [Question Text] [Answer v]

To start a survey, user select the employee who answered these questions in
the main form. Survey Date defaults to Today.

In the AfterInsert event procedure of the main form:
Dim strSql As String
strSql = "INSERT INTO ...
dbEngine(0)(0).Execute strSql, dbFailOnError

To get the SQL statement, mock up a query based on your Questions table.
Change it to an Append query (Append on Query menu.)
Type this into a fresh column in query design:
SurveyTakenID: 99
Switch to SQL View (View menu.)
There's the SQL statement you need.
Just remove the 99, and concatenate the value of the SurveyTakenID from the
main form into the string.

Is that what you were asking?

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Allen Browne" <Al*********@SeeSig.Invalidwrote in message
news:45***********************@per-qv1-newsreader-01.iinet.net.au...

*Please* don't use the check boxes, Keith. :-(
As you realized, they are not queryable (i.e. unnormalized.)

The tables you have a good, but here's some other possibilities to
consider:

a) Is it possible that employees may be asked to complete this survey
periodically (annually? on entry and exit?) If so, you need another
tblSurveyTaken table between tblUsers and tblAnswers. The EmployeeNumber
moves into this table, so fields are:
SurveyTakenID (PK)
EmployeeNumber (FK)
SurveyDate (Date/Time)

Now you have a main form for selecting the employee who completed the
survey, with a subform for their responses. In the AfterInsert event of
this form, you can execute an Append query statement to append all the
questions in the survey to tblAnswers, using the SurveyTakenID as the
foreign key. Now completing the survey is just a matter of running down
the continuous form/datasheet to give the answer to each question.

b) Could there be other questionnaires developed over time?
If so that might require a Survey table that defines the questions in this
questionnaire.

c) Any chance there will ever be a question that needs more than a yes/no?
Multiple choice? "Other" that requires a comment? If so, there's a sample
database by Duane Hookom here:

http://www.rogersaccesslibrary.com/Otherdownload.asp?SampleName='At%20Your%20Survey%2 02000'

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Keith Wilby" <he**@there.comwrote in message
news:45**********@glkas0286.greenlnk.net...

>I'm putting together a PC Workstation Assessment application, the idea
being dead simple - there are a number of predetermined questions for the
user to answer either "yes" or "no" to. My first stab, also dead simple,
is just a flat table with the user's details and a Y/N field for each
question to contain their responses. The questions themselves are not
stored but just displayed on a form next to the relevant check box for
their response.

It works but of course I can't report against failing items because the
questions aren't stored. This leaves me wondering what the best table
structure might be to store the actual questions (this is always the
hardest part of a design for me). Here's my first idea:

tblUsers:
EmployeeNumber (PK)
Name
Location
... etc

tblAnswers:
EmployeeNumber (FK)
QuestionNo (FK)
Response

tblQuestions:
QuestionNo (PK)
QuestionText

I think it's obvious where the joins are but of course I'd have to code
the app to populate tblAnswers with a set of question numbers for each
user ... wouldn't I? I think I need some fresh ideas having stared at
this for quite a while now, any comment and suggestions greatly
appreciated.

Regards,
Keith.

[Keith Wilby]

"Allen Browne" <Al*********@SeeSig.Invalidwrote in message
news:45***********************@per-qv1-newsreader-01.iinet.net.au...

>
Is that what you were asking?

Not quite but I think I've worked it out. The problem I had was populating
tblSurveyTaken when a new record (employee) is added via the main form (bear
in mind that the app will be empty and users will be entering their own
details in Data Entry mode). What I have now is two "insert into"
statements, one to insert the employee number/date into tblSurveyTaken and
the other to insert the questions into tblAnswers.

I can't pre-populate with a list of employees because this is a huge office
on a huge site and people are constantly moving around.

I think when it comes to users doing a second survey I'll need a "start new
survey" button but I'll cross that bridge when I come to it.

Many thanks again Allen.

Regards,
Keith.