Multiple inner joins question

[Macsterling]

I am trying to set up a file compare process. I load the two files into two "matching" tables (there is a key field). I have a series of queries which find any differences in the various fields and displays them. My problem is that one field (Field4 - a tran code) is coded 1,2,3 in one file and A,B,C in the other. I have set up a third translation table with two columns, each row showing the matching codes (1/A, 2/B, 3/C, etc). I want to find corresponding rows in FILE1 and FILE2 (matched on KEY) where tran codes (Field4) do not match (ie a "1" in File1 but NOT a "A" in FILE2).

I have not been able to get the two inner joins to work. Can some one help.

This is my last attempt:


FILE1: KEY-Field1-Field2-Field3-Field4
FILE2: KEY-Field1-Field2-Field3-Field4
(Field4 is the code that needs to be translated)

FILE3/numeric/alphabetic
1 A
2 B
3 C
4 D


SELECT FILE1.KEY,FILE1.Field4, FILE2.Field4
FROM
FILE1 INNER JOIN FILE2 ON FILE1.key = FILE2.key
INNER JOIN [FILE3] ON (FILE1.Field4 = FILE3.alphabetic)
WHERE FILE2.Field4 <> FILE3.numeric.

Hope this makes sense.

Mac

 

[namliam]

Looks to me like your query should be working....

 

[Macsterling]

I am getting a syntax error (missing operand) in the expression ON FILE1.key = FILE2.key INNER JOIN [FILE3] ON (FILE1.Field4 = FILE3.alphabetic) The cursor goes to the first line Field4 part of the FILE1.Field4 parameter.

I am looking for a missing ( or "." or something. The REAL code is below -cut and pasted (I changed some of the names to make it simpler to explain)

MAS is FILE1
IVT is FILE2
[099 65-4 HISTORY - TRAN CODE] is FILE3
Field1 , 2, 3, 4 are the real field names
MAS.TRAN is the 'numeric' in FILE3
IVT.TRAN is the 'alphabetic' in FILE3

SELECT MAS.KEY, MAS.Field4, IVT.Field4
FROM
MAS INNER JOIN IVT ON MAS.key = IVT.key
INNER JOIN [099 65-4 HISTORY - TRAN CODE] ON (MAS.Field4 = [099 65-4 HISTORY - TRAN CODE].[MAS TRAN])
WHERE IVT.Field4 <> [099 65-4 HISTORY - TRAN CODE].[IVT TRAN];

 

[Macsterling]

solution

I took out the joins and it worked first time - trying to make it too complicated!!