Tables and Relationships - how would you do this?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Keith Wilby - 16 Jan 2007 09:26 GMT

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.

Reply to this Message

Allen Browne - 16 Jan 2007 10:09 GMT

*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%20Sur
vey%202000'

Signature

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.

> I'm putting together a PC Workstation Assessment application, the idea
> being dead simple - there are a number of predetermined questions for the
[quoted text clipped - 32 lines]
> Regards,
> Keith.

Reply to this Message

Keith Wilby - 16 Jan 2007 10:49 GMT

> *Please* don't use the check boxes, Keith. :-(
> As you realized, they are not queryable (i.e. unnormalized.)
[quoted text clipped - 26 lines]
>
> http://www.rogersaccesslibrary.com/Otherdownload.asp?SampleName='At%20Your%20Sur
vey%202000'

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.

Reply to this Message

Keith Wilby - 16 Jan 2007 12:59 GMT

> a) Is it possible that employees may be asked to complete this survey
> periodically (annually? on entry and exit?) If so, you need another
[quoted text clipped - 3 lines]
> 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.

Reply to this Message

Allen Browne - 16 Jan 2007 13:52 GMT

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?

Signature

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.

> *Please* don't use the check boxes, Keith. :-(
> As you realized, they are not queryable (i.e. unnormalized.)
[quoted text clipped - 63 lines]
>> Regards,
>> Keith.

Reply to this Message

Keith Wilby - 16 Jan 2007 15:17 GMT

> 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.

Reply to this Message