Calculation

[gdzilla]

I created a form that looks something like this:

***DETAIL***

N/A 1.Question?
Compliant 2.Question?
Not Compliant 3.Question?
Compliant 4.Question?
Not Compliant 5.Question?

and so on.....
************

***FOOTER****
Total N/A: _______
Total Complaint: _________
Total Not Compliant: _________
*************

NOTE: N/A, Compliant and Not Compliant are in a drop down menu or combo box.

My question is, how can I have it calculate the total number of N/A's, Compliant's and Not Compliant's in the footer from the detail section?

If possible, please provide the the whole expression.

Thanks is advance.

 

[lameid]

In your query for your report recordsource you need calculated fields to give you values for each type. For example you could make a complaint field were field is the name of the field that stores the data
Complaint: IIF([Field] = "Complaint",1,0)

On your report simply use a sum to get to the number...
=Sum([Complaint])

You'll need to do this for each option.

 

[gdzilla]

I kind of understand what your saying, but don't really know how to implement it and as you can tell I'm very new to access.

How would I enter the calculation in a query? under the "total" input area?

if you put it in plain english for me it would be great!

Thanks again.

 

[lameid]

Complaint: IIF([Field] = "Complaint",1,0)

The above goes in the same place as the field name. Everything to the right of the colon is a calculated field and to the left is its alias. In other words access uses Complaint as the alias or field name and If Field is equal to Complaint it makes the value 1 otherwise it makes it 0.

=Sum([Complaint])
The above goes in the control source property of your text box on one of the footers of your report.

 

[gdzilla]

Complaint: IIF([Field] = "Complaint",1,0)

okay, i got that working for one field, but i have how do i add up more than one field of 'compliant's'like how it is in my form?

For this example it should output "2".

***DETAIL***

N/A 1.Question?
Compliant 2.Question?
Not Compliant 3.Question?
Compliant 4.Question?
Not Compliant 5.Question?
Compliant 6.Question?
and so on.....
************

i tried putting these in different fields: Complaint: IIF([FieldONE] = "Complaint",1,0) -- Complaint: IIF([FieldTWO] = "Complaint",1,0), but it says duplicate output "Compliant". How do i make it so that it counts how many "Compliant's" in many fields?

Thanks for putting up with me.

 

[lameid]

Sorry I didn't pick up on the fact that you have 6 different fields. Ideally I would rewrite the database to have a table that relates the questions back to whatever your key is. Then you could have multiple questions and answers with out adding fields. This makes the whole thing a lot easier. You would add a subform to be able to have multiple questions.

Less than Ideal, you need 6 complaint fields...

Complaint1: IIF([FieldONE] = "Complaint",1,0)
Complaint2: IIF([FieldTWO] = "Complaint",1,0)

Etc.

Then in your control source you will need something like

=Sum([Complaint1]) + sum([Complaint2]) + etc...

 

[gdzilla]

Thanks for all your help lameid!