Creating an equation off of a crosstab query

[DanG]

First off, I am really new to Access. So if my question sounds stupid, I
apologize...

I have a database used to track quality reviews on different cases handled
by people in our office. Each file (with its own individual file#) is graded
on a scale of: 1.0, 1.5, 2.0, 2.5, 3.0

The crosstab query I set up shows the different peoples names on the left
side and has the columns are: 1.0, 1.5, 2.0, 2.5, 3.0 and lastly overall
total of cases reviewed.

The middle part shows the total each person has received under each of
these grades.

I need to be able to get these numbers plugged into an equation that looks
like this:

(0.334 x %age of 3.0 files) + (0.667 x (1-(%age of 1.0+%age of 1.5))) = x

Does anyone have any suggestions for me? I am trying to read different
posts and force fit my issue into those, but it doesnt work. Again, sorry if
this is a stupid question. I am just in the learning stages with Access.

Thanks

 

[KARL DEWEY]

I am not understanding your formula.

What does %age represent?

Where are you going to put this formula to use?

How does this relate to the output of a crosstab query?

 

[DanG]

Sorry, I write the word percentage, %age. My bad. I am hoping to put the
formula to use in another query in which I can narrow down to the individual
level.

The crosstab query is where I get my score totals for each score for each
person. If there is an easier way to do that (without the crosstab, please
let me know and I can figure the rest out from there). Like I said, I am
pretty new to all of this.

Thanks
DanG

 

[KARL DEWEY]

I write the word percentage, %age.

I am still lost. You are taking what percent of what?
(0.334 x %age of 3.0 files) + (0.667 x (1-(%age of 1.0+%age of 1.5))) = x

You are counting how many files are graded within the grade groups. Then
are you want the percent that number represents of the total the person
worked?

 

[DanG]

What we have is a system were a 3.0 file is a great file. 2.5 and 2.0 are
decent, 1.5 and 1.0 are crappy. We call the score a CQI score. The total #
of 3.0 cases out of the total reviewed gives you the percentage of 3.0 cases.
2.5 and 2.0 file scores dont enter the calculation other than as part of the
overall count of cases reviewed. 1.5 and 1.0 case file scores are added
together and subtracted out of 1.

The bad scores are weighted double compared to the 3.0 score, hence the .667
and the .334 in the equation.

So let's say Joe Blow had 10 files reviewed. He had this scoring breakdown:

3 - 3.0 scores - 3/10 or .30
2 - 2.5 scores - 2/10 or .20
1 - 2.0 scores - 1/10 or .10
2 - 1.5 scores - 2/10 or .20
2 - 1.0 scores - 2/10 or .20

Joe's CQI score would be: (.334 x .3) + (.667 x (1-.4)) = .5004

 

[KARL DEWEY]

Still lost.and the .334 in the equation.
How did you get the .334 in your equation from the data?

The .667?

What is the 1-.4 represent?

 

[DanG]

First off, I didnt come up with this CQI index equation, it is just what I
have to work with.

The .667 and the .334 assign the weight to each side. 2/3 or .667 and 1/3
or .334

..4 is the percentage of the 1's and the 1.5's added together. they are
subtracted out of 100% or 1, since it is a decimal in this case.

So a third of the total score is from the 3.0's (which in my example was 3
out of 10, or .3), so that is the .334 x .3.

Two third of the total score comes from the bad scores of 1.0's and 1.5's
added together (in my example it was .2 + .2 = .4). As mentioned above, it
is subtracted out of 100% or 1. That is where the .667 x (1 - .4)

Those two are then added together to get the 0.5004 as noted previously.

Does this make sense? Thanks for spending time looking at these posts.