A tale of two tables

[rcappell]

Let me start by apologizing for bothering you because I am sure someone asked this before but I can't find the post and I have been working on this for 2 days and I really need to get my own work done.

I have two tables with the same fields: PART, DESC, REQ, CHASSIS, SEQ, REPORT DATE. And one table with the unique field CHASSIS this is the one to the others Many.

One table is DrivelineMain the other is a TempTable with the new data.

I have done an update query to update DrivelineMain with any new records in DrivlineTemp. but what I need to do is report the differences in PART numbers between the two tables for CHASSIS. I would need to list the old part number in DrivelineMain and the new part number in DrivelineTemp.

I would only want to display the first two rows because there are differences.

Please assist if possible or point me in an appropriate direction.

 

[mhartman]

Hello
Run the query titled chassis in your DB and see if this does what your asking.

Regards

 

[rcappell]

Nope sorry that was the first thing I did but you will notice that it delivers multiples and if you try to group it you lose data. if you look at the query named TEST1 you will see soemthing that looks like it works but if you glance at records 9,10,11,12 an issue appears. For some reason because of the sort it lines up the part numbers differently and you get a false positive.

 

[mhartman]

Hello

Try test2 query.

Regards
Mark

 

[rcappell]

Same results at TEST1.

 

[rcappell]

I found the solution to my own question so I am posting it for anytong else who might be intersted:

SELECT tmp.COL1, tmp.COL2, tmp.COL3, tmp.COL4
FROM [SELECT TestA.COL1,TestA.COL2, TestA.COL3, TestA.COL4
FROM TestA WHERE COL1 IN (SELECT COL1 FROM TestB)
UNION ALL
SELECT TestB.COL1,TestB.COL2,TestB.COL3, TestB.COL4
FROM TestB
]. AS tmp
GROUP BY tmp.COL1, tmp.COL2, tmp.COL3, tmp.COL4
HAVING (((Count(*))=1))
ORDER BY tmp.COL2, tmp.COL3;