Package Numbers

[gwc]

I need to create a report to produce shipping labels from a table in my database.

The table contains a number of fields of course, the four I think I need to get to work together are:

Quantity
Serial1
Serial2
Pkgs

In the shipping process, the items will be shrink wrapped into groups of 300. As an example:

For Customer Joe Schmoe, he has 1200 items going to him with a starting serial number of 3476 and an ending serial number of 4675. Since we are shrink wrapping in groups of 300, this means that there will be 4 total packages going to Joe Schmoe.

What I need to do is figure out how to create a report that will print out a label that has 'box 1 of 4' and 'serial# 3476 to 3775', 'box 2 of 4' and 'serial# 3776 to 4075', box 3 of 4' and serial.... and so on.

Any suggestions? Thank you in advance!

 

[cpod]

Not too hard to do. Just create a small table. Call is something like "SequentialNumbers". This table will have only one number field, let's call it "Num". Enter sequential numbers into this table, from 1 to as many packages as you will ever send to one person.

Now, create a query on your table and the sequentialnumber table. do *not* link the tables. Add your serial1, serial2 and pkgs fields to the query and the num field from the sequential table. For the criteria of the num field enter:

<=[pkgs]

Create two new fields in the query:

serialstart: [serial1]+([num]-1)*300

and

serialend: IIf([serial1]+([num])*300>[serial2],[serial2],[serial1]+([num])*300-1)

for your example, this will give you four entries for you four labels. For each label "Pkgs" gives you the total number of packages, "Num" gives you the package number and "serialstart" and "serialend" give you the range of serial numbers for that package.