How to extract/ link data between 3 tables

[winxmun]

Hi All, I have tried to link the 3 tables below and thereafter to be populated into Report but was not successful. I was prompted with "the specified field could refer to more than one table in the from clause of your sql statement" when opening the Report.

May I know how to populate the correct LawyerAddress1 and LawyerAddress2 (into a report) based on either "LawyerID" in Customer table or "LawyerA" in Order table or "LawyerB" in Order table?

I used query to link Customer and Lawyer table, then LawyerAddress1 & LawyerAddress2 will be populated based on Customer.LawyerID which is the correct result.

I have another query to link Customer & Order & Lawyer table, then LawyerAddress1 & LawyerAddress2 should be populated based on either Customer.LawyerID or Order.LawyerA or Order.LawyerB. However the results for LawyerAddress1 & LawyerAddress2 will always follow Customer.LawyerID only. :think:


Table 1: Customer

Table 2: Order

Table 3: Lawyer



Field 1: CustID

Field 1: CustID

Field 1: LawyerID



Field 2: CustName

Field 2: OrderID

Field 2: LawyerAddress1



Field 3: LawyerID

Field 3: LawyerA

Field 3: LawyerAddress2




Field 4: LawyerB





Would appreciate any help!:yes Thank you in advance!

[HiTechCoach]

Part of the issue is there Table 2 has repeating fields. Table 2 fields 3 and 4 really should be in a sub/child table with one record for each lawyer.


The trick here is to add the Taqble 3: Lawyer multiple times. In your current design you will need it 3 times. One for each of the LawyerID foreign keys. When you add the same table multiple times access will add a _1, _2 etc to the end of the table name.