compare two tables

[03Explorer]

I have five table sets, one that was emailed out as a csv export and another is in an MDB locked (no one can enter or modify data set). I want to compare the two sets of tables to make sure / verify that the data IS exactly the same between the five table sets.

Example CSV's
table1.csv
table2.csv
table3.csv
table4.csv
table5.csv

and currently in locked MDB
table1
table2
table3
table4
table5

I imported the csv files with a suffix of todays date. Is it safe to create a query of unmatched data using the two key fields to like the tables and include all columns? and if I do and the result of the query does not show any rows that they contain the same data?

I am working on an urgent project and would like some assistance quickly if possible. Data needs to be validated and report generated today with the included data.


Much thanks for any help!

 

[jrbarnett]

The query of unmatched data will only show differences on the key fields used for matching.
Possibly the quickest way to compare the two data sets is to repeat the exports with the current locked database and compare the two files on disk using a diff tool (or the FC command as a basic measure).
If they match, they are the same, if not, they are different.

John

 

[03Explorer]

John,
Thank you for the information. I will still like to know how to do this, but the urgancy is not as high as it was earlier today.

I need something that will give detail on differences, if they exist.

Rob

 

[alicorn2]

If this is something you have to do frequently, you might want to investigate Red Gate's SQL bundle. They have various compare tools, one of which compares the data in two SQL databases. I don't know if that would help you with this particular problem, since your files are MDB and CSV, but it's possible... (If not, and if you have a SQL server, you could import the tables into two temporary databases and compare them there...???)

The way it works is you select your two databases, and it returns a list of tables. You select the ones you want to compare, and whether you're only interested in rows missing from database "A", rows missing from db "B", rows different, etc. (I usually select "all differences") and then it gives you a list of exactly what's different, and offers to make them match for you (you decide which one is going to be considered right, and the other one gets updated)

It also has tools for comparing table structures (which fields are missing/different) and I think other tools I haven't used, as well. I find it very handy when I am developing changes to an existing database -- I make my changes on the test server, and then use the tool to copy them to the live server.

 

[03Explorer]

Thank you for the suggestion. My current need is of the past, but I will look into it for future concerns. It sounds like what I could have used.

Good suggestion.