How do I compare two access database tables, both the table has same rows and columns ?

Question

I have two access database tables. Table A Account #, Name, Addres, City, State, Balance, Purchase, Order, Year, TotalCost Table B. Account #, Name, Addres, City, State, Balance, Purchase, Order, Year, TotalCost My purpose is to compare Table A and Tables B, to verify both table has exact same data in each row and each columns. Should I change table to excel sheet and would that help? If so then how I compare two sheet in Excel to get my query done. Your help will be greatly appreciated, thank you very much.

Answer 1

You can "link" table from one MS Access fileto the other (or create a separate with links to both), then you can compare the data with SQL. https://support.microsoft.com/en-us/office/import-or-link-to-data-in-another-access-database-095ab408-89c7-45b3-aac2-58036e45fcf6

Answer 2

You can compare two tables in a number of ways, e.g. by using the NOT EXISTS predicate: SELECT * FROM TableA WHERE NOT EXISTS (SELECT * FROM TableB WHERE TableB.[Account #] = TableA.[Account #] AND TableB.Name = TableA.Name AND TableB.Address = TableA.Address …and so on to AND TableB.TotalCost = TableA.TotalCost); This will return all rows from TableA which don't have an exact match in TableB. You'd then do the same, but with the table names reversed to return all rows from TableB which don't have an exact match in TableA. The tables generated by the external systems are badly non-normalized, as I'm sure you've realised. If you are going to work with the data in Access they should be decomposed into a set of normalized tables. For an illustration of how this can be done take a look at DemposerDemo.zip in my public databases folder at: https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169 This little demo file. Imports data from Excel and then decomposes it by executing a set of INSERT INTO statements in a specific order. A brief description is given at each stage of the process as you step through the demo. Note, BTW, that keywords such as Name should be avoided as object names. The inclusion of spaces or special character such as the # sign in object names should also be avoided, as they can cause notational problems. Use CamelCase or represent a space by an underscore character like_this.

Answer 3

Ken's answer describes rows that are available in one table but missing in the other, and vice versa. If you want to compare rows that are in both tables you can do as follows (after you've linked them as described in Olaf's answer): SELECT A.[Account #], A.[City], B.[City] FROM [Table A] AS A INNER JOIN [Table B] AS B ON A.[Account #]=B.[Account #] WHERE A.City<>B.City OR (A.City IS NULL AND B.City IS NOT NULL) OR (B.City IS NULL AND A.City IS NOT NULL) Repeat this statement for all fields.