Help with Table build

[novoiceleft]

Can anybody help me build the last table in my database? I have taught myself Access – and can usually get by with a little help from this forum. I have been trying for days to try and solve this !!!

The database is designed to manage feedback questionnaires from attendees of training courses.

I have attached the relationship diagram.

- A course may run for several days. The details of the course are held in the table ‘Courses’. During a course, attendees may be asked to fill in several Questionnaires
- An Attendee is a Person that attends a Course. Attendees are held in the table ‘Attendees’ and their details are held in the table ‘Person’.
- There is a ‘Library’ of questions that sit in the table ‘Questions’
- The questions can be assembled into “Standard Questionnaires”. These sit in the table called “Questdesign” which is a junction table. Assembling a Standard Questionnaire involves using a form to pick questions and give them an order (Qnumber). I HAVE DONE THIS.
- A specific “Questionnaire” is a “Standard Questionnaire” that is used on a specific course. The list of Questionnaires is held in the table “Questionnaire”
- Scoresheet is the junction table between Attendee and Questionnaire (an Attendee on a course may fill in several Questionnaires and a Questionnaire may be filled in by lots of Attendees.)

I now need a table (Scores) in which to put all the scores to the questions (in other words, the scores of the questions on the Questionnaires that are completed by the Attendees on a Course.)

At the end of the day I am aiming to have a form in which:

The Attendee number is entered and all the person’s details are filled in automatically) – I HAVE DONE THIS
The Questionnaire number is entered and all the course details are filled in automatically – I HAVE DONE THIS
A Scoresheet ID is filled in (Autonumber) - I HAVE DONE THIS
A subform appears which shows the right set of questions and question numbers for the appropriate Questionnaire – with a blank column so that the user can fill in the scores which get stored in the Scores Table. - I CAN'T DO THIS !!!

I cannot work out where to link my Scores table and how to build a query that drives the correct behaviour of the subform

I hope I have explained this clearly. I would be so grateful for any help. This Forum is fabulous.

Many thanks

NoVoiceLeft

 

[KatCanDo]

Here's an idea

Make table with your questions in the field name, make data type Number with a Lookup wizard. In Lookup wizard assign the values your subjects should choose from such as 1,2,3,4,5 with 5 being the best rating for the questions.

Make form with control source this table you just made. You will have drop down boxes for the subject to select how they wish to rate the questions. This way no one can type in 99 when you need a rating of 1-5 or however you decide to do this.

Add your instructions in the top of the form, and a command button or so as needed to exit or save for example.

 

[Rich]

Actually using the look up wizard in tables is the worst thing one can do

 

[novoiceleft]

More help needed

Thanks guys.

But what I'm really struggling to understand is which fields to have in my Scores table, how to index them, and how to link them in to the rest of the database so far.

I have already tried so many combinations that just don't seem to work.

Will it work if I build a Scores table with the fields?

ScoreID
ScoresheetID
QuestionID
Score

And which tables and fields would I link them to?

It needs to work so that the QuestionIDs in the Scores table are those that correspond to the correct ScoresheetID and QUestionnaireID

All help appreciated

NoVoice

 

[novoiceleft]

Whats wrong with Lookup for Tables?

Rich - I'm curious to understand why you said 'the Lookup Wizard is the worst thing for tables"

I know how to use the Lookup Wizard and I also how to build lookups and links manually. But don't they achieve the same thing?

NoVoice

 

[Fear Naught]

Tables should be just that a straighforward table of data. I made the same mistake of using lookup wizards in tables but the problem with them is that you then lose control of what is displayed which can be confusing

 

[Pat Hartman]

I know how to use the Lookup Wizard and I also how to build lookups and links manually. But don't they achieve the same thing?

Yes they acheive the same thing but a lookup at the table level is only useful when you open the table as a datasheet. It actually gets in the way if you use queries or VBA. For example, when you look at the datasheet, you see a text value, however, the query actually returns the underlying numeric value and that is what is visible in VBA but not in the datasheet view of the query - nothing but confusion.

Table level lookups are a crutch for people who don't know enough to create joins or write VBA. Use combos on forms to serve the same purpose. They will not cause problems.