I'm a little confused by your table structure. Could you please explain what process you are trying to model? Can you describe the relationships in the model
It appears that your are trying to model pick ups and deliveries of items for customers. It also appears that a customer can have many addresses (their account address and their collection point address and possibly a billing address). Since the delivery points are also addresses, I would think it would be best to have a table of addresses. Why do you have fields for address, street and road in your tables?
tblAddresses
-pkAddressID primary key, autonumber
-txtAddress
-txtTown
-txtCounty
A table to hold company names
tblCompanies
-pkCompanyID primary key, autonumber
-txtCompanyName
-txtAcctNumber
A table to hold the addresses for a company
tblCompanyAddresses
-pkCoAddressID primary key, autonumber
-fkCompanyID foreign key to tblCompanies
-fkAddressID foreign key to tblAddresses
Now a couple of tables to hold your transactions and the pickups & deliveries involved in those transactions
tblTransactions
-pkTransID primary key, autonumber
-fkCoAddressID foreign key to tblCompanyAddresses (this might be tied to the billing address of the company for which the transaction is being conducted)
-dteTrans (transaction date)
A transaction can have many details (collections and deliveries) each with their associated address
tblTransDetail
-pkTransDetailID primary key, autonumber
-fkTransID foreign key to tblTransactions
-fkAddressID foreign key to tblAddresses
-fkTransType foreign key to tblTansType
tblTransType (a transaction type can be a collection or a delivery)
-pkTransTypeID primary key, autonumber
-txtTransType
If your description table describes the details of the transaction then it belongs in tblTransaction. If the details are specific to one of the transaction details, then it belongs in that table. I'm guessing that the description information is for the transaction. If this is indeed the case then your invoice table would be this
tblInvoice
-pkInvoiceID primary key, autonumber
-fkTransID foreign key to tblTransactions
-txtInvoiceNo
-dteInvoice (Invoice date, if no date then transaction has not been invoiced)
