Calculating percentage complete based on checkboxes – setting a variable?

[naomi1017]

I have four fields that are checkboxes. I want to create a variable called ‘percentage complete’, and populate it based on the checkboxes. For every checkbox that is checked (yes, -1) I want ‘percentage complete’ to go up by 25%. I’d like for this to happen in a query, so that I can pull the data into both a report and a pie chart. The pie chart would be the number of items that are 25, 50, 75, & 100% complete. The report would list each item and the checkbox data, grouped by the percentage complete.

I know how I’d do this in a webpage with ASP/VB – create a variable (dim: ‘PercentageComplete’) , then add to it with each checkbox (If CheckBox1 = ‘yes’, then ‘Percetange Complete’ = ‘PercentageComplete’ + 25.) But how would it work in Access?

 

[naomi1017]

Got it!

Here’s what I did:
Created a new field (ReadinessLevel) that lived in the main table with the 4 checkboxes. That field was a number field set to 0.

Created an update query that checked the first checkbox, if it was yes (-1), then the new value of the number field was “[tblCourse]![ReadinessLevel]+25”

Duplicated that update query for each of the four checkboxes, adding 25 each time.

Once I had the data in my table, getting the pie chart was a matter of using a query and ‘Group By’ and ‘Count’ to get a count of each variable, then running a report off that query.

Now I’m setting up a macro so I can run all 4 updates with one click. The only thing left to figure out is how to get my macro to click ‘yes’ for me each time the update query comes back and says ‘you are about to update X rows are you sure you want to do this?”

:banghead::banghead::banghead:

Arrgggh.
Thought I had it. But then I set up my macro, ran it a few times, and now I have readiness levels at 900%. Sigh.

So I need a way to only count each one once. Any ideas?

I was going to write an update query that would put everything back to 0 at the begginging of the Macro, but it seems like there should be a better way.