Select two records and autopopulate form

L

Lisa

I have a simple database for a home inspection business with three tables:
Clients, Jobs (one to many relationship of Clients to Jobs), and Real Estate
Agents.

I'm setting up the input form. All I now want to do is allow the user to
use a dropdown list to look up two separate Real Estate agents (one for the
Buyer and one for the Seller) and have their info populate the fields on the
form.

I have spent a lot of time on this, with different joins, different
autolookup queries, and separate tables for both Buyer and Seller agents.
Clearly I'm floundering and am at my wit's end. Please ... any help would be
greatly appreciated.
 
C

Chegu Tom

Lisa

I assume that thye Job file has a field for the BuyersAgent and one for the
SellersAgent

In the query design Open the Job table and TWO copies of the Agents Table.
Thw wizard will call it Agents_1

Link the BuyersAgent field in the Job file to one copy and Link the Sellers
Agent field to the other copy. Now you can add the fields you want from
both copies of the agents file

Tom
 
Joined
Mar 23, 2009
Messages
1
Reaction score
0
Hello Tom -

Thank you so much for your quick response. I did what you suggested but am still getting the ambiguous outer join error when trying to run the query. I have two right outer joins with the two copies of the Agents table, as I need to see all records in those tables.
 
L

Lisa

Hello Tom -

Thank you so much for your quick response. I did what you suggested but am
still getting the ambiguous outer join error when trying to run the query. I
have two right outer joins with the two copies of the Agents table, as I need
to see all records in those tables.
 
C

Chegu Tom

Lisa

Please Copy the SQL of your query and paste it in a message here. Maybe I
can see from that what you should be doing?
Does it work with inner joins (you would not see any records that lacked
either agent reference but it will help troubleshoot)?
 
L

Lisa

Tom -

Here it is.

SELECT Jobs.OrderID, Jobs.BuyerAgentID, Agents.ContactLastName,
Agents.ContactFirstName, Agents.CompanyName, Jobs.SellerAgentID,
Agents_1.ContactLastName, Agents_1.ContactFirstName, Agents_1.CompanyName
FROM Clients INNER JOIN (Agents AS Agents_1 LEFT JOIN (Jobs RIGHT JOIN
Agents ON Jobs.BuyerAgentID = Agents.AgentID) ON Agents_1.AgentID =
Jobs.SellerAgentID) ON Clients.ClientID = Jobs.ClientID;

Thank you.
 
L

Lisa

And yes, it does work with inner joins but of course cannot see agents with
no matching key in Jobs.
 
C

Chegu Tom

Lisa

Sorry for the late response. Been away for a week

Your SQL confuses me a little. It should be Two Left Joins (like this
example that joins two project tables to a Jobs table

FROM (Table1 LEFT JOIN Project ON Jobs.job1 = Project.ProjectID) LEFT JOIN
Project AS Project_1 ON Jobs.job2 = Project_1.ProjectID;

Try this

Join your Clients and Job tables in a Query (Inner Join?)

Then start a new query and join that query with two copies of your agent
table
 
L

Lisa

Tom -

I created a query first for just Clients and Jobs, creating an inner join.

Then I used that query in a new query with two copies of the Agents table.
I need to see all records in both Agents tables at all times. When I try to
do the join allowing me to do this (option 2 in the Join properties dialog
box), I get an error referencing "ambiguous outer joins".

Thanks for hanging in there with me.

Lisa
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top