substituting a field for a text field

[Cosette]

Good morning all,

I have a table of sales(tblSales) with a buyer agent field and a seller agent field. I created a combo box in that form (frmSales)to find the agent's name at time of input - teh records store accurately the agent ID (number bield). Now, I am querying to get the names of the agents back and I am stuck. My table Agent (tblAgent) has Agent ID and agent name. How do I get to display two columns, one for buyer and one for seller, coming from the same source table, displaying names and not numbers?

Thanks

David

 

[lupins46]

Are the Agent names held in a table?

 

[PHV]

You have to use 2 aliased instantiations of tblAgent:
SELECT A.buyerID, B.Name, A.sellerID, S.Name, ...
FROM (tblSales AS A
INNER JOIN tblAgent AS B ON A.buyerID = B.agentID)
INNER JOIN tblAgent AS S ON A.sellerID = S.agentID
...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Cosette]

Yes the agents names are held in table agent. Since I didn't want to dplicate the info and create two tables with the same info, I thought to do it that way was the 'correct' design. Am I wrong? Should I duplicate the table and give it another name?

PHV, you gave me SQL code. I assume this means that I cannot achieve this with the query graphical interface?

Thanks you both for your replies.

David

 

[lupins46]

Sorry - I didn't read enough of the Q properly.

You can just add the same table twice to the query grid and create sparate joins from the main table.

 

[Cosette]

Thank you very much, I never thought of that.

David