Can I add values to checkboxes for tallying?

[tac5829]

I am currently building an Access database. I have built my tables, queries, forms and reports. On one of the forms, I have checkboxes with headings on them; ie: Project Complete. Yet with each heading I want to assign a value. Then the values for each heading will be added per database entry, to give me the total percentage of project completion. Can I do this with check boxes or radio buttons?

Example: Item A (if checked)= 33%, Item B (if checked)= 33%, Item C (if checked)= 33%. Once adding (if A & B are checked) shows that I have 66% completion on that particular project.

 

[FoFa]

You would have to check the checkbox's (they are true or false only) and replace the true/false with what you want.
I.E. IIF(Checkbox1,33,0) as CKB1
So if it was checked (true) it would return 33, else zero.

 

[tac5829]

It shows a checkbox format in the table of yes/no which i think are 0/-1 values, but I don't think I can change this.

 

[FoFa]

you can't change the true or false, but you can check for it and return what you want depending on that value as I pointed out in the prior post.

 

[tac5829]

So do I place this type of equation in to the format type or in the default value in the Table? Or in the query? Also after the values are applied, how do I go about getting the totals added as I explained in the first post.

Thanks for your help.

 

[FoFa]

Well, truthfully, the easiest route would be to create a view (not an access term, but the same thing) which is a query that handles the calculations, and a primary key. Then you can join that to other queries and it is easier to manipulate. If you need the values in a form, it would be better to use DLOOKUP to pull them from the view for the form fields. To get the totals, if you join the first view, you can just add the fields together in the second query (remember, using the view makes it easier).

 

[tac5829]

Awesome. that was what I was thinking. Useing second query to do the calculations. Again though, and forgive me, but when I use the type of equation that you specified above, where would I place it? (table? query? and under what category?)

Thanks.

 

[FoFa]

What I was using would basically be in a query as a calculated column. In designer it would look something like this:
CHKB1: IIF([CheckBox1]=True,33,0)
This would create a column called CHKB1 that would have a value of 33 or zero depending on the status of CheckBox1.
So if you called it Query1 (I am so original) then in Query2 you joined Query1 to your table (remember the primary key in Query1 so you can join it) you would reference that value as Query1.CHKB1, or in designer it would look like any other field.

 

[tac5829]

excellent. I will give this a try......if it works......many thank yous. If not.....you'll probably see me back. hahahaha

Thanks.