I have a table that has these fields - Loc, Style, #Cases, Pallet, Plant
I need to make a query to make a new table that will assign a serial number to each case on a pallet.
So the original table would look like:
Loc Style #Cases Pallet Plant
10 AAA 3 P011 02
15 SWS 2 P033 04
The new table will look like:
Loc Style Pallet SN
10 AAA P011 0200001
10 AAA P011 0200002
10 AAA P011 0200003
15 SWS P033 0400004
15 SWS P033 0400005
I need to use the #Cases to figure out how many records for each pallet. Then I would generate a serial number (SN) in sequential order but using the Plant number for the start of each number.
Not sure the best way to tackle this.
My goal is to use this final table to run case labels and palet label from a report (Pallet label followed by the cases on that pallet, next pallet label then cases on that pallet etc.) That way the operator can stick the pallet label on the pallet first then the case labels to the cases.
Thanks for your help.