Results 1 to 14 of 14
  1. #1
    3 Star Lounger siflaar's Avatar
    Join Date
    Jan 2001
    Location
    Amersfoort, Netherlands
    Posts
    203
    Thanks
    4
    Thanked 0 Times in 0 Posts

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

    Please help me
    Greetings,

    Patrick Schouten
    (The Netherlands)

  2. #2
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    The Netherlands
    Posts
    216
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: AC97: Report problem

    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.

  3. #3
    3 Star Lounger siflaar's Avatar
    Join Date
    Jan 2001
    Location
    Amersfoort, Netherlands
    Posts
    203
    Thanks
    4
    Thanked 0 Times in 0 Posts

    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)
    Greetings,

    Patrick Schouten
    (The Netherlands)

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    The Netherlands
    Posts
    216
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #5
    3 Star Lounger siflaar's Avatar
    Join Date
    Jan 2001
    Location
    Amersfoort, Netherlands
    Posts
    203
    Thanks
    4
    Thanked 0 Times in 0 Posts

    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?
    Greetings,

    Patrick Schouten
    (The Netherlands)

  6. #6
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    The Netherlands
    Posts
    216
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

  7. #7
    3 Star Lounger siflaar's Avatar
    Join Date
    Jan 2001
    Location
    Amersfoort, Netherlands
    Posts
    203
    Thanks
    4
    Thanked 0 Times in 0 Posts

    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
    Greetings,

    Patrick Schouten
    (The Netherlands)

  8. #8
    3 Star Lounger siflaar's Avatar
    Join Date
    Jan 2001
    Location
    Amersfoort, Netherlands
    Posts
    203
    Thanks
    4
    Thanked 0 Times in 0 Posts

    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.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?
    Greetings,

    Patrick Schouten
    (The Netherlands)

  9. #9
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    The Netherlands
    Posts
    216
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

  10. #10
    3 Star Lounger siflaar's Avatar
    Join Date
    Jan 2001
    Location
    Amersfoort, Netherlands
    Posts
    203
    Thanks
    4
    Thanked 0 Times in 0 Posts

    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)
    Greetings,

    Patrick Schouten
    (The Netherlands)

  11. #11
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    The Netherlands
    Posts
    216
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #12
    3 Star Lounger siflaar's Avatar
    Join Date
    Jan 2001
    Location
    Amersfoort, Netherlands
    Posts
    203
    Thanks
    4
    Thanked 0 Times in 0 Posts

    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.
    Greetings,

    Patrick Schouten
    (The Netherlands)

  13. #13
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    The Netherlands
    Posts
    216
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: AC97: Report problem

    Patrick,

    Glad I could help.
    What do you teach?

  14. #14
    3 Star Lounger siflaar's Avatar
    Join Date
    Jan 2001
    Location
    Amersfoort, Netherlands
    Posts
    203
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Re: AC97: Report problem

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

    Patrick Schouten
    (The Netherlands)

Posting Permissions

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