Thanks in advance for the help I am about to receive! Amen.

I have two tables that have the same type data in them. One was used for one reason and the second was devevloped later by another group for a totally different reason (lack of communication). Without recreating the wheel. I need to know if it is possible to query the data so that I can take the results and chart them as a comparison of one table vs the other. I have tried joins but when Reason tables have different amounts of entries, the data is repeated instead of returning null or 0. Or it does not return all of Reason1 table when Reason2 does not have a record for that ID.

Sturcture is similar to following

Table1 ' done every time work is completed
TaskID
TaskType
ScheduledDate

Reason1Table ' may/may not have entry/task
TaskID ' relates back to table1
Activity
TimeUsed

Reason2Table ' may/may not have entry
Activity
TimeUsed

I am not sure if this will help you in any way, but I had a similar problem.

maybe if you read the reply I received it might help.

this was my posting on the 14-04-02

"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 was my reply from Suc6
"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


This union qry worked well for me.
thanks to Suc6

regards

Groundrush