CODEWITHSUNDEEP
sqlsql-servervbams-accesscomparison
toolshed
toolshed

Reputation: 2029

Comparing two databases in MS Access: is it feasible and how?

I am attempting to compare two inventory lists; the current month vs. the previous month. This data is housed in two separate tables.

The two schemata are slightly different, one file uses double for numerical values, whereas the other employs decimal.

I would like to compare where new ItemIDs were added or removed in comparison with the data from the previous month, as well as the value changes.

Is something like this possible in Access, or would I be better suited to import the data into MS SQL Server?

Also, can this comparison be accomplished solely with queries, or am I required to write some VBA?

Upvotes: 0

Views: 550

Answers (2)

Fionnuala
Fionnuala

Reputation: 91376

MS Access is quite happy to compare decimal and double

SELECT "In 1" As Diff, Invent1.ID, Invent1.Amount, Invent2.Amount
FROM Invent1 LEFT JOIN Invent2 ON Invent1.ID = Invent2.ID
WHERE Invent2.ID Is Null

UNION
SELECT "In 2" As Diff, Invent2.ID, Invent1.Amount, Invent2.Amount
FROM Invent2 LEFT JOIN Invent1 ON Invent2.ID = Invent1.ID
WHERE Invent1.ID Is Null

UNION
SELECT "Amount" As Diff, Invent1.ID, Invent1.Amount, Invent2.Amount
FROM Invent1 INNER JOIN Invent2 ON Invent1.ID = Invent2.ID
WHERE [Invent1].[Amount]<>[Invent2].[Amount]

You might like to change the last union, depending on your data:

SELECT "Amount" As Diff, Invent1.ID, Invent1.Amount, Invent2.Amount
FROM Invent1 INNER JOIN Invent2 ON Invent1.ID = Invent2.ID
WHERE CCur([Invent1].[Amount])<>CCur([Invent2].[Amount])

Or better:

UNION SELECT "Amount" As Diff, Invent1.ID, Invent1.Amount, Invent2.Amount
FROM Invent1 INNER JOIN Invent2 ON Invent1.ID = Invent2.ID
WHERE CCur(Nz([Invent1].[Amount],0))<>CCur(Nz([Invent2].[Amount],0));

Upvotes: 1

Mike Cheel
Mike Cheel

Reputation: 13106

I would do something like:

SELECT
    t1.*,
    t2.*
FROM Table1 t1 
FULL OUTER JOIN Table2 t2 ON t2.Id = t1.Id
WHERE 1=1
AND t1.Field1 <> t2.Field1 
AND t1.Field2 <> t2.Field2
etc.

This will get you the raw data. Then you can add to the WHERE to filter against the NULLS or whatever else. Missing entries on the left table would indicate stuff that has been added. Missing entries on the right table (t2) will show what was removed.

I'm not sure if FULL OUTER JOINS are available in msaccess but they should be in the query designer: http://www.databasejournal.com/features/msaccess/article.php/3516561/Implementing-the-Equivalent-of-a-FULL-OUTER-JOIN-in-Microsoft-Access.htm

Upvotes: 0

Related Questions