Count data

[arynn]

hi.

i have a table name result that contain result from a survey form.
The table have username, a1,a2,a3,a4,a5, a6,.......a75 column which represent username and their answers to each question. The answer range from 1 to 8.

How do I count number of times each user chooses 3 as the answer?

thank you.

[NeoPa]

Are you saying you have 75 tables (from A1 to A75)?
I think this is a problem with a solution but I'll need some help clarifying the problem before I can find it.

[arynn]

only 1 table.
the survey form have 75 question.
so i create the table something like this...

username | a1 | a2 | a3 | ...........| a75 | ==> header
arynn | 1 | 3 | 3 | .........| 8 | ==> data

[NeoPa]

That's much clearer thank you.
Unfortunately the problem is harder to deal with than counting values in the SAME field across DIFFERENT records.
A Cross Tab query may give you better results but I can't help you there as I've only ever used that feature once.
If that's not a viable solution then the only thing I can think of is messy :-

IIf(A1=3,1,0)+IIf(A2=3,1,0)+IIf(A3=3,1,0)+...

As you can see - not nice.
Someone else may come up with help on the CrossTab query or maybe a better way that I didn't think of :( - sorry.

[arynn]

That's great. Thank you.
As messy as it could be, it's better than having nothing at all.
I have been working on this problem for days and seems to find no solution plus the dateline for my assignment is next monday!.
Thank you again. I'll give it a try.

[NeoPa]

I had a quick look at cross-tab queries and couldn't find a way to use it for this problem.
I'd be interested to hear if anyone else can find a more elegant way to get this data.

[MMcCarthy]

hi.

i have a table name result that contain result from a survey form.
The table have username, a1,a2,a3,a4,a5, a6,.......a75 column which represent username and their answers to each question. The answer range from 1 to 8.

How do I count number of times each user chooses 3 as the answer?

thank you.

The only way to do this neatly is to use VBA and recordsets. First create a new table to hold the results. e.g. tblResultsTotal

Put in the following fields

tblResultsTotal
UserName (Text field)
CountOf1 (Number field)
CountOf2 (Number field)
CountOf3 (Number field)
CountOf4 (Number field)
CountOf5 (Number field)
CountOf6 (Number field)
CountOf7 (Number field)
CountOf8 (Number field)

Now create the following procedure behind a command button on a form. e.g. cmdCalc

 
Private Sub cmdCalc_Click()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim count1 As Integer
Dim count2 As Integer
Dim count3 As Integer
Dim count4 As Integer
Dim count5 As Integer
Dim count6 As Integer
Dim count7 As Integer
Dim count8 As Integer
Dim i As Integer
	Set db = CurrentDb
	Set rs = db.OpenRecordset("Result")
	
	rs.MoveFirst
	Do Until rs.EOF
		count1 = 0
		count2 = 0
		count3 = 0
		count4 = 0
		count5 = 0
		count6 = 0
		count7 = 0
		count8 = 0
		For i = 1 To 75
			Select Case rs.Fields("a" & i)
			Case 1
				count1 = count1 + 1
			Case 2
				count2 = count2 + 1
			Case 3
				count3 = count3 + 1
			Case 4
				count5 = count4 + 1
			Case 5
				count5 = count5 + 1
			Case 6
				count6 = count6 + 1
			Case 7
				count7 = count7 + 1
			Case 8
				count8 = count8 + 1
			End Select
		Next i
		DoCmd.RunSQL "INSERT INTO tblResultsTotal (Username, CountOf1, CountOf2, CountOf3, " & _
					 "CountOf4, CountOf5, CountOf6, CountOf7, CountOf8) VALUES ('" & _
					 rs!UserName & "'," & count1 & "," & count2 & "," & count3 & "," & _
					 count4 & "," & count5 & "," & count6 & "," & count7 & "," & count8 & ");"
		rs.MoveNext
	Loop
	
	rs.Close
	Set rs = Nothing
	Set db = Nothing
	
End Sub

[PEB]

Maybe this cabe also helpfull:

Create this function in a module:

Function Value_in_columns(SQL, Start_col, End_col, MyValue) As Double
Dim mydb As Database
Dim myr As Recordset
Dim i
Dim values


Set mydb = CurrentDb()
Set myr = mydb.OpenRecordset(SQL)

values = 0

myr.MoveFirst
For i = Start_col To End_col
    If myr(i)=MyValue  Then
        values = values+1
    End If
Next i
myr.Close
mydb.Close

Value_in_columns = values
End Function

Then in your query that is used to show you this information:
In an empty column write:

3R:Value_in_col umns("Select * FROM QUESTIONS WHERE User='" & [User] & "';", 1, 75, 3)