**HELP** - I'm new to access - Points & Percentages

[LSSGreenBelt]

Hi all!

I have set up a Access Database to be used as a Quality Monitoring Form for Call Center Auditors.

I have a list of questions with the answers in drop down boxes, dependent on the answer selected, a score will be generated. The scores are then totaled up to give an over all score.

I have 3 questions.

1) How do I also generated a percentage score that will be displayed on the Form but also saved into my table?

2) Some questions can be answered with 'N/A' which obviously has '0' points attached. If N/A is selected, how to I remove the 'maximum points possibly awarded for that questions' from the percentage?

3) On my table, the points shown in each field are cumulative, however I would like to view the points scored for each individual question and then a total.

Sorry if this is not clear - any help would be VERY gratefully received!!

[meratigoerr]

There's really not enough information to go on here. We know absolutely nothing about your data structure or how your DB is set up. Having said that, you should read the stickies at the top of the forum they have some great information on how to properly set up a DB. Some things I can tell you from your questions.
1.) DO NOT save a calculated value in your table. This is just asking for trouble. Only do calculations as you need them for display purposes.
2.) No idea, we would need to see your DB to help. Post it to a file sharing site and link it so that we can take a look. (remove any confidential information and compact and repair it before loading.)
3.) A cumulative column would seem to indicate that this a calculated field. refer to #1. You should be saving the response of each individual question and response seperately then doing the calculations to add them up only as you need to.

[LSSGreenBelt]

Thank you for your response.

I will 'TRY' to explain in more detail.

I have 3 tables set up

1) The Form table, where all the information is stored.

2) I have a 'Category' Table with my unique ID.

3) I then have the Criteria table with 3 columns
- The possible answers that appear in the drop down boxes of the form.
- The Question ID from the Questions Table
- Points - How many points you get dependent on the answer given.

For example -

Question/Category
1 How old are you?
2 What is your eye color?

Answer/Criteria Question ID Points
Under 21 1 5
22 - 35 1 10
Over 35 1 15
Blue 2 5
Green 2 10
Brown 2 15



My combo boxes on my forms have a row source of...

SELECT [QualityCriT].[ID], [QualityCriT].[CriteriaName], [QualityCriT].[Points] FROM QualityCriT WHERE CategoryID=1 ORDER BY [Points];

I then have a event procedure 'After Update' to 'Calculate Points' in my 'Points' box.

Private Sub CalculatePoints()

Points = 0
Points = Points + Val(Nz(CatageCmb.Column(2)))
Points = Points + Val(Nz(CateyecolorCmb.Column(2)))

Private Sub CatageCmb_AfterUpdate()

CalculatePoints

etc....

In my Form Table I currently see this....

Name Contact Age Eye Color Points
Emma xxxxxx 10 20 20
Sam xxxxxx 10 15 15

I would like to be able to see this....

Name Contact Age Eye Color Points %
Emma xxxxxx 10 10 20 66%
Sam xxxxxx 10 5 15 50%

Once the score is calculated once, it will not changed.

These scores will remain in the database and a report will be pulled for us to monitor performance and progression.

Does this help or hinder??????

I will try to upload what I have already....

[June7]

I agree with meratigoerr.

Enter raw data and do summary calcs as needed. A report object would be ideal for this. Allows display of detail records and summary calcs (including percentages) in report header/footer sections.

Percentage of what - apparently highest possible total points.

Is age and eye color real data or is that just dummy example?

The Form table does not appear to be normalized structure. How many reponse columns are there?

[LSSGreenBelt]

Ok - i think I am getting my head around this now - thank you!

No - the age/eye is just dummy data.

It is a large QM sheet - roughly 20 questions with 4 possible responses for each.

The issue I am having is dealing with the N/A responses.

For example - if a certain question is 'N/A' - i need to deduct the entire question out of the end score so that they will have 0 out of 0 rather than 0 out of 30 (for argument sake) so that it doesn't skew there overall score and makes the QM process as fair as possible.

I would like the end users of this not to have to do any calculations themselves - it's currently set up so that they can only use the user friendly form.

[meratigoerr]

How are the actual results of your data stored. If you're storing them in a properly normalized table it should look somehting like this: (response of 0 indicates an N/A)

ID : QuestionID : QuestionResult : TesteeID : MaxPoints

With data like this:
ID QuestionID QuestionResult TesteeID MaxPoints
1 1 1 1 3
2 2 1 1 3
3 3 2 1 3
4 4 3 1 3
5 5 2 1 3
6 6 1 1 3
7 7 2 1 3
8 8 2 1 3
9 9 1 1 3
10 10 3 1 3
11 11 2 1 3
12 12 2 1 3
13 13 2 1 3
14 14 2 1 3
15 15 1 1 3
16 16 0 1 3
17 17 0 1 3
18 18 1 1 3
19 19 3 1 3
20 20 2 1 3

the query:

SELECT Sum(Table1.QuestionResult) AS SumOfQuestionResult, Table1.TesteeID, Sum(IIf([QuestionResult]=0,0,[MaxPoints])) AS TotalPoints
FROM Table1
GROUP BY Table1.TesteeID;

Would yeild the results:
TesteeID SumOfQuestionResult TotalPoints
1 33 54

Then use another query to calculate your percentages.

Unfortunely I don't know enough about your table structurs to know if this will help you. If you do have values stored horizontally instead of Vertically, then you have a real challenge on your hand and should consider redesigning your tables before moving on any further.

[LSSGreenBelt]

Oh no - it's horizontal!

Thank you all - looks like i'm going to be starting again!

Valuable lesson learnt!!

[meratigoerr]

Vertical storage of data over horizontal is usually refered to as Normilaztion (sp?). This is how data is stored in an access database versus an excel spread sheet. while it possible to store data horizontally in a database it will cause you great headaches and pain and should be avoided. Changing your tables to a more normilized setup, you will notice it becomes a lot easier to do many of the tasks you're asking about.