Incomplete Orders...

[Deinoc]

So I have been using this database and I am trying to redesign some of its bugs... One concept I cannot wrap my head around is incomplete orders.

Usually we get a stack of items shipped every month and sometimes they are incomplete... Currently I have a received field and an amount received field [Part-QTY] (a greyed out text [QTY] letting the users know the total # expected) they also have a Box field were we store it in our warehouse[BoxNo].

If a week passes by and we get a new order and with that order the leftovers showed up... then I either have to delete the current [Part-QTY] field or the [BoxNo]... I have no idea how to simply add a solution to this problem of partial orders, it usually happens on single iterations meaning just one split of the total. Thanks for your help

orden	cliente	Item	QTY	Part-Qty	BoxNo
10402​	Custo 1	12345​	1​	1​	1​
7145​	Custo 1	12346​	1​	1​	1​
10522​	Custo 2	12347​	1​	1​	1​
7158​	Custo 3	12348​	1​	1​	4​
7922​	Custo 4	12349​	1​	1​	4​
7455​	Custo 5	12350​	1​	1​	5​
7373​	Custo 6	12351​	1​	1​	5​
8720​	Custo 7	12352​	30​	20​	3​
8772​	Custo 8	12353​	1​	1​	2​
8765​	Custo 8	12354​	25​	25​	1​
8745​	Custo 8	12355​	25​	18​	1​
8764​	Custo 8	12356​	25​		0​
8824​	Custo 8	12357​	25​		0​
8769​	Custo 8	12358​	25​		0​
8803​	Custo 8	12359​	15​		0​

 

[petertenthije]

Does the [Orden] column allow duplicate values?

Assuming duplicates afe allowed, and looking at your partial receipt of orden 8720: I would change the value in column [QTY] to 20 to balance out that row. Then I would create a second row with [Orden] number 8720 with a remaining [QTY] of 10.

if duplicates are not allowed, I would still create a second row, but create a suffix behind 8720. Make a second order numbered 8720A, 8720.1 or 872000001. Which of these three options would depend on the field settings (integer number? partial number? short text?). The decimal point would be the easiest, as you could simply do ([orden]+0.1) which could allow your database to handle up to 10 deliveries for the same [Orden].

 

[J.Ty.]

I would modify the proposal of petertenthije slightly and add a separate column [Shipment]. An order can have any number of shipments, always beginning with 1. The same idea is really present above, but in a form which violates the First Normal Form, i.e., that there is always one piece of information in each cell. It is always better to preserve 1NF.

J.Ty.

 

[JonXL]

It sounds like you actually have two different things you are tracking, the orders and their receipts.

And it also sounds like these things are related to one another with a many to many relationship where one order can have multiple receipts associated with it and one receipt instance can contain multiple orders.

These things tell me what you really need is another table - or two - one to track the receipts and one to relate receipts to orders.

When staff are going through each receipt, they log the receipt and then link orders and quantities to it. When a receipt is logged later for an earlier (but incompletely received) order, they log the receipt and then link that order to it indicating the extra quantity received.

This change will require some work on your forms and possibly reports. It also might be more than your'e looking for in terms of tracking what's going on with your orders (maybe the split receipts don't happen often-enough and just modifying the form to allow editing the received quantity is sufficient, especially if you don't care to have a record of when the order came in and it all goes in the same box).

So is this a route you're interested in going?

 

[J.Ty.]

It sounds like you actually have two different things you are tracking, the orders and their receipts.

And it also sounds like these things are related to one another with a many to many relationship where one order can have multiple receipts associated with it and one receipt instance can contain multiple orders.

These things tell me what you really need is another table - or two - one to track the receipts and one to relate receipts to orders.

Using this terminology, I have offered a different perspective, where receipts are not first-class entities. In this case we are not interested in recording receipts as such. Therefore each order has its own sequentially ordered sequence of (perhaps partial) shipments, and we cannot tell later if parts of two particular orders arrived together or not.

I have guessed this observing that no typical shipment information is recorded, like date of delivery.

J.Ty.