It seems you still have your Excel hat on.
The above is Excel Design.
In an Access table, you wouldn't have field names of "ANL", "PIL", Maersk" would you? You would have a field name "Carriers" and the DATA would be "ANL", "PIL", Maersk".
Same with Contracts. Would you have field names like "12345" or "abcde"? The field name might be "ContractName", with DATA like "12345" or "abcde".
tblPortsWould not have field names like "Hong Kong", "Sydney", "Singapoer", etc. The field name might be "PortName", with DATAlike "Hong Kong", "Sydney", "Singapoer".
So now the "FRT_Additionals_Table" table. If the cost codes are "20GP BAF", "40GP BAF", "40HC BAF", "20GP GRI", etc, do you think they should be field names? Or shiykd the field name be something like "CostCode"?
Arvil brought this up in his previous post as to why DATA should not be field names......
Using your Post #8, these are some tentative table designs.
Code:
tblCarriers
------------
CarrierID_PK (Autonumber)
CarrierName (Text)
tblContracts
-------------
ContractID_PK (Autonumber)
CarrierID_FK (Number - Long Int) (linked to tblCarriers, so that each carrier has their own tblContracts)
ContractTypeID_FK (Number - Long Int)
ContractName (Text)
tblContractTypes
----------------
ContractTypeID_PK (Autonumber)
ContractType (Text)
tblPorts
---------
PortID_PK (Autonumber)
ContractID_FK (Number - Long Int) (linked to tblContracts, so that each contract in each carrier has their own tblPorts)
PortName (Text)
PortType (Text) (Values = POL or POD)
tblCosts
----------
CostsID_PK (Autonumber)
POL_PortID_FK (Number - Long Int) (linked to tblPorts so that each portpair under each contract under each carrier has their own tblCosts)
POD_PortID_FK (Number - Long Int) (linked to tblPorts so that each portpair under each contract under each carrier has their own tblCosts)
CostCodeID_FK (Number - Long Int) (linked to tblCostCodes)
CostType (Text) (Values => FRT, Surcharge or Local )
CostValue (Number - Double)
ValidFrom (Date/Time)
ValidTo (Date/Time)
LocalCurrency (Text) (AUD, MYR, etc)
Notes (Text)
tblCostCodes
------------
CostCodeID_PK (Autonumber)
CostCode (Text) (Values => 20GP BAF, 40P BAF, 40HC BAF, 20GP GRI)
tblTransits
------------
PortPairID_PK (Autonumber)
TransitDays (Number - Integer)
Direct (????)
(linked to tblPorts, so that each portpair under each contract under each carrier has their own tblTransits) <<== I'mnot sure about this yet
Still need another table to bring together (collect) all of the Look Up TABLE selections,
maybe
tblShipping
===========
ShippingID_PK (Autonumber)
CarrierID_FK (Number - Long)
ContractID_FK (Number - Long)
ContractTypeID_FK (Number - Long)
POL_PortID_FK (Number - Long)
POD_PortID_FK (Number - Long)
CostsID_FK (Number - Long)
TransitID_FK (Number - Long)
OK, have to go put the thinking hat back on. I think it might needto go to the shop to be fixed.......