How can I calculate % of correct responses PER RECORD

[aaron.irvine]

G'day!

I've been researching all day, and have finally given up. I'm an Access n00b, but can understand low-level coding when reading it.

What I'm looking to do is calculate the success rate (%) of compliance rates with Quality Control paperwork. Essentially, each record has 12 "check box" fields representing the different QC sheets that are submitted each day.

QCflavourChange
QCfillerOperator
QCblowMoulding
QCtorqueTest
QCnetContents
QClabeller
QCpacker
QCpalletiser
RMpreform
RMclosure
RMlabel
RMcarton


I have been able to find the code for counting the success rate of a specific type of QC sheet across multiple days worth of testing, eg:

=Sum(IIf([RMpreform]=True,1,0))])

But I have been unable to determine how I can count the total of all the successful fields listed above, across a single record.

I've learnt a lot today in how to use ACCESS, and I'm very excited to continue to learn how to use this very useful tool!

Thanks guys.

 

[Chrafangau]

([QCflavourChange]+[QCfillerOperator]+[QCblowMoulding]+[QCtorqueTest]+[QCnetContents]+[QClabeller]+[QCpacker]+[QCpalletiser]+[RMpreform]+[RMclosure]+[RMlabel]+[RMcarton])/12 AS Score

This is what I have been using on a similar project, just sums the fields and then divides the total by the number of fields. Messy but quick and easy.

 

[namliam]

True and False are only representations of what "in the core" is -1 and 0 each...
Abs(QCflavourChange+QCfillerOperator+QCblowMoulding...) /12
Should do the trick for you...

or if you prefer you can do
(Iif(QCflavourChange,1,0) + Iif(QCfillerOperator,1,0) + ... ) / 12

 

[Chrafangau]

Thanks namliam, forgot about that. I've been processing text results myself, so the IIf was included in an earlier query.

 

[aaron.irvine]

Thanks a tonne for the quick responses guys. I feel like a bit of an idiot just quietly! I figured out the rudamentary way of doing it about 5 min ago as I was packing up to go home - you've both expanded on that for me to make the coding a little smoother.

That'll teach me for having only 3hrs sleep last night! Such a simple formula....

Oh well, work is paying me regardless

 

[aaron.irvine]

True and False are only representations of what "in the core" is -1 and 0 each...
Abs(QCflavourChange+QCfillerOperator+QCblowMoulding...) /12
Should do the trick for you...

or if you prefer you can do
(Iif(QCflavourChange,1,0) + Iif(QCfillerOperator,1,0) + ... ) / 12

I was writing it up the 2nd way initially namliam, but I like the 1st way a little better.

Now I just need to do two more things to complete this one:

1. Formulate the results into graphs for reporting - hopefully I can create a PivotChart or something referring to the above fields.
2. Start & End dates for variable reporting capability. I'm sure that one's going to pretty straight forward.

I'm feeling a lot more confident with it after snapping out of my brain freeze!!

:banghead: :banghead: :banghead: :banghead: :banghead: :banghead: :banghead: :banghead: :banghead: :banghead:

 

[Chrafangau]

It's starting to sound like we're working on exactly the same project.

 

[aaron.irvine]

And it looks like you're 4 days ahead of me on the project mate haha