It looks like you need to work normalizing your tables. I'm by no means an expert on normalization but hopefully I can at least point you in the right direction.
There's some links in this thread
that have been very helpful to me.
I might do the structure a little more like this.
ShipMethCost (this is going on an assumption that there is a fixed cost related to a given shipping method, any calculations on cost, or any calculations for that matter, should be done at runtime and never stored in a table)
(This will be the parent table)
OrderCatID (FK linked to ShipMethID in tblShipMethod)
CustomerID (FK linked to CustomerID in tblCustomer)
(This will be the child table)
OrderID (FK linked to OrderID in tblOrderMain)
ItemID (FK linked to ItemID in tblItem)
VendorID (FK linked to VendorID in tblVendor)
ShipMethID (FK linked to ShipMethID in tblShipMethod)
OrderQuantity (for the particular item)
DateReceived (I put this in the line item in case different products on one order are shipped seperately and arrive on different days.)
Anyway, that's how I'd set it up. Again, by no means am I normalization expert and some of the regulars might say I don't know what I'm talking about. If they do, believe them!
Hope that helps!
Edited by: SWasko on Thu Oct 5 9:33:02 EDT 2006.