Append Query with no Unique key

jimmypickins

New member
Local time
Today, 15:12
Joined
Oct 22, 2015
Messages
3
Good day,

I am trying to build a database to handle a small inventory shipping and receiving. I thought I had everything working, but noticed right at the end that the append query was made incorrectly. This query is only handling what was actually shipped/received off the order as a whole. The problem is you can have multiple shipments per order.

More specifically I'll use an example:

[Order] 999 is created for [Quantity] 50 of [Item] 111. We only have 20 in stock, so [QtyShipped] is 20. The append query would then create a [Shipment] 1 for [QtyShipped] of 20 for [Item] 111 on [Order] 999. I use VBA on the form to set [QtyRemaining] to 50-20 to get 30 (which we need to track backorders).

The remaining 30 show up 3 days later, so I run through the same steps and the append query adds [QtyShipped] of 30 for [Item] 111 on [Order] 999. The VBA side keeps track and sets backorder to 0.

Because each line of the order can have multiple shipments, there doesn't seem to be a good unique key to use. I could probably use [Order]+[Date()] but if the very rare case that the backordered items show up in the afternoon it would prevent the user from being able to send the second shipment.

Is there a way to use the append table here? Any suggestions are greatly appreciated.

Thanks in advance,
Jimmy.
 
I would create a shipments table related to the order table, both having primary keys and with the shipments table having a (foreign key) field which specifies the order (primary key).

All table should always have a primary key, usually an autonumber datatype.
 
Hi llkhoutx,

Both tables do have a primary key, the problem is there is no unique field that I can use to index without duplicates as each order can have multiple shipments.

The primary key for the order table is [LineNumber] which is an indexed autonumber. The primary key for the shipment table is [ShipLineNumber] which is also an indexed autonumber. [LineNumber] can have multiple shipments against it if there are backorders (ie. not enough quantity to complete the request).

I think I found a way though, i created another line [UniqueShipKey] which is a string ("Line Number " & [LineNumber] & " on order " & [OrderNumber] & " on ' & Now()). This generates a statement such as "Line 1 on order 999 on 22/10/2015 2:45:22 PM". It's not used anywhere except to only append the table if it doesn't already exist, so no duplicates when you use an append query so long as you do not try to run it at the exact same second. That shouldn't be a worry because two employees should never be in the same order at the same time because the actual order processing is too small to warrant that concern.

I am still open to any other suggestions that might improve efficiency.
 
How many tables do you have? What do tyou append to? SHow your data structure.

Forget creating complex composite keys. That will bite you.

If you have an order and order lines, then you should have a shipment and shipment lines. Each shipment line will correspond to an order line. More than one shipment line can relate to the same order line. I.e. each of these holds the ID of the order line. Done!
 
Build a junction table to handle the orders to multiple shipments, i.e.

PHP:
Orders.PKO (one) >> (many) JcnOS.FKS
and
PHP:
(many) JcnOS,FKS>> (one) Shipments.PKS

The JcnOS table handles to one to many relationship of Orders to Shipments.
 
Junction table? What for? One shipment line cannot belong to multiple orders, can it? If not then logging each order line with all related shipment lines is enough.
 
Errr so what? Did you read #6? Each shipment line does not belong to multiple order lines. It's like parent and children - one parent many children. You don't have a many-to-many for that.
 
I didn't say that. Each order has multiple shipments.
 
You have not added any new information, so I point at #6 again.
 
Hi all,

I'm on mobile now so excuse the formatting or typos.

One order can have multiple shipments but each shipment only belongs to one order so the relationship is a many to one not a many to many.

I thought of doing a shipping header table but I feel I will run into the same situation, multiple shipments with no real new key.
 

Users who are viewing this thread

Back
Top Bottom