Union and Minus equivalents

[dixie09]

Hi,
I need to perform a quality control check between an original report and the same report produced by a new process. Specifically i want to check that both versions of the report are identical across all columns and rows. The original report is held in a table "OR1" and the new report is in table "NR1".

In Oracle I would use:

(SELECT * -- list all rows in original report but not in new report
FROM OR1
MINUS
SELECT *
FROM NR1)
UNION ALL -- appended to
(SELECT * -- all rows in new report but not in original report
FROM NR1
MINUS
SELECT *
FROM OR1)

There will be zero rows output if both tables are identical.

How do I perform this check using Access 2002? (Please note that I need to check all 50 columns and not just the primary key.)

[dixie09]

Found the answer to my own question on "Jeff's sql server blog":

SELECT MIN(TableName) as TableName, ID, COL1, COL2, COL3 ...
FROM
(
SELECT 'Table A' as TableName, A.ID, A.COL1, A.COL2, A.COL3, ...
FROM A
UNION ALL
SELECT 'Table B' as TableName, B.ID, B.COL1, B.COl2, B.COL3, ...
FROM B
) tmp
GROUP BY ID, COL1, COL2, COL3 ...
HAVING COUNT(*) = 1
ORDER BY ID

The inner "tmp" query needs to be handled as its own query.