Lookup tables & scoring system

[paulcliman]

I've been asked to prepare a DB for our Nursery dept. It's a form for
visiting parents before their child joins the school. I have many
lookup tables linked to the main table e.g. "Interest in Reading" may
be (i)nil (ii)indifferent (iii) high. Each choice scores; i.e 1point
for (i); 2 for (ii); etc. At the end they want a total score for all
questions answered. On the form there are list boxes created from these
aforementioned linked tables. What is the neatest (and easiest to
understand for a relative newcomer to Access) way of doing this
please?
Thanks in anticipation.

 

[The Sisko]

Err...off the top of my head howabout...A very basic, simple solutio
might be:

1. Create a table called "tblQuestionaires" with the following fields:

QuestionaireID (autocounter)
ParentSurname
ParentForename
Q1text - Question 1 text ie "Does child like reading"
Q1score - score for question 1
....repeat for however many questions you want to ask....

2. Base a new form on tblQuestionaires and drag out all the question
and position them on the form

3. Against each question place a drop down which has 2 columns.
visible which shows "nil", "indifferent" etc and the 2nd hidden colum
which holds the corresponding numeric value ie 1, 2 etc. The secon
column would be the BOUND column and should be stored in the scor
field for the appropriate question.

4. Therefore when someone fills in the form the saved record will hol
a numeric value for each question. You will then need to add a tota
field at the bottom of the form which adds up the score values. Thi
would be display only and would not be part of the saved record.

There are a holes in this such approach such as what if the question
change etc but if you are not bothered about this then fair enough. I
you are then you would need to hold the questions separate from th
scores and link them via a question id. It could get complicated quit
quickly so If I was you (ie Access novice) I'd stick to an eas
solution and if needs be it can be customised at a later date you whe
you are more experienced.

PaulC

 

[paulcliman]

Hi,
Many thanks for swift and comprehensive response; much appreciated.
What I most want to do, is try to foresee what teachers are likely to
ask i.e. how they will interrogate and research the data according to
their own chosen criteria.
Therefore I am trying to figure out the most suitable design approach
to cater for all eventualities.
Yes, I know.......some task for a relatively Access newcomer!
Thanks again,
Paul Climance

 

[The Sisko]

As in any db application the starting point is to understand your data
ie What data will you need to store and what will the users be doing
with it. Only once this is clear should you move onto doing anything
with Access. My advice would be to keep it simple. Access is fairly
easy to use but it can quite quickly get more complicated and soon you
will be in the realms of record sets and the like.

 

[paulcliman]

Thanks again.....not sure of the etiquette involved here, but, I think
I've got the lookup tables and option boxes matter pretty much sorted.
I have another (more specific) problem now, so I thought I'd start with
a nice new thread?
I'm going to post it in about half an hour so if it's NOT the thing to
do, let me know.
Where would we be without people like yourself giving up time and
effort to help intellectually-challenged misfits like me??!!??