Query one field of one table in either of two fields of another table.

[marcchehab]

Dear Accessers.
This is my first post, I didn't find a solution to a problem I had online, but now figured it out myself. I thought I'd just post the solution, so that it's available. Maybe I didn't know the right words to find this too and the solution is obvious to everybody but me. Anyway, here it is.

The problem is the following: If you have ONE field (e.g. PERSONID) in one table that can occur in TWO fields of another table, and you want to query all hits, where PERSONID occurs in EITHER of the two fields in the other table, you cannot use a straight forward join, because Access links multiple joins between two tables with logical AND operators.

The obvious solution is: Use criteria instead of joins. However, I have a lot of data and it made my database VERY slow. In my query, joins are, literally, 100x faster.

So, how I did it is:

* Go to the SQL view
* Locate the Join (mine looks like this):

FROM COWAlliancesPerYear INNER JOIN COWCountryCode ON (COWAlliancesPerYear.ccode2 = COWCountryCode.CCode) AND (COWAlliancesPerYear.ccode1 = COWCountryCode.CCode);

* Change the AND to an OR:

FROM COWAlliancesPerYear INNER JOIN COWCountryCode ON (COWAlliancesPerYear.ccode2 = COWCountryCode.CCode) OR (COWAlliancesPerYear.ccode1 = COWCountryCode.CCode);

and enjoy.

Make sure you never go back to design view, it destroys the SQL apparently. Anybody knows how to prevent that?

kind regards, hope it helps somebody.