Table Compare

[juneau730]

Hello all,
I am working on an addition to the massive dBase I have, a query that compares two different tables, linked by a name field in each, and performing a datediff function.

For the most part I have this working. The datediff is operational, but the slight issue and question I have is this...

The name fields for each table in question are formatted differently, i.e. one will read
Snuffy, Joseph R. whereas the other is Snuffy,Joseph R

Without changing the format in the RAW data sets, is there a way to make the linked table compare? I have tried all three of the linked property settings and neither of them functions fully due to the name field formats

 

[Micron]

Just to be clear, those are not examples of different formats. It is the values that are different.
You have described one simple variation so answers can only be given based on that. If you use the Left and Instr functions together to find the location of the comma and take everything before it, you could try criteria that looks for matches based on what comes before that comma. Rather than suggest an expression, I'll wait to see if that is not the only variation. If you have others, then it becomes more difficult by some order of magnitude for every variation.

 

[stumac]

As Micron Says, it is unclear if the only difference is the additional space, assuming it is there are a few options.

The cleanest is to create a query (or 2) that changes the format of one or both of the name fields so that they match - i.e. take out the additional space and then use that query to join to the second table.

A second option would be to use an expression in a sub query, remove the join and use the In operator:

SELECT Table2.ID, Table2.NameField FROM Table2 WHERE Table2.NameField In (Select Replace(Table1.NameField,", ",",") from Table1)

alternatively you can have an expression in the join, however, it does mean that you can't edit the query using the designer (it will give an error), it has to be edited in SQL view (the example below is showing 2 tables linked by NameField with all spaces removed:

SELECT Table1.NameField, Table2.NameField FROM Table1 INNER JOIN Table2 ON Replace(Table1.NameField," ","") = Replace(Table2.NameField," ","")

 

[juneau730]

Though the names are fictitious, those are exact examples of the raw data format when output from the different sources.

 

[juneau730]

Just to be clear, those are not examples of different formats. It is the values that are different.
You have described one simple variation so answers can only be given based on that. If you use the Left and Instr functions together to find the location of the comma and take everything before it, you could try criteria that looks for matches based on what comes before that comma. Rather than suggest an expression, I'll wait to see if that is not the only variation. If you have others, then it becomes more difficult by some order of magnitude for every variation.

Though the names are fictitious names, the format shown are the exact format when the data is output from the pulled source.
I like the thought of matching only the data before the comma, unfortunately, I am not sure how that would work since we have users with the same last name.
I can script a find/replace function into one of the import steps, to correct the spacing before/after the comma.

 

[Micron]

Therein lies the rub with these sorts of issues. Almost always, the details are revealed one post at a time. What if you have Snuffy, JosephR in one table and Snuffy, Joseph R. in another? I presume you are stuck with linking on these 2 fields, because something more reliable would simplify things - like an employee number, phone number, etc.

 

[juneau730]

Therein lies the rub with these sorts of issues. Almost always, the details are revealed one post at a time. What if you have Snuffy, JosephR in one table and Snuffy, Joseph R. in another? I presume you are stuck with linking on these 2 fields, because something more reliable would simplify things - like an employee number, phone number, etc.

I understand what you are saying.. However, the data comes from the same sources each time it is pulled. The names themselves can/will change, as we are dealing with a 5000+ user domain. But,, the format of the output will not change.

We don't use a user/employee ID (that would simplify things immensely) and for what we are auditing, it has to be done by name.

 

[Micron]

I can script a find/replace function into one of the import steps, to correct the spacing before/after the comma.

That means that all values will have the same pattern, but some last names may be for different people? Then I might try adding the Mid function to the prior suggestion to see if what follows matches as well as what is to the left of the comma. However, I think the best approach would be to use these functions to parse the string into separate fields and join those instead. Then you could join 2 fields that contain Snuffy and 2 fields that contain Joseph R.