query unmatched fields in microsoft access

Question

Totally newbie to Access:

2 Almost identical tables (table 1 and table 2). Field 1 acting as primary key(PK) for both.

I would like to run a query and return which fields do not match in table for that specific primary key.

Example:

PK1 Field1, Field2 unmatched

PK26 Field3, Field7 unmatched

I tried using the wizard and it works, but it doesn't identify the particular field that didn't match.

Answer 1

You say you've already created a query which returns the primary key columns of rows with mismatches.  If you join that query to both tables to return all columns from each table and create a report as I and John have described, returning two rows per key value, and highlighting the mismatches by means of conditional formatting, then, if we assume 12 pairs of rows per page and 400 rows with mismatches are returned, that's only 34 pages to scan.  Provided that you select a prominent colour for the mismatches, they'll stick out like sore thumbs as you scan the pages, so it's not going to be a particularly onerous task to identify the culprits.

As I said earlier, you can return a result table like that which you described, but it needs a knowledge of VBA more than of SQL as the hard work will be done in the function called by the query.  If you can post a file with cut down samples of your two tables, say 100 rows each with 10 or so mismatched rows, to a file sharing site such as OneDrive, and post a link to it here I'd be happy to have a crack at it.

If you'd prefer not to put a file in the public domain you can mail it to me at the following munged address: 

kenwsheridan<at>yahoo<dot>co<dot>uk

Answer 2

I think this can be done using Conditional Formatting in a Report. Create a Query joining the two tables on the PK, with criteria on the other fields to find mismatches; use OR logic so a record will be retrieved if any field is mismatched. You can then display the query on a Report, using the Conditional Formatting feature with an expression on each field, to display the matches in black and white and the mismatches in (say) green on red background.

Answer 3

I'm not sure that a query to do this is a task for a complete newcomer to working with MS Access.  You won't do it with a simple query.  It could be done by calling a function in a query, whose code loops through the first table's columns (fields) per primary key value, and within that loop examines the second table's corresponding column, incrementally building a string expression as the function's return value where the values do not match.  This is not too difficult if you understand how to work with recordsets in code, but is not trivial and might be outside the comfort zone of someone new to the subject.

Another way of presenting the mismatches would be to do so in a report.  The report would be based on a query which joins the two tables on the primary key columns, returning all columns from each table.  The values could then be presented in the report's detail section as two rows, with the corresponding columns from each table vertically aligned with each other.  Conditional formatting could then be used to change the ForeColor and/or BackColor of each control if its value differed from the corresponding one above/below it.  The report would show all values for all columns in both tables, but where the values differ these would be readily apparent by virtue of their different colour.  Such a query and report would be very easy to create, and no code would be required. It would merely require you to build the simple non-equality expressions for the conditional formatting of each control.

Thank you Ken, even though I am familiar with algorithms and coding. My experience is with Java and XHTML, whereas Access uses SQL. That's just to much work for a side project at work. I just wanted to confirm with some others that what I was hoping to accomplish, could not be simply achieved. I apologies for the lack of information I provided in my example; it sounded better in my head. 

At work, we have 2 databases that don't communicate with each other, and are maintained by 2 different departments. So there are some errors when inputting the data. The necessary data that requires correction can be exported in to excel, but with over 1800 rows and 9 columns it can become time consuming pinpointing the errors. It was expected that locating the unmatched values could be done rather quick using Microsoft access. Like I previously stated, I was able to create a query to return the primary keys that have unmatched values, but the unmatched values could occur in any of the nine fields. So the query will return roughly 400 PKs with all 9 fields. 400 is much better to work with than 1800, but it would be nice if I could pinpoint the exact field for the PK that had the error.

Answer 4

I'm not sure that a query to do this is a task for a complete newcomer to working with MS Access.  You won't do it with a simple query.  It could be done by calling a function in a query, whose code loops through the first table's columns (fields) per primary key value, and within that loop examines the second table's corresponding column, incrementally building a string expression as the function's return value where the values do not match.  This is not too difficult if you understand how to work with recordsets in code, but is not trivial and might be outside the comfort zone of someone new to the subject.

Another way of presenting the mismatches would be to do so in a report.  The report would be based on a query which joins the two tables on the primary key columns, returning all columns from each table.  The values could then be presented in the report's detail section as two rows, with the corresponding columns from each table vertically aligned with each other.  Conditional formatting could then be used to change the ForeColor and/or BackColor of each control if its value differed from the corresponding one above/below it.  The report would show all values for all columns in both tables, but where the values differ these would be readily apparent by virtue of their different colour.  Such a query and report would be very easy to create, and no code would be required. It would merely require you to build the simple non-equality expressions for the conditional formatting of each control.

Answer 5

Your example is not understandable.

Post examples of data in your fields with the datatype and field names.