Table \ Relationship Design (1 Viewer)

Scenario first. Our employee's are scored on responses that they give to inbound customer emails. There are 10 questions and the score is calculated on the average of all 10 questions.

Attached is my proposed table design.

With help from another member here (Thanks) I know now the basics and understanding of creating an unbound form and writing the answer data back to the response table using a loop.

The uncertainty I have is how correctly to link the response data to the handler who the score belongs to.

One way I have thought about is to have a form bound to the tbl_QASVData table so that I get given an auto number on the form with the Assessor and Handler ID's written to this table.

Below that the list of unbound questions.

Once the assessment has been done a VBA sql runs putting the SVDataID, QuestionID and Score into the tbl_Responses table.

This then giving me a way of linking the Assessor, Handler, date, time and questions together in order to average the score for that particular assessment.

What I can see happening is the assessor aborting the assessment half way through and leaving a orphan entry in the tbl_QASVData table. I know I can add some code to check if the bound parts have been updated and stop prompt the assessor to either delete the record or complete, but I thought I'd check to see if there is a cleaner solution.

After a bit of research I considered using DMAX to get the next autonumber from the tbl_QASVData table, but another poster suggested this could run into issues if the Autonumber got out of sequence due to deleted etc.

Over to you!

One thing you can look into is to nest your updates inside a transaction. Then, if something goes wrong you can rollback any pending updates. Using DAO you can use code like ....
Be aware though that the domain aggregate functions like DCount() function outside of any transactions. They will not be aware of a transaction until it is committed.

Hi, please share this with me - With help from another member here (Thanks) I know now the basics and understanding of creating an unbound form and writing the answer data back to the response table using a loop.

The uncertainty I have is how correctly to link the response data to the handler who the score belongs to.

I need to create a form from multiple tables - what is the best way - from the tables or an query - please explain the process or refer me to other documentation - thanks?

landonet, is your post directed at me? I don't know how to 'link your response data to your handler' because I don't know anything about your system. What is the structure of your data and the rules governing the relationships?
What does it mean that you want to 'create a form from multiple tables?' A form can support data from one data source at a time. If you want to show data from many tables at once you will need to use a query.
Or do you mean that you want to be able to change the record source from one table to another table of the same structure?
Cheers,

landonet said:

I need to create a form from multiple tables - what is the best way - from the tables or an query - please explain the process or refer me to other documentation - thanks?

Click to expand...

You can base your form on a query that gets data from more than 1 table if that is what you are trying to do. The more specific you can make your questions the easier it is for us to help you

Thanks, i have multiple tables with relationships - i want to create a form from the multiple tables, ie i want to use fields from diffirent tables, ie out of 50 fields in 6 tables i only want to use 10 fields in my form - what is the best way, using the tables or create a query?

SteveGMC said:

Scenario first. Our employee's are scored on responses that they give to inbound customer emails. There are 10 questions and the score is calculated on the average of all 10 questions.

Attached is my proposed table design.

With help from another member here (Thanks) I know now the basics and understanding of creating an unbound form and writing the answer data back to the response table using a loop.

The uncertainty I have is how correctly to link the response data to the handler who the score belongs to.

One way I have thought about is to have a form bound to the tbl_QASVData table so that I get given an auto number on the form with the Assessor and Handler ID's written to this table.

Below that the list of unbound questions.

Once the assessment has been done a VBA sql runs putting the SVDataID, QuestionID and Score into the tbl_Responses table.

Click to expand...

Sounds like you can save yourself a lot of trouble by just starting with Duane's "At Your Survey" DB. Basically, your call is analogous to an interview (consists of many questions to a single person). Then just grade each question and average them.

Or if it's a quiz model you want, I would think that would be close to the same...

landonet said:

Thanks, i have multiple tables with relationships - i want to create a form from the multiple tables, ie i want to use fields from diffirent tables, ie out of 50 fields in 6 tables i only want to use 10 fields in my form - what is the best way, using the tables or create a query?

Click to expand...

A query is just a way of getting data from your tables so base your form on aquery that gets just the data you need.