When you have an incoming bulk order do you distribute it completely? or do you retain some on hand?
If you are basically the clearing house and every incoming bulk order is distributed as you get it you should have a table structure like:
Code:
BulkOrders (BOID autonumber PrimaryKey)
BOID BO_RecDate other information ------>
1 1/1/2011
2 1/2/2011
Items (ItemID autonumber PrimaryKey)
ItemID ItemName
1 ItemA
2 ItemB
BulkOrderItems (BOIID autonumber PrimaryKey)
BOID BOIID ItemID ItemQty
1 1 1 500
1 2 2 1000
2 3 1 1000
2 4 2 750
Locations (LocID autonumber PrimaryKey)
LocID LocName
1 LocationA
2 LocationB
OrderDistribution (DistID autonumber PrimaryKey)
BOIID DistID DistQty LocID
1 1 200 1
1 2 300 2
2 3 300 1
2 4 700 2
If you set it up like this then you can create forms with subforms that will inherit the parent item (the primary key fields) so that all you are storing is the primary key and you don't have to re-enter any information.