Use a Union query to link two tables via a third intermediate table? 2

[VBAjedi]

I have three tables - I'll call them TableA, TableB, and Corrections (the actual names and data would take too long to explain). TableA contains handkeyed data, including a "TableBValue1" field which is a foreign key used to link to TableB.

The problem is that the data entry folks consistently miskey certain "TableBValue1" entries (and I can't correct that root issue at this time). So I have a Corrections table with two fields: "MiskeyedTableBValue1" and "CorrectTableBValue1". Kind of like an AutoCorrect table.

QUESTION: How do I now write a query to link TableA to TableB on that "TableBValue1" field, but checking the Corrections table first? I thought about trying to write a Union query to join those TableA records with a "TableBValue1" match in Corrections to those TableA records with no match, then link that composite table to TableB. But I'm not sure how to do that...

VBAjedi

 

[PHV]

what about something like this ?
SELECT *
FROM TableB B, TableA A
LEFT JOIN Corrections C ON A.TableBValue1=C.MiskeyedTableBValue1)
WHERE Nz(C.CorrectTableBValue1,A.TableBValue1)=B.Value1

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[VBAjedi]

Wow, that looks brilliantly simple! I wasn't expecting a possible solution to this to be that clean/minimalist.

That said, I'm getting a "Syntax error in JOIN operation" error. I'm sure it's something simple. Is the missing left parentheses in your example a possible reason for this? I tried deleting the right parentheses mark at the end of the LEFT JOIN line and otherwise monkeyed around with tweaking your example to see if I could isolate the error.

Removing the WHERE clause for the moment, this executes:

SELECT *
FROM TableB B, TableA A

And this executes as well:

SELECT *
FROM TableA A
LEFT JOIN Corrections C ON A.TableBValue1=C.MiskeyedTableBValue1

But putting them together like you show generates that error:

SELECT *
FROM TableB B, TableA A
LEFT JOIN Corrections C ON A.TableBValue1=C.MiskeyedTableBValue1

Any thoughts?

VBAjedi

 

[lameid]

Likely the Access query parser is being difficult. Try...

SELECT *
FROM (TableA A
LEFT JOIN Corrections C ON A.TableBValue1=C.MiskeyedTableBValue1)
 , TableB B
WHERE Nz(C.CorrectTableBValue1,A.TableBValue1)=B.Value1

 

[VBAjedi]

lameid,

That didn't work either. Got a "Join expression not supported" error. It occurred to me that I forgot to mention that I'm working in Access 2003. Thinking that maybe this kind of join wasn't supported back then, I tried some workarounds and discovered a two-query approach that appears to work.

The first query is named TableAandB:

SELECT *
FROM TableA, TableB

The second query is:

SELECT *
FROM TableAandB as A
LEFT JOIN Corrections C ON A.TableBValue1=C.MiskeyedTableBValue1)
WHERE Nz(C.CorrectTableBValue1,A.TableBValue1)=A.BValue1

I'd still like to find out if there's a syntax adjustment that would allow this to happen in a single query in Access 2003, but this is an acceptable solution. Stars for both of your help!

VBAjedi

 

[lameid]

You don't need a query object as a sub query but I prefer them.

This also should work...

SELECT *
From
(SELECT * FROM TableA, TableB)A
LEFT JOIN Corrections C ON A.TableBValue1=C.MiskeyedTableBValue1)WHERE Nz(C.CorrectTableBValue1,A.TableBValue1)=A.BValue1

Although, I would do the Left Join first as the sub-query for performance reasons. Unless of course reality proves me wrong.

 

[VBAjedi]

Both good thoughts. I came to the same conclusion on doing the left join first - much faster and doesn't generate a subquery result set in the millions of rows.

Thanks!

VBAjedi