Occurrences of A, B, C, D in Questions 1-20 in report layout 1

vbaprogammer · Feb 28, 2000

I have created a report layout to display the number of times an answer (A, B, C, or D) are given to a particular question in a set of questions 1 to 20, multiple records. 
 
Sample desired layout: 
.........A....B....C....D 
Q1.......16...2....1....3 
Q2........9...2...16....5 
etc. 
 
Input screen: 
 
Q1....___....Q2....___....Q3....___ etc. 
 
I can get counts using Query 
Q1.........Q1 
DB1........DB1 
Group by...Count 
 
which produces 
 
Question 1 (dummy numbers) 
A....19 
B.....2 
C.....5 
D.....1 
 
I need the counts for multiple questions. Even though the query provides the counts for A, B ..., I can't extract them into the format desired. 
 
I also need them to display in the report as indicated above ('cause the client likes it). 
 
I can create some unsatisfactory results with one record and one question, but I can't figure out how to get the counts parsed so I can spread them across the page. 
 
So, 
 
Say 20 participants in the test 
20 questions 
 
.............A....B.....C....D 
Question 1...5....8.....2....5 
Question 2...3....9.....9....4 
Question 3...0...20.....0....0 
... 
Question 20 ... 
 
I have spent two days on this, and am suffering from serious "senior moments" and brain drain. Can anyone put me on the right track? Would appreciate it. 
 
Dan

elizabeth · Feb 28, 2000

Are you using the Crosstab Query Wizard?

vbaprogammer · Feb 28, 2000

Although I tried crosstab several times, I couldn't get the results I wanted, either because my data won't work in that scenario, or I don't know how to do it properly. 
 
Dan

DougP · Feb 28, 2000

Well Instead of trying to write one massive query try breaking it down in sections. 
See if you can make a simple query that gives some of the results. 
I always start problems that way then build on them. 
 
 DougP <a href=mailto: dposton@universal1.com> dposton@universal1.com</a> <a href= > </a>

vbaprogammer · Feb 28, 2000

I agree writing one massive query may not be the way to go, but I can only get results from one simple query. 
 
I can't seem to create more than one bit of information, from one question, from one set of participants. 
 
So what I need is how to reformat this information, duplicate it, and present it to the client so I have all 20 questions on the left and all counts of answers in columns on the right. 
 
My file structure is: 
RegistrantID 
Question1 
Question2 
Question3 
... etc. 
 
With Question 1-20 containing either "A", "B", "C", or "D" 
 
With future records totalling about 3,500. 
 
Perhaps it has been started completely wrong. 
 
Any help you can provide would be appreciated (or perhaps directions to some excellent training (as I feel out of my league on this one). 
 
Dan

elizabeth · Feb 28, 2000

Do you need to stay with that structure? Using that, the crosstab query wouldn't give you what you want in one step. It would work in one step with a structure of: 
 
RegistrantID 
QuestionNumber 
Answer 
 
Using QuestionNumber as your Columns. You might consider using this structure as it would be more flexible for analysis. A simple (manual) way change your structure is to set up a new table, and run an append query from your existing table. You'd run the query 20 times, each time just changing the Question/Column. Of course you wouldn't want to if you need to keep gathering data in your old format...

LWoods · Feb 28, 2000

Well, this may be "cheating", but here is a piece of code that will do the job for you...from the way you describe it. 
 
First, this assumes that your table is as you described it, with the fields as "Q1" thru "Q20" for the question values, and that there is a row for each respondent. It also assumes that the values are "A" - "D". In my sample code, I have assumed the name of this table is "Questions". You can change that, obviously. 
 
You need to create another simple table...I called it QSummary. It has 5 fields: 
 
Q Text 
A Number 
B Number 
C Number 
D Number 
 
After executing the attached code, this new table will be filled with the sums of the A's - D's for the questions, and the field named 'Q' will be the text values "Q1" - "Q20". The, just create a report.... 
 
Good luck! 
 
Larry Woods 
<A HREF="mailto:larry@lwoods.com">larry@lwoods.com</A> 
 
P.S. If you don't know how to execute the code, send me an email and I'll walk you through it. 
 
 
Code: 
 
 
 
 
 
 
Option Compare Database 
Option Explicit 
 
Public Sub LoadQ() 
Const QCOUNT As Integer = 20 
Dim Q(1 To QCOUNT, 1 To 4) As Long 
Dim intQIndex As Integer 
Dim intADIndex As Integer 
Dim strAD As String 
Dim rs As Recordset 
Set rs = CurrentDb.OpenRecordset("Questions&quot

Do While Not rs.EOF 
For intQIndex = 1 To QCOUNT 
strAD = rs("Q" & intQIndex) 
Select Case strAD 
Case "A" 
intADIndex = 1 
Case "B" 
intADIndex = 2 
Case "C" 
intADIndex = 3 
Case "D" 
intADIndex = 4 
End Select 
Q(intQIndex, intADIndex) = Q(intQIndex, intADIndex) + 1 
Next intQIndex 
rs.MoveNext 
Loop 
rs.Close 
Set rs = CurrentDb.OpenRecordset("QSummary&quot

For intQIndex = 1 To QCOUNT 
With rs 
.AddNew 
!Q = "Q" & intQIndex 
!A = Q(intQIndex, 1) 
!B = Q(intQIndex, 2) 
!C = Q(intQIndex, 3) 
!D = Q(intQIndex, 4) 
.Update 
End With 
Next intQIndex 
rs.Close 
Set rs = Nothing 
End Sub

vbaprogammer · Feb 29, 2000

Larry, 
 
It worked like a charm. Of course, I had to modify my routines a little, but your code worked without glitch. 
 
You have saved me hours of frustration. I have been working on this now for an hour, and accomplished more in this one hour than both days of last weekend. 
 
Elizabeth -- Yes, I agree. However, the client provided the data and the format. I still have a lot to learn about crosstabs. Seems you have it down pat. 
 
Thank you all for your quick responses. You all have proven there is a lot to learn, and thanks for sharing your knowledge. 
 
Dan

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Occurrences of A, B, C, D in Questions 1-20 in report layout 1

vbaprogammer

Programmer

elizabeth

IS-IT--Management

vbaprogammer

Programmer

DougP

MIS

vbaprogammer

Programmer

elizabeth

IS-IT--Management

LWoods

Instructor

vbaprogammer

Programmer

Similar threads

Part and Inventory Search

Sponsor