Table comparison problem

  • Thread starter Thread starter Michael Pierson
  • Start date Start date
M

Michael Pierson

I am comparing 2 cash receipts tables for the same time
period.
The tables are comprised of client,invoices and costs.
Table 1 has costs that total $1Million. Table 2 has costs
that total $900,000. The difference is $100,000

When I run the "Find Unmatched Query Wizard", my output
only totals $50,000 and not $100,000.

The "Is Null" criteria is only selecting those record
found in one table and not the other. The problem is that
table 1 may have a client cost of $100 and table 2 may
have involved a reversal and the client cost is now $90.
The "unmatched query wizard" will not help me find this
client with the $10 difference.

I have tried "Select, From, Where, Not Equal and a
multitude of other expressions and am not having any
luck. I need to identify the clients that have any and
all differences between the tables. I need to know exactly
what makes up the entire $100,000 difference. Any help
will be appreciated
 
Michel,

To do a true conparison of these two tables, you are going to have to use a
union query that will give you the effect of a Full Outer Join (all the
records from each table, linked on the appropriate fields (which is hard to
discern from your description). I'll assume that Client and Invoice are the
two fields in the primary key, although you may actuall have others. It
would look something like

SELECT "Table1 only" as Source, T1.Client, T1.Invoice, T1.Amount as T1Amt, 0
as T2Amt
FROM Table1 T1
LEFT JOIN Table2 T2
ON T1.Client = T2.Client
AND T1.Invoice = T2.Client
WHERE T2.Client ISNULL
UNION
SELECT "Table 2 only" as Source, T2.Client, T2.Invoice, 0 as T1Amt,
T2.Amount as T2Amt
FROM Table2 T2
LEFT JOIN Table1 T1
ON T2.Client = T1.Client
AND T2.Invoice = T1.Invoice
WHERE T1.Client ISNULL
UNION
SELECT "Both tables" as Source, T1.Client, T1.Invoice, T1.Amount as T1Amt,
T2.Amount as T2Amt
FROM Table1 T1
INNER JOIN Table2 T2
ON T1.Client = T2.Client
AND T1.Invoice = T2.Client

From here, you should be able to identify where the differences are in the
two tables between the two tables.

HTH
Dale
 
Back
Top