Make record copies, append names

[Tom McMillion]

I have an Orders table with a Work Order Number ("W/O
Number") field having the primary key. W/O Number is a
text field because it contains a letter followed by a
series of numbers, for example: W12345678

Every order larger than approximately 20,000 must
be "split" into at least 2 (or more depending on order
size) smaller orders for easier processing through a
packaging plant. For the sake of discussion, a 100,000
piece order would be split into 5 duplicate orders of
20,000 each.

All multiple Orders in such cases share the same common
W/O Number, with the exception of -01, -02 etc. being
added to the base number identifying each of these as a
separate order. Because it is the primary key field,
every W/O Number must be a unique number, yet at the end
of the day, this group of Orders can be easily pulled
together and shipped as one large order with a common W/O
Number of W12345678 in the above example.

I would like to programatically accomplish the following
tasks:

First, copying an entire record that was created as W/O
Number W12345678-01 (for example) back into the table as
a new record with the W/O Number incremented to W12345678-
02. The first Order in every case where a large order
needs to be split, will always have -01 on the end.

Secondly, I would like for this operation to be
repeatable as many times as necessary to create
additional Orders ending in -03, -04 etc. as needed.

Does this sound doable by the click of a button or other
simple front end presented to the Order Entry clerk? One
more thing. The data entry clerk will simply adjust the
order quantity on the last order if it's not an exact
multiple of 20K.

Thanks in advance. Or let me know if you need any
further info.

 

[Jonathan]

-----Original Message-----
I have an Orders table with a Work Order Number ("W/O
Number") field having the primary key. W/O Number is a
text field because it contains a letter followed by a
series of numbers, for example: W12345678

Every order larger than approximately 20,000 must
be "split" into at least 2 (or more depending on order
size) smaller orders for easier processing through a
packaging plant. For the sake of discussion, a 100,000
piece order would be split into 5 duplicate orders of
20,000 each.

All multiple Orders in such cases share the same common
W/O Number, with the exception of -01, -02 etc. being
added to the base number identifying each of these as a
separate order. Because it is the primary key field,
every W/O Number must be a unique number, yet at the end
of the day, this group of Orders can be easily pulled
together and shipped as one large order with a common W/O
Number of W12345678 in the above example.

I would like to programatically accomplish the following
tasks:

First, copying an entire record that was created as W/O
Number W12345678-01 (for example) back into the table as
a new record with the W/O Number incremented to W12345678-
02. The first Order in every case where a large order
needs to be split, will always have -01 on the end.

Secondly, I would like for this operation to be
repeatable as many times as necessary to create
additional Orders ending in -03, -04 etc. as needed.

Does this sound doable by the click of a button or other
simple front end presented to the Order Entry clerk? One
more thing. The data entry clerk will simply adjust the
order quantity on the last order if it's not an exact
multiple of 20K.

Thanks in advance. Or let me know if you need any
further info.
.

Hi Tom,

you may like to read up on Normalising the design of a
database.

Consider instead of creating duplicate records having
another table to store related records. In your case it is
details for orders. This way you store basic/common
information in your order table and quantity specific
details in an order details table.

Using this approach simplifies everything, including
analysing and reporting sales.

Luck
Jonathan