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.
7 2254 NeoPa 32,556
Expert Mod 16PB
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.
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 32,556
Expert Mod 16PB
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.
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 32,556
Expert Mod 16PB
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.
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 1,418
Expert 1GB
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_columns("Select * FROM QUESTIONS WHERE User='" & [User] & "';", 1, 75, 3)
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Joseph Shraibman |
last post by:
On a 7.3.4 database:
explain analyse select count(*) from elog;
Aggregate (cost=223764.05..223764.05 rows=1 width=0) (actual
time=81372.11..81372.11 rows=1 loops=1)
-> Seq Scan on elog ...
|
by: Nicolae Fieraru |
last post by:
Hi All,
I have a query, Select Count(BoolField) from tblMyTable, Where BoolField =
true.
If I run the query by itself, it returns the number of true records
I want to use the result of that...
|
by: DataFreakFromUtah |
last post by:
Hello!
No question here, just a procedure for the archive.
Search critera:
count records imported count data imported count number of rows
imported
count number of records imported record import...
|
by: matt (ziba) |
last post by:
using the Microsoft.Data.Odbc component instead of System.Data.Odbc
how do u get a row count using the Microsoft.Data.Odbc.OdbcDataReader ??
and is it best to use Microsoft.Data.Odbc OR...
|
by: Matthew Groch |
last post by:
Hi all,
I've got a server that handles a relatively high number of concurrent
transactions (on the magnitude of 1000's per second). Client
applications establish socket connections with the...
|
by: Tejpal Garhwal |
last post by:
I have datagrid filled with some data rows. At the run time i want know how
many total rows are there in the data grid ?
Any idea ? Any Suggestions ?
Thanks in advance
Tej
|
by: Martin Joergensen |
last post by:
Hi,
I have some files which has the following content:
0 0 0 0 0 0
0 1 1 1 1 0
0 1 1 1 1 0
0 1 1 1 1 0
0 1 1 1 1 0
0 0 0 0 0 0
|
by: heckstein |
last post by:
I am working in Access 2002 and trying to create a report from our company's learming management system. I am not a DBA and most of my SQL knowledge has been self taught through trial and error. I...
|
by: MP |
last post by:
vb6,ado,mdb,win2k
i pass the sql string to the .Execute method on the open connection to
Table_Name(const) db table
fwiw
(the connection opened via class wrapper:)
msConnString = "Data Source="...
|
by: sahil |
last post by:
Hello frends i am learning c language, I want to make a program which
count occurence of each element in an array .I write following code
for it but ity is not giving me desired result.pls help me....
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
|
by: ryjfgjl |
last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
| |