Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2004
    Location
    Alabama
    Posts
    4

    Unanswered: Grouping records in a query by the sum of a field

    I am trying to group records by a variable index value (percentage) in a query (or some other method).
    The basic idea is that I need to select future shipping items from a specified order and group them to fill trailers. Each record will fill some percentage of the trailer depending on the item. I have calculated a percentage of the trailer that each item will occupy (as a percentage or fraction of 1), but I cannot figure out how to get a query to group these in increments as close to 1 without going over...

    ITEM INDEX_VALUE
    Item A 0.1999
    Item B 0.2499
    Item B 0.2499
    Item C 0.3332
    Item B 0.2499
    Item B 0.2499
    Item C 0.3332
    Item B 0.2499
    Item B 0.2499
    Item C 0.3332
    Item B 0.2499

  2. #2
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    That sounds a little too complicated for a query. I think you are going to have to use some code to figure that out.

  3. #3
    Join Date
    Nov 2004
    Location
    Alabama
    Posts
    4
    Any ideas on what type of code to use? I am thinking a Do While Loop, but that is a new one for me. I am still novice at VBA...

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    You'll need to do the calculation in the query, then group on your calculated field.

    Eg:

    SELECT yourField, iif(yourField < 1, 1, 2) AS grouping_field
    FROM yourTable

    Then in the report, you would group on "grouping_field".
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  5. #5
    Join Date
    Nov 2004
    Location
    Alabama
    Posts
    4
    Would this IIF not simply return a series of 1's and 2's?

    What I am hoping to acheive is grouping (in sequence) a number of records out of the table that approach 1 without exceeding 1.
    I may not have clearly expressed my dilema. Remember that 1 is my trailer capacity, and these fractional values (.1999, .2499, etc) are the individual products that will take up space on the trailer.

    The bottom line is that I am trying to figure a way to fill trailers without the benefit of knowing every time how many records from the table (products) will fit on each trailer. I would like Access to select the all of the next products that will fit on one trailer, print picksheets for the warehouse and then later allow them to print 1 shipping document for the trailer. My issue is telling Access how to calculate the number of lines to put in each trailer.

    Thanks for the help, you guys are great...

  6. #6
    Join Date
    Nov 2004
    Location
    Alabama
    Posts
    4
    Would this IIF not simply return a series of 1's and 2's?

    What I am hoping to acheive is grouping (in sequence) a number of records out of the table that approach 1 without exceeding 1.
    I may not have clearly expressed my dilema. Remember that 1 is my trailer capacity, and these fractional values (.1999, .2499, etc) are the individual products that will take up space on the trailer.

    The bottom line is that I am trying to figure a way to fill trailers without the benefit of knowing every time how many records from the table (products) will fit on each trailer. I would like Access to select the all of the next products that will fit on one trailer, print picksheets for the warehouse and then later allow them to print 1 shipping document for the trailer. My issue is telling Access how to calculate the number of lines to put in each trailer.

    Thanks for the help, you guys are great...

Posting Permissions

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