Calculating a score based on yes/no parameters

[okieheart]

I'm working on the attached database, which is to be used to track employee audits. Employees are reviewed based on four criteria (Complete, Notes Correct, Update, & Other) & are scored either Yes (criteria met) or No (not met). They must get at least 3 Yesses to pass, otherwise that case fails. And they must pass at least 80% of the cases to pass the audit. I think I have everything properly set up. Now I need to create a report to filter those audits that did not pass. I believe I need to create a query in order to make a report showing all the failing audits. For the database I uploaded, the report should list Employee 1 as failing; Employee 2 and Employee 4 as passing. Can someone point me in the right direction?

[ranman256]

Q1, run a query to count yes/no's /person.
Q2, run a query to count all questions.

Q3,make a query to join the 2 above, and get %.

[ssanfu]

Welcome to the forum

Not sure what you want the report to look like, but take a look at this one.

I did a lot of modifications to the table and form names. You had/have a field name of "Update"; this is a reserved word in Access. I changed it to "NeedUpdate".
I added "_PK" and "_FK" suffixes to field names.

I do not use calculated fields IN TABLES. Nor do I use Look Up FIELDS in tables. See The Evils of Lookup Fields in Tables
BUT, I didn't change them. (its your dB )

You have a field/table "AuditMonth". Don't understand this (but you must ) This could be automated - after you enter an audit Date, calculate the EOM (End Of Month) and push it into the Audit Month field. No need for a table.

I added a couple of queries and reports. There are two buttons on the form to see the reports.

[okieheart]

Thanks. Audit month is when the audit is supposed to occur. For example, the auditor gets a list of all audits for October. Normally they're done that month, but sometimes they get pushed to the following. So we need the audit month field to track whether they are doing their audits in a timely manner.

So if you wouldn't use lookup fields or calculations, what would you suggest?

[ssanfu]

So if you wouldn't use lookup fields or calculations, what would you suggest?

Instead of Lookup FIELDS in a table, use combo boxes on a form.
Simple calculations can/should be done in a query, More complex calculations could be done in a UDF or just use VBA.

My view is that tables are for storing data and nothing else. If you ever decide to move the data to a big dB engine (SQL Server, MySQL, Oracle, etc) you will have to redesign your dB because Access (JET/ACE) is the only DBE that allows calculations and Lookup FIELDS in tables.

Much easier/better to do it right the first time........

[okieheart]

Thanks for the explanation. It makes sense. And I agree, always easier to design it with thoughts toward moving it out of Access and into SQL. For our purposes, that won't happen, but I'll definitely keep it in mind for my next project.