design problem

[angie]

i have two tables as follows:

orders table, fields: order number, quantity ordered, shipment code.

invoices table, fields: order number, quantity received.

most of the times our supplier does not ship all the quantities ordered. so
this means that backorder items should be updated with a new shipment code. i
cannot figure out the best way to do this because i have multiple entries of
items with the same order number in both tables.

i was thinking of creating a series of queries that would add records with
negative quantities in the orders table with the initial shipment code and
add positive quantities in the same table with a new shipment code.

can anyone make a suggestion? i have been doing all the updates manually
and it is really time consuming!!!

 

[vanderghast]

The expected end result is not very clear, but indeed, if you have two
tables:

Orders
OrderID, ItemID, Qty, ...

and

Receptions
ShipmentID, OrderID, ItemID, Qty, ...


where, for one order, one item, you may have many records in Receptions, you
can then SUM the quantity over the items to know which items are still in
back order:

SELECT OrderID, ItemID, SUM(qty) AS totalReceived
FROM receptions
GROUP BY OrderID, itemID



as saved query, say q1, then

SELECT orderID, ItemID, qty-Nz(q1.totalReceived, 0)
FROM orders LEFT JOIN receptions
ON orders.orderID = receptions.orderID
WHERE qty-Nz(q1.totalReceived, 0) > 0

would give you the items ID (by order id) which are still back order.


If you have just one table, that would be even easier, uisng + for ordered
quantity, and - for received ones. Get one record per reception, and then,
simply :


SELECT orderID, itemID, SUM(qty)
FROM oneTable
GROUP BY orderID, itemID
HAVING SUM(qty) > 0


You don't have to 'manually' update any records, just USE the query with the
SUM when and where you need the 'updated' sum. That won't take much more
time, and will always be up to date.




Vanderghast, Access MVP

 

[Daryl S]

Angie -

I suspect your table design is not quite right (or maybe you just didn't
include all the pertinent information). You said you could have multiple
records in the orders table for one order. This would indicate to me that
you should have an Orders table and an OrderItems table. Only one order
record per order in the Orders table, and the multiple items would each have
their own row in the OrderItems table. You should have a unique, required
index on the OrderNumber in the Orders table. The OrderItems table should
have its own unique (autonumber?) index as well as a foreign key for the
Order Number it is related to. The Shipment Code should not be on the Orders
or OrderDetails tables, as there can be more than one shipment per order.

As for the Invoices, is this really a shipment notice for part of an order?
If so, you should have an Invoice table with a unique Invoice Number (is this
the shipment code?), and a foreign key to the OrderNumber it is related to.
Then an InvoiceDetails table that shows each item received and the quantity
received for each item in that invoice (shipment). The quantity here may or
may not match the quantity in the OrderDetails. You may have more than one
shipment (invoice) per Order, to handle back-orders, but each Invoice will be
related to one Order.

To show the back-ordered items, you sum the item quantities in the
InvoiceDetails for each Item and subtract from the OrderItems quantity for
that item.

This would not require any manual updating - you simply add in the Invoices
(and InvoiceDetails) as the shipments are received.