Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Jul 2002
    Location
    Oak Lawn, Illinois, USA
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    using a totals query to feed detail to a report (2002)

    SIGH-Things that seem so simple in concept get complex in a hurry. Have a student attendance table with an entry for each day the student is off and the reason. Want a detail of days off and reasons for all students with over 9 absences. In order to count the absences and bring back the students, I did the following query.

    Field: Last Name, FirstName, Date
    Table: all Absences table
    Total : GroupBy in Last Name, GroupBy in First Name, Count in Date
    Sort
    Show: Showing all
    Criteria: >9 in Date

    So far so good. The query brings back the correct numbers with the students. However, if I add reason to the grid, the count changes to count whatever the first reason happens to be. I have a hunch that I'm on the wrong track to accomplish my end result detail report. Any suggestions? Thanks in advance.
    Best regards,
    Barry Zakes

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: using a totals query to feed detail to a report (2002)

    I believe that you need 2 queries to accomplish this task.
    1. Build a query that lists from your table those students where there are more than 9 absences.
    SELECT [Last Name], [First Name], Count(*) as Cntr
    FROM [all Absences table]
    GROUP BY [Last Name], [First Name]
    HAVING Count(*) > 9
    2. Build another query based upon the 1st query and joined to table [all Absences table]

    Pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  3. #3
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: using a totals query to feed detail to a report (2002)

    I would add an autonumber field to the table, and do the count on that so the Date can be done as a Where or GroupBy with criteria, and put the Reason at the end also as a GroupBy. I think that may let you do the task with one query, but Pat's suggestion should also be looked at.
    Wendell

Posting Permissions

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