dcsimg
Results 1 to 2 of 2

Thread: Union and Minus equivalents

  1. #1
    Join Date
    Nov 2009
    Posts
    6

    Union and Minus equivalents

    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.)

  2. #2
    Join Date
    Nov 2009
    Posts
    6
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •