Data compare between three tables

[kryptkitten]

Hi All,

So I am working in Access 2000 (not a database I designed or have control over just one I have to pull data from and do QC on the data entry) and not sure what would be the best approach for doing a comparison of a field that is the same in three different tables. In other word I want to double check that the data is consistent between the three tables. What's the best way to do this?

Thanks!

Stacy

 

[MSAccessRookie]

Hi All,

So I am working in Access 2000 (not a database I designed or have control over just one I have to pull data from and do QC on the data entry) and not sure what would be the best approach for doing a comparison of a field that is the same in three different tables. In other word I want to double check that the data is consistent between the three tables. What's the best way to do this?

Thanks!

Stacy

EDITORIAL POINT:

I have a comment about the design (which I know you have no control over). If there is a single Field in three separate tables that serves the same purpose, such as the name of a Person, then there needs to be a table or People and Person related information, and each of these tables should not contain the name, but instead should contain the ID for the Person information from the other table. This would allow entry of a Person Name via a Combo Box, and eliminate the chance for errors in typing. Selection errors would still be possible, but I do not believe that is your issue.

END OF EDITORIAL POINT:

The only thing that you can do in the case that I described above is locate something in common between the Tables that is not the name of the Person, Join the Tables on that Field, and display all instances where the names do not match. You did not provide enough information regarding the structure of the Tables for me to provide anything more than theoretical instructions. If you are able to provide additional information, (Table structures will probably be enough), I would be more than glad to try to help you further.

NOTE:

The EDITORIAL POINT was provided for the sole reason of explaining the usefulness of another table in this case. I am aware that you said that you have no control over the Table or its structure, but perhaps there are others who might be interested in the explanation.

 

[Endre]

There are probably quite a few ways to do this, but the easiest is to write a Union Query on all 3 tables using the Primary Key Field and the Field you are checking. Then run a query on the union query where the count of the individual grouped values is greater than 1 i.e there is more than 1 value per indexed value in the union query, then relate that back to a union query to see the exact differences:

So step by step:

The 3 tables are called T_1, T_2, T_3. They are identical except that there are some discrepancies in the field DollarValue. The Primary key is the DateValue.

The following is a union query showing the Primary Key, the possible values in the rogue field, and an identifier holding the Table Number. This will show all values in the 3 tables. Save this as query Q2.
SELECT T_1.DateValue, T_1.DollarValue, "1" as FIL FROM T_1 UNION SELECT T_2.DateValue, T_2.DollarValue, "2" As FIL FROM T_2 UNION SELECT T_3.DateValue, T_3.DollarValue, "3" As FIL FROM T_3 ORDER BY DAteValue, FIL;

The following is a union query showing the Primary Key, and the possible values in the rogue field. This will show all unique values in the 3 tables. Any duplicates here will be the differences. Save this as query Q3.

SELECT T_1.DateValue, T_1.DollarValue FROM T_1 UNION SELECT T_2.DateValue, T_2.DollarValue FROM T_2 UNION SELECT T_3.DateValue, T_3.DollarValue FROM T_3;

Now write a query based on the Q3 query that shows where we have duplicate values:
SELECT Q3.DateValue FROM Q3 GROUP BY Q3.DateValue HAVING (((Count(Q3.DollarValue))>1));
This shows you the PrimaryKey values that have differences. Lets call this new Query Q4.

Finally we can match this back to the Union query Q2 so we can see the exact differences and the tables:
SELECT Q2.DateValue, Q2.FIL, Q2.DollarValue FROM Q4 INNER JOIN Q2 ON Q4.DateValue = Q2.DateValue ORDER BY Q2.DateValue, Q2.FIL;

These are your differences - It won't tell you which is correct, just that there are differences.

AFter changing or modifying your table data, just refresh the last query above to get the latest update. That's it. No data migration, copying, etc etc.

You can do this for any number of tables. If you are comparing more than 1 field either repeat the above for the new field, or consider concatenating all the fields you are checking into a single value and seeing if the concatenated values are different. It wont tell you which field has the problem, just the key figure - but this could save you time in wrtiting lots of union queries.

 

[kryptkitten]

MSAccessRookie,

I totally get where you are coming from and I completely agree. I also get that there could be others who would benefit from this as well. Believe me I was less than pleased when I saw how this database was set up and if it were mine it would not be set up the way that it is. That being said it is not relational and there are absolutely no primary keys set up either. Basically the database is the equivalent of a bunch of Excel spreadsheet with a lot of redundancy between the tables hence the reason I have to QC it quarterly. I already spoke to management about the need to modify that database, but it's not high on the priority list.

So the items I need to compare in each table are the name, product type, person number (this is not an auto number field it is an assigned number by the company). Not sure if this is enough information for you or not. If you need additional information just let me know.

Thanks!

Stacy

 

[kryptkitten]

Endre,

Well there is a problem in that they did not set up any primary keys. Not sure if maybe dumping these tables into another database and letting Access do an auto number would help. Thoughts on that?

Yeah not worried if it doesn't tell me what is correct. I just need the inconsistencies pointed out and then I will go back to the source document to verify what is correct and document what is incorrect in what tables and notify them to correct it.

Thanks!

Stacy

 

[MSAccessRookie]

Endre,

Well there is a problem in that they did not set up any primary keys. Not sure if maybe dumping these tables into another database and letting Access do an auto number would help. Thoughts on that?

Yeah not worried if it doesn't tell me what is correct. I just need the inconsistencies pointed out and then I will go back to the source document to verify what is correct and document what is incorrect in what tables and notify them to correct it.

Thanks!

Stacy

Take a look at the advice given by Endre. As long as each table has all three of the Fields in it, an approach of that type should work for you. Get back with any questions.

Note:

Endre's Solution could still work even if there are no Primary Keys. Just use the three common Fields in the First Union Statement and don't forget the Table Tag.

 

[Endre]

Autonumbering will only help if you need to find exactly where your final mis-match records exist in your database. Personally I LOATHE autonumbering and I NEVER use it. I'd rather you create a number field, populate the records 1,2,3,4,5 etc from copy/paste excel. Then convert it to a PrimaryKey and repeat for all 3 tables. However, if you regularly use autonumbering, then you can create one in the 3 tables and call it [AN].

I am usually quite good at picking up problems on this site - but I am still a little perplexed at what it is you are comparing to?

I am now guessing that you are looking at the combination of name, producttype, personnumber. If the concatenation of these is exactly the same in all 3 tables (and can occur anywhere) then there is no problem? This is a very dodgy thing to check for as you may get combinations that accidentally match and you wouldn't know. What if there are several "David's" - each one technically a different David?

However using the same precept as earlier here is a similar thing:

The following is a Union Query concatenating the 3 fields and showing the file number FIL and the autonumber [AN]. This shows All records from the 3 Tables. Call this Q2.

SELECT [AN], [Name] & [ProductType] & [PersonNumber] AS XChk, "1" as FIL FROM T_1 UNION SELECT [AN], [Name] & [ProductType] & [PersonNumber] AS XChk, "2" as FIL FROM T_2 UNION SELECT [AN], [Name] & [ProductType] & [PersonNumber] AS XChk, "3" as FIL FROM T_3;


Now we need to show where the count of the concatenated value grouped is less than the quantity of tables we are cross-checking i.e. < 3. So run a query on Q2 as follows and lest call it Q3:

SELECT Q2.XChk FROM Q2 GROUP BY Q2.XChk HAVING (((Count(Q2.FIL))<3));

Then match these back to Q2 to find the filenumber:
SELECT Q2.XChk, Q2.AN, Q2.FIL FROM Q3 INNER JOIN Q2 ON Q3.XChk = Q2.XChk ORDER BY Q2.XChk, Q2.FIL;

This is your final query that shows where we have a combination of name, producttype, and personnumber that exists in one table, but does not appear in that combination in either of the other two tables. Without the [AN] field we spoke about earlier (autonumber if you want to call it that) you may struggle to find your record.

What you do with these differences I have no idea as you don't know what your comparing to. It could be correct and the other two values incorrect. I guess that is why I have orderd the last query, Q4, by the concatenations first.

Finally, if each table holds the same quantity of records, but with differences, then your errors should come in "packs of 3" because if one combination is incorrect, then it so creates two incorrect combinations for the other tables.

 

[kryptkitten]

Hi Endre,

The compare is really just amongst the three tables for consistency since there is not on particular table that would have all the correct data. So for an example on what I am trying to do is checking to see if a person ID field is the same is entered the same in all three tables and if it's not then I need to know which isn't matching and go back to the source and see what's correct. Granted this does not show me if something was entered incorrectly across all three tables, but we will be performing an audit at a later date to verify that. There are basically three fields that I need to check consistency on which are the person ID, product type, and last name.

Not sure if that really clears things up much.

At any rate giving what you've written a shot and will let you know how that works out.

Appreciate the input from both you and MSAccessRookie.

Thanks!

Stacy