One table field referenced twice in query

[jakobt]

I have made one table with id, purchaser, seller,
Then I have a table called entities: id, entityname

The purchaser and seller fields reference an entity id

How can I setup a query where I now gets the purchasing entity name and the selling entity name.

Which relations do I need to setup.

 

[plog]

You didn't name your first table, so lets call it T1. In design view bring in T1, entities and entities again. That second entities table will appear as 'entities_1'. Link entities to T1.purchaser and link entities_1 to T1.seller. Bring down entity name from the entities table and make it look like this:

Purchaser: entityname

Then do the same for entities_1 and change it to this:

Seller: entityname

 

[jakobt]

This was exactly what I did. The problem is that in both two new fields ie. purchaser and seller the same value come up ie. the purchasing entity

 

[plog]

Can you post the SQL of your query or a screenshot of it in design view?

 

[jakobt]

please see attached

 

[plog]

That's exactly what it should be. Can you post a screen shot of the results and of the data in CaseFromTo?

 

[arnelgp]

use Right Join.

there should be only one field for Seller ID and Purchaser ID in table casesfrom, then add another field (possibly boolean) signalling if it is a seller or purchaser.