Results 1 to 8 of 8
  1. #1
    Join Date
    Apr 2008
    Location
    All over, Now in Missouri
    Posts
    73

    Unanswered: How to Group a Report by query record size

    I query a class by class number (08-027), but now I want to break that class down by A, B, or C Group based on number of students in query, this week it could be 80, next week 110.

    What I am looking at.
    - Run a query to pull everyone in that class (08-027)
    - Based on the class size (lets say 75) divide it by 3
    - Print a report for the first 25 as group A, next 25 as group B and last 25 as group C.

    I dont know where to start on this! (Case, IIF, etc.)

    Peter

  2. #2
    Join Date
    May 2008
    Location
    Raleigh, NC
    Posts
    151
    This will probably be a multi-step process. If the record source for your query is a table another column containing a 'report group' might be needed. You could update this column in code prior to the actual query running and then the report could be filtered upon the recently updated report group column.

    Just a quick thought. Good luck!

    Stu
    --If its free, take it for what its worth!

  3. #3
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    VBA.

    Code:
    If Class_Size > 25 Then
       'do this
    ElseIf Class_Size > 50 Then
       'do that
    ElseIf Class_Size > 75 Then 
    ... etc.
    Endif
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  4. #4
    Join Date
    May 2008
    Location
    Raleigh, NC
    Posts
    151

    But where...

    Quote Originally Posted by StarTrekker
    VBA.

    Code:
    If Class_Size > 25 Then
       'do this
    ElseIf Class_Size > 50 Then
       'do that
    ElseIf Class_Size > 75 Then 
    ... etc.
    Endif
    But where do you determine the Class_Size, in a seperate query? And then how do you filter the report to just run for the first 25 (example in #1) people? Real curious about this one since I don't think there is a quick and dirty answer.
    --If its free, take it for what its worth!

  5. #5
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Sorry, my post was way too vague. I was just trying to say that I think a VBA script is needed to do what you want... Let me clarify:

    One VBA procedure could do things like:

    1. Query the students in the class and get a count. In that query it can exclude those in a temporary table.
    2. Divide the student count by 3, giving NoInGroup.
    3. Get the top <NoInGroup> records from the query used in 1.
    4. Print a report, changing labels on it to identify the group.
    5. Write the IDs of the records from 3 to the temporary table.
    6. Repeat steps 3 to 5 until no records are returned by 3.

    Hope that makes things clearer
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  6. #6
    Join Date
    May 2008
    Location
    Raleigh, NC
    Posts
    151
    Excellent! I was thinking along those same lines.
    --If its free, take it for what its worth!

  7. #7
    Join Date
    Apr 2008
    Location
    All over, Now in Missouri
    Posts
    73
    That process is clear, but writing the code is another story. It is amazing the hurdles one faces to complete a project.

  8. #8
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    If development was easy, I'd be out of a job!
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

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