Sum IF ( Similar to Excel) (1 Viewer)

learnaccesscg

Registered User.
Local time
Today, 07:13
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?
 

pr2-eugin

Super Moderator
Local time
Today, 11:13
Joined
Nov 30, 2011
Messages
8,494
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")
 

BlueIshDan

☠
Local time
Today, 08:13
Joined
May 15, 2014
Messages
1,122
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.
 

learnaccesscg

Registered User.
Local time
Today, 07:13
Joined
Dec 1, 2014
Messages
12
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: 79

learnaccesscg

Registered User.
Local time
Today, 07:13
Joined
Dec 1, 2014
Messages
12
Q1-Q17 are fields, that is correct. Also the ID is the Key field. The totals are also fields.
 

vbaInet

AWF VIP
Local time
Today, 11:13
Joined
Jan 22, 2010
Messages
26,374
Alright, I just wanted to clarify that. You're in capable hands. Don't want to interfere ;)
 

learnaccesscg

Registered User.
Local time
Today, 07:13
Joined
Dec 1, 2014
Messages
12
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

AWF VIP
Local time
Today, 11:13
Joined
Jan 22, 2010
Messages
26,374
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

Top Bottom