Make-Table Query to produce grouping of records 1

[scottparks]

I am pretty new to Access and I need some assistance. Here is my problem.
I know what my beginning and ending serial numbers are, and I know how many cases per pallet. What I need to do is group my serial numbers by pallet number.

Example.

ProductCode is the relationship link between all three tables.
Each case = 1 serial number

Info from Table 1 : Beg Serial = 5000 End Serial = 5250
Info from Table 2 : cases/Pallet = 65

What I want returned in Table 3 is:

Serial#'s Pallet#'s

5000-5064 1
5065-5129 2
5130-5194 3
5195-5250 4

This exampe should produce the 4 table entries above. I would prefer not to use any VB code. Using an SQL statement would be better.

Hope that makes sense
Thanks for any help you can give.

 

[Golom]

Select ((Serial-5000) \ 65) + 1 As PalletNo, ...

 

[scottparks]

Thank you for your quick response.

That will only produce one pallet number. I need to group the serial numbers also. The example I gave should produce four groups of serial numbers and 1 - 4 on pallet numbers.

Thanks

 

[Golom]

Select Serial, ((Serial-5000) \ 65) + 1 As PalletNo
From SomeTable

Will produce a list of serial numbers and the associated Pallet numbers for each Serial value.

If you want to see the output you illustrated

Select MIN(Serial) & "-" & MAX(Serial) As [Serial#'s]
     , ((Serial-5000) \ 65) + 1 As [Pallet#'s]

From SomeTable

Group By ((Serial-5000) \ 65) + 1

 

[scottparks]

Thanks GOLOM