1. ## AC97: Report problem

I have a table with evaluation scores:
ID, Q1, Q2, Q3, ..., Q12
Every Q-field has a score from 0 to 5

Now I want to see in a report per ID the number of 0s 1s 2s 3s 4s and 5s

e.g.
ID 2:
0 = 0
1 = 2
2 = 0
3 = 6
4 = 1
5 = 1

ID 3: ...

2. ## Re: AC97: Report problem

Generate a report with the ID field and group on the ID field.
In the details section of the report put 5 unbound fields, one for each Q field.
In the ControlSource property of these fields use the following expression:
=dCount("Q1", "tblYourTable", "ID=" & cstr(me![ID]))
For each unbound field use the appropriate Q-field (Q1, Q2,etc...)
I hope this will get you going.

3. ## Re: AC97: Report problem

This is not doing the trick I think Bart.
Maybe I was not clear.
I have 12 questions fields (Q1 - Q12). In every field there is a score from 0 to 5
In the list it must show per ID how many zeros there where (all the questions), how many ones, how many twos, ...

(In Excel I Can do it with a CountIf() function)

4. ## Re: AC97: Report problem

You can use a query for that.
I made an example for one field:
SELECT tblTest.Q1, Count(tblTest.Q1) AS Aantal
FROM tblTest
GROUP BY tblTest.Q1;

Is this what you are looking for?

5. ## Re: AC97: Report problem

Your solution is showing me per Q the values, but I need to see the values per ID

So for ID 1 I need to know how many times I have a 0 in Q1-Q12, and a 1 in Q1-Q12 and a 2...
And the same foor all other ID's
e.g.
TBL:
ID Q1 Q2 Q3 Q4 Q5 Q6 Q7 Q8 Q9 Q10 Q11 Q12
01 0 3 1 0 5 0 5 3 2 0 1 1
02 0 3 5 5 3 0 0 4 5 0 0 3
03 0 4 4 5 5 4 4 4 4 4 5 4
...

RPT:
--------------------------
ID 0 1 2 3 4 5
--------------------------
01 4 3 1 2 0 2
02 5 0 0 3 1 3
03 1 0 0 0 8 3
...

I hope this example makes it all clear Bart?

6. ## Re: AC97: Report problem

I can only think of one way to do this.
Define a new table with the layout of the report.
Start writing code to fill that table:

Delete all the records in the table
Loop through your table and start filling the new table like this:

set rsold = db.openrecordset("YourOldTable", dbopensnapshot)
set rsnew = db.openrecordset("NewTable", dbopendynaset)
with rsold
.movefirst
while not .eof
rsnew![ID] = ![ID]
Select Case ![Q1]
case 0
rsnew![fld0] = rsnew![fld0] + 1
case 1
rsnew![fld1] = rsnew![fld1] + 1
etc

Select Case ![Q2]
case 0
rsnew![fld0] = rsnew![fld0] + 1
case 1
rsnew![fld1] = rsnew![fld1] + 1
etc
end select

rsnew.update
.movenext
loop
end with
set rsnew = nothing
set rsold = nothing

Now open report based on the new table.

7. ## Re: AC97: Report problem

I had the feeling that only programming will give me the solution.
Thanks Bart, I will give it a try and let you know

8. ## Re: AC97: Report problem

Okay Bert,
I'm doing this for the firts time now (realy working with VBA) and have modified your listing as follows:

Dim db As Database
Dim rsold As Recordset
Dim rsnew As Recordset
Set rsold = db.OpenRecordset("EHC", dbOpenSnapshot)
Set rsnew = db.OpenRecordset("RPT", dbOpenDynaset)
With rsold
.MoveFirst
While Not .EOF
rsnew![ID] = ![ID]
Select Case ![Q1]
Case 0
rsnew![S0] = rsnew![S0] + 1
Case 1
rsnew![S1] = rsnew![S1] + 1
Case 2
rsnew![S2] = rsnew![S2] + 1
Case 3
rsnew![S3] = rsnew![S3] + 1
Case 4
rsnew![S4] = rsnew![S4] + 1
Case 5
rsnew![S5] = rsnew![S5] + 1

End Select
rsnew.Update
.MoveNext
Wend
End With
Set rsnew = Nothing
Set rsold = Nothing

When I run the code I get en error:
Objectvariabele of blokvariabele With is niet ingesteld (Fout 91)
(Nederlandese versie!)

Could you again help me?

9. ## Re: AC97: Report problem

On which line in the code?

You can scip the with part (and implement it later, it saves you typing and runs faster!)

change this part of the code:
'With rsold
rsOld.MoveFirst
While Not rsOld.EOF
rsnew![ID] = rsOld![ID]
Select Case rsOld![Q1]
Case 0
rsnew![S0] = rsnew![S0] + 1
Case 1
rsnew![S1] = rsnew![S1] + 1
Case 2
rsnew![S2] = rsnew![S2] + 1
Case 3
rsnew![S3] = rsnew![S3] + 1
Case 4
rsnew![S4] = rsnew![S4] + 1
Case 5
rsnew![S5] = rsnew![S5] + 1

End Select
rsnew.Update
rsOld.MoveNext
Wend
End With

You can include dutch error messages. No problem, dutch is my native language.

10. ## Re: AC97: Report problem

When I debug te code (return to the code screen after the error) it highlights te following line:

Set rsold = db.OpenRecordset("EHC", dbOpenSnapshot)

11. ## Re: AC97: Report problem

OK,

Sorry, I assumed you knew a little bit VBA.
At the top of the code add:
dim db as database
dim rsNew as recordset
dim rsOld as recordset
set db = currentdb()

At the end at the following line:

set db = nothing

12. ## Re: AC97: Report problem

Thats's okay Bart, I'm arealy glad that you help me. I'm learning a lot.

Thanks for the instructions. It is working good now.
Keep up the good work.

13. ## Re: AC97: Report problem

Patrick,

What do you teach?

14. ## Re: AC97: Report problem

Bart,
I'm teaching MS Office programs to endusers and Helpdesk people.
(zie ook je private mail voor meer info)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•