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

Josh S 26 Reputation points

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.

Microsoft Technologies based on the .NET software framework.
3,192 questions
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.
1,326 questions
An object-oriented and type-safe programming language that has its roots in the C family of languages and includes support for component-oriented programming.
9,980 questions
Access Development
Access Development
Access: A family of Microsoft relational database management systems designed for ease of use.Development: The process of researching, productizing, and refining new or existing technologies.
799 questions
{count} votes

3 answers

Sort by: Most helpful
  1. Olaf Helper 39,181 Reputation points

    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.

  2. Ken Sheridan 2,581 Reputation points

    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 in my public databases folder at:!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.

  3. Peter Doering 160 Reputation points MVP

    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.

    0 comments No comments