Query percentage calculation help

[glb466]

Hello. I have created a DB to manage our projects (ATM installations). Once a week a summary report is provided to upper management giving them the status of each project. On this summary report they want to see a "percent complete". This percent is derived from the status fields (approvals, ordered, received, ready to ship, and completed). These fields are check boxes on the data entry form. Each of these should be equivalent to 20% so when all are checked the project is 100%. I am at a loss at how to create a query that will look at all 5 fields and return the appropriate completion percentage. Thanks in advance!

 

[GohDiamond]

If you're using a Yes/No checkbox then the value of a Checked Yes/No checkbox is -1 with an UNchecked Yes/No checkbox = 0

Here is one solution example:

Add an evaluation column to get a sum of the checkboxes's value. It'll be negative.

To calculate the percentage the way you propose you'll need to flip the negative sum in the evaluation field, multiply by 20 (since each step represents 20% completion by your definition), and divide by 100, THEN format that as a Percentage.

I set up a table called tblCheckboxes to run an example:

SELECT checkbox1, checkbox2, checkbox3, checkbox4, checkbox5, Val([checkbox1]+[checkbox2]+[checkbox3]+[checkbox4]+[checkbox5]) AS Evaluation, -[evaluation]*20/100 AS Percentage
FROM tblCheckboxes;


Is that too simplistic, cuz I've guided people in the wrong direction before, LOL.
Hope that helps,
Goh

 

[glb466]

Worked like a charm!!