Hello, Please can anyone help


I am trying to compare data from 2 tables that should have the same information, and filter out the differences.

After running a “Unmatched Query” I am only left with unmatched results from the first fields i.e. (DJKL) from Table1, (DU1G) from Table 2,and(DHJ2)from both tables is eliminated because all fields are equal.

The problem is that each example has 4 fields that have to be equal and it is not picking up entries that has the same first Field but different amounts in field 2,3 and 4.

e.g.

TABLE 1
Job No B B1 B2
DHJ2 2 6 13
DJKL 5 1 6 DLE4 2 8 9

TABLE 2
Job No B B1 B2
DHJ2 2 6 13
DU1G 12 3 18
DLE4 7 5 4

The result that I am looking for after running the query is:

TABLE 1
Job No B B1 B2
DJKL 5 1 6
DLE4 2 8 9

TABLE 2
Job No B B1 B2
DU1G 12 3 18
DLE4 7 5 4


Any Ideas?

Thank you
Groundrush
Junior Member



[This message has been edited by Groundsrush (edited 04-09-2002).]

[This message has been edited by Groundsrush (edited 04-09-2002).]

Try this:

SELECT DISTINCTROW "Table1", tblJob1.Job, tblJob1.B, tblJob1.B1, tblJob1.B2
FROM tblJob1 LEFT JOIN tblJob2 ON (tblJob1.B2 = tblJob2.B2) AND (tblJob1.B1 = tblJob2.B1) AND (tblJob1.B = tblJob2.B) AND (tblJob1.Job = tblJob2.Job)
WHERE (((tblJob2.Job) Is Null))
UNION
SELECT DISTINCTROW "Table2", tblJob2.Job, tblJob2.B, tblJob2.B1, tblJob2.B2
FROM tblJob2 LEFT JOIN tblJob1 ON (tblJob2.B2 = tblJob1.B2) AND (tblJob2.B1 = tblJob1.B1) AND (tblJob2.B = tblJob1.B) AND (tblJob2.Job = tblJob1.Job)
WHERE (((tblJob1.Job) Is Null));

Suc6,

RV

Thank you
I will let you know how I get on.

Groundrush

Thanks Suc6

Appreciate your help, the Union query works well.

Groundrush