Sum IF ( Similar to Excel)

[learnaccesscg]

I am trying to have a field auto sum if specific values come up within a form. The form is a survey. Total all number zeros, total all 1's, 2's etc. so that I get a discrete value total for each option.

Ex.

Q1) 1 Q9) 0
Q2) 1 Q10) 1
Q3) 0 Q11) 2
Q4) 2
Q5) 1
Q6) 1
Q7) 3
Q8) 1

Total Number occurrences

Total 0's= 2
Total 1's= 6
Total 2's= 2
Total 3's= 1

Each question in the above example is a field within the same Form. Each total is its own field. Does anyone know if this can be done. and if so how?

 

[pr2-eugin]

How about a DCount?

Total 0's = DCount("*", "yourTable", "yourNumberFieldName = 0")
Total 1's = DCount("*", "yourTable", "yourNumberFieldName = 1")
Total 2's = DCount("*", "yourTable", "yourNumberFieldName = 2")
Total 3's = DCount("*", "yourTable", "yourNumberFieldName = 3")

 

[BlueIshDan]

SELECT DISTINCT num, Count(num) As [Count]
FROM [table]
GROUP BY num

Edit: My mistake, I failed to read that you were placing these on a form's fields.

 

[learnaccesscg]

Hi! Thank you both for your replies. I am a bit of a noob when it comes to access so I need a bit more explanation about how to make this code. I have also decided to enclose a picture of what I am talking about.

Each field should be added only within the record. I do not want to add total occurances from all records, just within one record. I hope the attached picture gives better insight.

Is this possible?

 

[vbaInet]

I would imagine Q1 to Q17 are fields?

 

[learnaccesscg]

Q1-Q17 are fields, that is correct. Also the ID is the Key field. The totals are also fields.

 

[vbaInet]

Alright, I just wanted to clarify that. You're in capable hands. Don't want to interfere

 

[learnaccesscg]

Thanks! So far I am trying to figure out how to set up a Sum IF function, but I am not sure how to get it to do multiple fields.

 

[vbaInet]

Looks like BlueIshDan and pr2-eugin haven't been online since.

What you need is a ParamArray(). Create a function that will take all the fields as parameters, loop through the ParramArray() and for each item check whether it's a zero (i.e. for the zeros) and increment an integer variable by 1. Look into ParamArray() and see what you come up with.