Sum IF ( Similar to Excel)

learnaccesscg

Registered User.
Local time
Today, 00:32
Joined
Dec 1, 2014
Messages
12
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?
 
How about a DCount?
Code:
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")
 
Code:
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.
 
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?
 

Attachments

  • access example.JPG
    access example.JPG
    56.3 KB · Views: 126
Q1-Q17 are fields, that is correct. Also the ID is the Key field. The totals are also fields.
 
Alright, I just wanted to clarify that. You're in capable hands. Don't want to interfere ;)
 
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.
 
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.
 

Users who are viewing this thread

Back
Top Bottom