conditional join

[Conditional join]

In Excel, I can do a conditional lookup by using an IF statement saying if
column A (table #1) = "football", lookup in table #2 and return matched
value, otherwise lookup column H (table #1) and return value from Table #2.

I'm trying to replicate in Access, but I don't know how. In access, I have
Table 1 joined to Table 2. As it is joined, it knows only to return values
that are matched by the joined field. Is it possible to code Access to do
the same as I described above where it knows to return values from Table #2
with the condition that if it doesn't match, use another field to match to
Table #2.

 

[BruceM]

Most likely yes, but you may need to be less abstract in describing the
situation. You wrote:
"it knows only to return values that are matched by the joined field"
What exactly is "it"?

I don't quite follow the Excel description, so I can't translate into
Access. I think you're talking about VLookup where Table #2 is the range
examined by the VLookup formula. Access has DLookup, which is quite a bit
different, but then so is Access from Excel.

In general terms, what real-world entity's data is stored in Table #1, and
what related data are stored in Table #2? Once that is described, give some
specific examples of what you hope to accomplish. Access is a program for
creating relational database applications, and can return any specified
value, but that specification can occur in a variety of ways, including
queries and VBA code.

 

[KARL DEWEY]

A LEFT JOIN will pull all records from left table and only those of the right
that match the left.

 

[Paul Shapiro]

You can't make the join conditional, but you can make the output column
conditional. You need to provide table structure for accurate help, but
maybe something like this:

Select
IIf(Table1.ColumnA="football", Table2.ColumnZ, Table1.ColumnH) as
OutputAA
From Table1 Inner Join Table2 On ...

If Table2 ONLY has matching rows for Table1 when Table1.ColumnA="football",
then you need to use an outer join. A left outer join will return all rows
from Table1, with matching rows for Table2 and nulls if there isn't any
matching row in Table2. Again, depending on details, it could be something
like:

Select
nz(Table2.ColumnZ, Table1.ColumnH) as OutputAA
From Table1 Left Join Table2 On ...

If Table2 doesn't match except when Table1=football, you can use the nz
function which returns Table2.ColumnZ if that value is not null, and
Table1.ColumnH if Table2.ColumnZ is null.