AC97: Report problem

Topic

New Reply

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: …

Please help me

Reply | Quote

Replies

Start with the report wizard.
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.

Reply | Quote

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)

Reply | Quote

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?

Reply | Quote

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?

Reply | Quote

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.addnew
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.

Reply | Quote

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

Reply | Quote

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.AddNew
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?

Reply | Quote

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.AddNew
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.

Reply | Quote

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

Set rsold = db.OpenRecordset(“EHC”, dbOpenSnapshot)

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

Patrick,

Glad I could help.
What do you teach?

Reply | Quote

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

Reply | Quote