Bulk Order Database?

[sp00k]

Basically I have to create a database to keep track of bulk orders that we receive.

I have a tracking form to know when it came in, and what was in the certain shipment bulk order(finished this part)

Now the hard part. I should have another form that combines all the same items from the tracking form, to a total number.

(example:
tracking form:
Shipment 1: Received: 10 Keyboard and a Monitor
Shipment 2: Received: 20 Keyboards and a USB Flash Drive

Totals Form should show I have received 30 keyboards, a monitor, and a usb flash drive.

BUT! I have to allow a person to add or subtract directly from the total amount form. (so it can't really be based on the tracking form?)

The tracking form is used just for keeping track when and what came in on certain shipments.

Any ideas?

 

[The_Doc_Man]

This design is fatally flawed in this sense:

You have an order that comes in. You account for it.

Now someone comes behind you and edits the total, not via transaction but just by going in and changing the number.

You have instantly lost data integrity because at no time after initial data entry do you know what that number means. If this number that you track is to have ANY meaning based in reality, you can't do that. Rethink what you are doing.

If it is a valuable function to your company, you want to protect that total so that it retains meaning at all times. If it isn't valuable, why bother to compute it at all?

 

[sp00k]

Yeah, thats pretty much what my boss wants. He wants to know what came in, just in case there is any issues with the original shipment in later times. But he wants to know how much of *a certain item* he has at any given time. As well, he wants the employee who gets the items ready for the field, to be able to deduct or add from the total bulk order, not necessarily changing the original shipment amount.

Any ideas how I can make something work like this? or is it all flawed?
Should I go with
a) Bulk all items into totals from the start (when a certain piece comes in, just add it to the inventory)
b) Add it into the shipment inventory and do it again(tiresome i know) for the total inventory. Keeping the original shipment inventory solely for checking later(if a problem arises with the original shipment)

If every shipment has x amount of monitors, x amount of keyboars, x amount of tvs, am I allowed to make those fields? or should I just stay with PartDescription and fill in the rest?

 

[gemma-the-husky]

what you want is as follows

a) an order table
b) an assocated order lines table

so
a) order 1234, supplier Dell (say), date 15/1/09

b) order lines
order 1234 line 1 - 4 keyboards type A, price also?
order 1234 line 2 - 6 keyboards type B, price also?
order 1234 line 3 - 2 monitors, price also?

Now you also need to record the goods inwards - so possibly-

c) goods inwards header
document ref, date received, handled by, etc etc

d) goods received lines
document ref, line number, product received details etc

Then to achieve your aims, you need a mechanism to relate the items in the red entities table b) and table d) - either store the order line ref in the grn table, or vice versa - or even use an extra matching table to hold the relation

I do similar things to this - I even maintain the bulk order but modify the prices during the life of the order, by holidng a price history table in (or attached to) the order lines - depends on your industry, but the one I deal with treats a bulk order as a general requirement rather than a mandated order.

You probably may even need a third entity to to order/orderl ine scenario - that of a call-off instruction - so you have a bulk order for 1000 keyboards, and schedule them 100 a month, for the next 10 months - and then match the grn lines to the call-off table

You need to make your logical (computer) system work in the same way as your real-world situation - and not design a computer system that forces you to change the way you work (IMO) - although you may get some serendipitous improvements during the process