Foreign key problem and linked drop downs from 2 diffrent databases into a third database.
I have 3 databases. 1. Business Info(anagraficaAzienda) it has address, phone, email, etc. 2. The Contact from the business(referente) it has telephone, email, fax, etc. 3. the work request(RichiestaLavoro). This is where all the data comes together. The anagraficaAzienda table is connect to the RichiestaLavoro by the Ragione Sociale. The RichiestaLavoro is connected to the referente by the cognome(last name) field. On the work request form when you select the Business name(Ragione Sociale) ,from a drop down thats is populated from the AnagraficAzienda(Business Info) tables' Ragione Sociale(business) field, it automatically pulls the data from the anagraficaAzienda table. Like i want. Also the when the cognome is selected from a drop down list, populated from the referente table, it too pulls data correctly. I see 2 problems, the Ragione Sociale and the last name are not always unique. I have created foreign keys(fk) and the do not pull the data unless i use the key fields specifically. It will be hard to remember all the fk's, and it is a waste of time to always have to look up the key every time. I am worried about errors that could arise. A business will have many requests. Most of the time it will be the same contact person, but if the person leaves, changes position, etc I have to give the operatores a way to store the new contact info and select it for the work request.
I would like to have the Cognome(last name) list populated dynamically after the Ragione Sociale is selected. I have put the Ragione Sociale field in the referente database (Contact person) which is populated from the AnagraficaAzienda(Business info) Ragione Sociale(Business name) field. I can dynamically link 3 fields from one db to another but i can not figure out how to dynamically link two fields from seperates databases in another database. I just can't wrap my head around how to link them.Thank you in advance
(I wrote it and I am not sure that i understand)