Results 1 to 3 of 3
  1. #1
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    In the attached db there are two tables tblchildren and tblAttendance. tblAttendance records whether the children attended a session, and whether they got a phone call about Not Attending. The Phone Call does not happen if they did attend, so is not relevant in that case.
    Furthermore, Children in the Control group are not expected to attend session 2, even though there is a record in tblAttendance. This record is irrelevant.

    So I am making a query to flatten the data and show each child's attendance in a single row of data.

    [attachment=85090:qryAttendance.gif]

    qrySession2Attendance only has records for the Intervention children, yet the query returns values for S2PhoneCall for those children. Why?
    [attachment=85091:qryAttendance2.gif]

    Is there any way to get rid of them other than using IIF statements such as I have in qryAttendance2?
    In the real situation there are lots more sessions and lots more fields so I was hoping to avoid using lots of IIFs.
    Attached Images Attached Images
    Attached Files Attached Files
    Regards
    John



  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    I'd set up a table with the desired "flat" structure, and use VBA code to:
    - Delete all records from the "flat" table.
    - Loop through the records of the Attendance table to populate the "flat" table.

  3. #3
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Thanks for that suggestion. It suggested to me an alternative that I will try first. Use my query to append records to a temp table, then run an update query on that to delete data I don't want.
    If that still does not work properly, I will start again with VBA.
    Regards
    John



Posting Permissions

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