Relationship problem with Related Data source files
I have the following system setup:-
1) A Companies File - each company can have multiple addresses (head office, depot, sales, southern office, northern office etc)
- company table (companyID(PK), companyName)
- addresses table(AddressID(PK), CompanyID (FK)
2) A Sales File - a script runs that adds the companyID into the salestable to associate the sales with a company
What I want to be able to do (but cant) is as follows:-
When i create a Sales rder, I want to select the company, but then choose the address to invoice to
The address must belong to the company and come from the companies file that has an addresses table.
My Relationship Diagram is here which I think is wrong?!?!? http://www.assessors-unit.com/rd.jpg
Can anyone let me know how to set up the Sales orders file properly so that I can select the address (to invoice to) that belongs to the company associated with the sale.
Could you also indicate how I setup my Pop up menu to display the addresses available for the select company?