Discerning unmatched query from multiple tables

[Kevin R]

I currently have an unmatched query running for multiple tables, however I would like to view which field(s) are different at a glance.

The background here is that I am trying to create a new database combining data from different departments and a separate company. With so many columns, when there is a typo or conflicting data in only one block I have the entire customer row flagged, resulting in searching for the differences by hand.

I am also looking for a better method to convert and equate the differences from the tables at the same time. One table has the full customer name, while the others split it up. Some tables have product codes while others have product names.

What is the best method to attack this project using Access 2007? Additionally time permitting I would like to create a Form frontend to choose if I want to overwrite existing customer data with data from tables imported from a different company?

[ADezii]

Initially, it appears as though you would have a real challenge on your hands, but first a couple of questions.

1. Does each Table have a Unique Field/Primary Key, and is the Field common to all Tables? I am looking for something like an EmployeeID based on an individual's SSAN, that exists in all the Tables.
2. Does each Table have the same number of Fields, and are you comparing Values in ALL of the Fields?
3. Does each Table contain the same Number of Records?
4. Posting some Sample Data, along with Table/Field Names wouold be very helpful.

[Kevin R]

1. Yes I do have the common field in all tables which are related.
2/3. No, none of the tables have the same number of column fields or rows of customers. While my focus is currently on 2 tables that I am trying to merge there is even another table that will need to flag (or delete) the customer in the single resultant table.

Field names from the seperate company does not match the field names in my company's tables. Also some of the data that I need to compare is abreviated in one table and not in a seperate table that need to be compared as well.

[Kevin R]

4. Here is the sample data:
Internal Table
SSN,FirstName,LastName,MiddleInit,Date,Description ,Code
123-456-789,John,Doe,M,11/16/2011,Pen,Accounting

External Table
"Company Code","Formatted SSN","Formatted Name","Date","Description"
"123456","123-456-789","DOE, JOHN MIDDLE",2011-11-16 12:00 AM,"P"

Delinquent Table
"Formatted SSN","Formatted Name","Delinquent Date","Reason","Company"
"123-456-789","DOE, JOHN MIDDLE",2011-01-19 10:17 AM,"Bad Check","123456"

Company Code 123456 is equal to only Accounting in this example.
When I import the External and Delinquent CSVs and set the Data Type for the Date as Date then it errors out and resultantly the Data Type has to be set as a text field.
There are more fields from more tables that I use to complete the final data table/query.
The Code for Pen could have multiple abbreviations from P to Pn or XYZ

[ADezii]

Kevin, I see no easy Method of Field comparisons across Tables that have different Field Names, Number of Records, etc. I do, however, have an idea but one more question. The Social Security Number exists in every Table, is Unique to each Table, and is stored exactly the same in every Table (XXX-XX-XXXX), is this correct?

[Kevin R]

Yes SSN is formatted exactly the same in all tables and is in every table.

[ADezii]

1. What Field would you like to do an initial comparison on, and what are some of the Values displayed in this Field? I'm thinking initially of something like comparing the [FirstName], [LastName], and [MiddleInit] Fields in the Internal Table to the [Formatted Name] Field in the External Table Linked by SSN.
2. Do you wish simply to perform the comparison, or change the manner in which the Field is displayed, such as parsing the [Formatted Name] Field in the External Table to [FirstName], [LastName], and [MiddleInit] Fields?

[sierra7]

Kevin
My 5 cents of advice is;

You have to plan these data migrations sytematically. They should be totally repeatable and this is particularly true if your source data is still in use and subject to change.

I create myself a new database called, say DataMigration.mdb. I would then import the table structure of your final application, without any data.
I then organise myself an unbound form, as a control panel, onto which I will place groups of command buttons. Each button fires off a query or series of queries, which have been build, tested, named and saved.

The first set of buttons might be to import the data from your standard operation. You will have to link to those tables so you may have tblCustomers and tblCustomers1 for instance. The append queries are very easily created in the query design grid, so this does not take up a lot of time.

You will also need a Reset button that deletes data out of your local tables so you can repeat the import operations easily.e.g.

Application.SetOption "Confirm Action Queries", False
DoCmd.RunSQL "DELETE * FROM tblCustomers;"
DoCmd.RunSQL "DELETE * FROM tblCustomerAddresses;"
DoCmd.RunSQL "DELETE * FROM tblSalesOrders;"
DoCmd.RunSQL "DELETE * FROM tblSalesOrderDetails;"
....
 

When you are importing data from your 'External' tables, the important thing is to leave the source data intact. Again, I would recommend that you start by bringing in the structure only, then create queries to append the data to your main tables. One advantage for doing this is that you can then add additional columns to the local table, which will assist in intermediate processing the data to your standard format.

For instance, you might want to run a query that expands 'P' to 'Pen', then a subsequent queries converting 'I' to 'Ink' or whatever. Also, Date-fields from DOS text systems may have different formats within the same column, and Access which is normally very good, may need more than one query to sort out all the options. It's easier to run sequences of saved named queries under a single command button than to try and create and debug a single query with nested IIF()'s.... no-one will ever know!

I also add text boxes that display number of records processed at various stages, so I can check all is running as expected.

Designing a Control Panel probably sounds like a lot more work but it ensures systematic processing and removes the risk of omitting any steps. When it's finished you can add a command button that calls each of the others in sequence, then sit back and enjoy!
It is also essential to reduce downtime when migrating live systems.
S7

[NeoPa]

I would say, as someone who's had a fair bit of experience in such things, that Sierra7 sounds like he has some too. Worthwhile advice and worth the extra 3 cents ;-)

[ADezii]

sierra7 does provide some excellent advice and insight into this long and tedious process. I was thinking more along the lines of keeping the original Source Data in tact, but just expanding it. A case in point would be the [Formatted Name] Field of the External Table which consists of the Last, First Name, Middle Initial/Name. I would then add 3 Fields to the External/Imported Table, namely: [First], [Last], [MI]. Next would be parsing the [Formatted Name] Field, then populating the [First], [Last], and [MI] Fields. I do realize that this approach is not foolproof, but it would break down/update the Source Data into some kind of Standardized Format, while still maintaining the Source Data. I like the idea of keeping the Source and Modified data under one roof. Make sense, or not?

[sierra7]

NeoPa,
Two cents was the charge when it was five bob to the dollar!
Things have changed. We must be business like now!
S7

[NeoPa]

Five bob to the dollar? You're talking a Crown in old money. If you're going back that far it should probably be more than five cents by now. Good advice anyway :-)

BTW Did I mention that ages you :-D (But I won't tell a soul.)

PS. Let me say here that my recommendations for Sierra's comment was in no way intended as any sort of criticism of ADezii's efforts, which I'm sure were also very pertinent. My comment was from a position of experience such that I know how many real 'bite you in the bum' situations can occur in the sort of migration project the OP was asking about and I felt that Sierra7 indicated he'd had similar experiences himself. Not many could be expected to have had such experience so ADezii's ideas can still be, and probably are, very helpful.

[Kevin R]

I'm sorry that I don't know SQL that well, so I don't know what the sample SQL provided above will do exactly. While right now I am running an unMatched query I can see that there is a change for the customer somewhere.
Is there any way of being able to generate a report that will highlight the differences of only the fields that have changed? How much more difficult would it be to have a form with data from 2 tables so that I see a pair of rows for each customer with red borders around the boxes that are different and a check box, drop down, or some way of manually specifying which value is good?
I am fine with comparing my internal live database to each external database one at a time to update the internal database. I am just trying to go about this in the most round about method or is this simple enough (with the limited knowledge I have) and most importantly viable as well?

[sierra7]

Hi
To arrange data side by side in a form to compare for differences is relatively simple using a Form/Sub_Form arrangement.

Clearly the two tables being compared must have a field common to both. It can have a different fieldname but hold the same data. It should be unique.

Create two seperate forms which display the fields to be compared. Other fields can be shown too if it helps you.

Open the form which you think represents the 'master' table in Design Mode and then add the second form to it as a Sub-Form. You will have to set the 'Link Master Form' and 'Link Child Form'settings to the appropriate joining fields.

I would then save and run the form and use the navigation control at the foot of the master form to check that the data is synchronized.

If all is OK, go back into Design mode and add somrething like the following code to the On_Current event of the sub-form.

Private Sub Form_Current()
 
If Trim(Me!Field1) <> Trim(Me.Parent!FLd1) Then
    Me.Field1.BackColor = vbRed
Else
    Me.Field1.BackColor = vbWhite
End If
 
If Me.Field2 <> Me.Parent.Fld2 Then
    Me.Field2.BackColor = vbRed
Else
    Me.Field2.BackColor = vbWhite
End If
 
If Me.Field3 <> Me.Parent.F3 Then
    Me.Field3.BackColor = vbRed
Else
    Me.Field3.BackColor = vbWhite
End If
 

This will change the BackColor of any field tested in the sub-form to Red, that does not match the equivalent field in the master form. Those fields are referenced by 'Parent' and their name in themain form; note their field named do not have to match.

Don't omit the 'Else' statements because the boxes will not automatically turn back to white without them.

When comparing string values it is sometimes necessary to use the Trim() function to remove unwanted leading or trailing spaces.

I hope this helps
S7

[sierra7]

Kevin,
I've re-read your post and it seems you may prefer to have the data presented in a report rather than a form.

The same method applies, create a main report and a sub-report. For the SUB-REPORT delete all the labels in headers & footers etc and set their heights to zero.

Instead of using the On_Current event (as required for a Form) select the Detail section of the report, then find the On_Paint event. Insert code like the following;

Private Sub Detail_Paint()
 
If Trim(Me!Field1) <> Trim(Me.Parent!Field1) Then
    Me.Field1.BorderColor = vbRed
Else
    Me.Field1.BorderColor = vbWhite
End If
 
If Me.Field2 <> Me.Parent.Field2 Then
    Me.Field2.BorderColor = vbRed
Else
    Me.Field2.BorderColor = vbWhite
End If
 
If Me.Field3 <> Me.Parent.Field3 Then
    Me.Field3.BorderColor = vbRed
Else
    Me.Field3.BorderColor = vbWhite
End If

Make sure the BorderStyle = Solid and Border Color = #FFFFFF (white) and then you will find that the fields which differ in the Sub-Report will have a Red border.

I will try and attach an example of the output and then post separately how you might adjust the data.
S7

[sierra7]

PROBLEM WITH LAST POST!
In my last post I suggested that the code should go into the On-Paint event. This is because I was using Access 2010 but I noticed that the red boxes sometimes appeared when they should not have, until I clicked on them!

This was unusual so I exported the demo to Access 2003 and put the code in the On_Format event and everything works fine.

I've read that Access 2010 does interactive reports but I did not take notice because for me, a Report is a piece of paper and anything interactive is a Form.

Back to school!
S7

[sierra7]

In my last post I reported that I had encountered a problem with the way the report formatted the data.

I have now found that the report works perfectly when called from a command button but exhibits some 'interactive' behaviour when opened by double clicking in the Database Window.

I'll raise this as a separate thread "Why does Report Open Interactively?" if I can't resolve the matter
S7

[NeoPa]

Please post a link in here in case I miss it S7.

It's fun watching you contradict yourself (I mean learn as you go) :-D

PS. It's also new to me so I'm not watching from ahead of you.