Calculations from multiple tables

[buzzmcduffie]

I have inherited a database and I need help with a calculation:

Table #1 (tblAuditData) has audit information: [AuditID] (key), [employee],
[claimnumber] etc concerning the claim that is being audited.

Table #2tblErrorData) has [AuditID] that matches (tblAuditData) and
[ErrorCode] the auditor found.

Table #3 (tblErrors) which has [ErrorCode] and [Category] (there are 5
different categories) and [ErrorDescription]

There will be one (tblAuditData) record but there can be many (tblErrorData)
records.

I can join [ErrorCode] in (tblErrorData) and [ErrorCode] in (tblErrors) to
retrieve the [Category] and [description] of the error found.

I have to do a calculation that I can easily do in Excel but I have to do it
in Access so managers can pull reports at any time:

Category Score: ( [# of Audits conducted] minus [the number of errors in the
category]) / [# of Audits conducted]
This calculation will give us the number of audits without errors for the
each of the 5 categories. If no there were no errors found in one of the
categories, I want to see that category listed as 100%. i.e. There won't be
a record on the (tblErrorData).

So - any ideas?
thanks for any help you can give me... my next hurdle is figuring out how to
do a rolling 3 month score for each category...

 

[KARL DEWEY]

Category Score: ( [# of Audits conducted] minus [the number of errors in the
category]) / [# of Audits conducted]

I do believe there is a problem with your methodology. You can never have
more audits conducted for a category than errors. There must be at least one
error to have the category.