# Thread: Grouping records in a query by the sum of a field

1. Registered User
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. Registered User
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. Registered User
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. Purveyor of Discontent
Join Date
Mar 2003
Location
The Bottom of The Barrel
Posts
6,102
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".

5. Registered User
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. Registered User
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
•