Multiple left joins

[KS]

I'm trying to develop a conditional join that will allow me lookup the data
in one field and if it doesn't match, it will look to another field to match.

I currently have the following setup:
I'm using a left join to include all records from "Table 1 Column A" and
only those records in "Table 2 Column A" where the joined fields are equal.

Here's what I'm looking to achieve:
If "Table 1 Column A" does not match "Table 2 Column A", look to join "Table
1 Column A" where the fields match in "Table 2 Column B"

 

[Jeff Boyce]

That is a bit complex ... can you describe the kind of data you have that
could have a potential matching value in one field some times and a
different field other times?

Often when what you're looking for could be in Column A or Column B (or
Column C, or ...), this is because the data is structured more like a
spreadsheet than a relational database.

More info, please...

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[KARL DEWEY]

In design view when you place a second instance of Table2 above the grid
Access adds a suffix to name it Table2_1.
Try this --
SELECT Table1.Column_A, IIf([Table2].[Column_A] Is
Null,[Table2_1].[Column_B],[Table2].[Column_A]) AS Match_Test
FROM (Table1 LEFT JOIN Table2 ON Table1.Column_A = Table2.ClientID) LEFT
JOIN Table2 AS Table2_1 ON Table1.Column_A = Table2_1.Column_B;

 

[david]

To design an updatable query plan, Access needs to
be able to uniquely identify which record in which table
will be updated when you update a recordset.

So the design you describe, where the join could be
on one field, or on another field, depending on the data,
will never be completely updatable in a plain JET query.

You can make both joins, and then select the data
from one join or from the other join, and either the
selected (calculated) data will not be updatable, or
the whole recordset will not be updatable, depending
on the nature of the relationship.

(david)