More than 15 left joins for matching rows

isladogs · Feb 14, 2019

@Jarek
The famous quote by Donald Rumsfeld (US Secretary of State under George Dubya Bush) may come in useful here:

As we know, there are known knowns; there are things we know we know.
We also know there are known unknowns; that is to say we know there are some things we do not know.
But there are also unknown unknowns – the ones we don't know we don't know.
And ... it is the latter category that tend to be the difficult ones.

Substitute nulls for 'knowns' in this context

You need to remember that these are artificial examples designed to prove functionality.
In order to check it works, we first need to ensure it correctly detects differences we know about
MajP & I started with two identical tables and modified these to get known differences as well as matching pairs of null fields
To keep a track of these changes, we manually populated the Differences column

In a real world example, such as the one you had at the start of this thread, you wouldn't know which fields are different or have matching nulls.
In fact you wouldn't have a Differences field!
If you did know the differences/matches, there would be absolutely no point using this code!

My added code detects the fields with matching nulls and populates that field

The buttons at the top of the form gives you 3 variations on the output of this code

All results

Unmatched records

Matching nulls

@MajP
Oops - I had included a few items from my students versions of this database
I've removed those items & fixed a couple of related code lines in the attached update

However I have just found a rather odd glitch in matched null detection using a different dataset. Will investigate further and report back

jaryszek · Feb 14, 2019

Hi,

thank you. But already in MajP code there was mechanism to get matched from nulls fields also?

Look please at this record:

this is not match and in your example it is match...

Best,
Jacek

isladogs · Feb 14, 2019

I think you may have missed the point....as I did originally

Frank Gibson is a non-match as one field is different - the uncropped version of my screenshot shows that clearly

If the records have any differences (field values different or null in one table), there is no matching record detected.

The problem in standard table matching is that a null value is not the same as anything else ...not even another null.
Therefore normally two records with all fields identical and a pair of 'matching nulls' would NOT be detected as identical
See methods 5 & 6 in the 2nd part of my Synchronising Data article

The main point of this code is that it allows for those matching null pairs.
I thought it helpful to show these - if you don't agree, just omit that part of the code

jaryszek · Feb 14, 2019

Ok, i agree

thank you Colin, now i understand. But MajP code also was catching nulls fields.
And it was working if i have pair of nulls, i tested id.

Best,
Jacek

isladogs · Feb 14, 2019

Yes I agree...but the original code won't show you which fields have matching nulls. Remember you wouldn't have the Differences column in real life
As already written, I thought it useful to see that info. You may not agree.

jaryszek · Feb 14, 2019

hi Colin,

thank you. I agree with you

Best,
Jacek

MajP · Feb 14, 2019

Code:

  'Replace code
  strNullMatch = ""
  For Each fld In rsSearch.Fields
    If IsNull(fld.Value) Then
      prmField = "[_" & fld.Name & "]"
      newSql = Replace(newSql, "=" & prmField, "is Null")
      If strNullMatch = "" Then
         strNullMatch = prmField
      Else
        strNullMatch = strNullMatch & "; " & prmField
      End If
    End If
  Next fld
  'For Each prm In prms
     '   blnNull = False
        'Unfortunately need to run this twice
        'all values lost after resetting qdf.sql
       ' prmField = Replace(prm.Name, "[", "")
       ' prmField = Replace(prmField, "]", "")
       ' prmField = Mid(prmField, 2)
       ' prm.Value = rsSearch.Fields(prmField).Value
        'change the sql string
        'If IsNull(prm.Value) Then
        '    newSql = Replace(newSql, "=" & prm.Name, "is Null")
         '   blnNull = True
            'save matching null fields (Colin Riddington)
        '    If strNullMatch = "" Then
        '        strNullMatch = prmField
        '    Else
        '        strNullMatch = strNullMatch & "; " & prmField
        '    End If
        'End If
  'Next prm

Colin,
In procedure IsMatch the following code can be simplified. I was looping the parameters and setting their values, just to determine if the field was null. I already had the answer if the field was null and the name of the parameter has to be the field name [_FieldName]. Does not change anything, but it is simpler for anyone reading the code.

isladogs · Feb 14, 2019

As mentioned in our PM exchange:

1. That simplified code is definitely better - further slight modification to use the field name rather than parameter name. Also need to exclude Differences column:

Code:

  For Each fld In rsSearch.Fields
        If fld.Name <> "Differences" Then 'only needed for testing purposes
                If IsNull(fld.Value) Then
                  prmField = "[_" & fld.Name & "]"
                  newSQL = Replace(newSQL, "=" & prmField, "is Null")
                  If strNullMatch = "" Then
                        strNullMatch = fld.Name ' prmField
                  Else
                        strNullMatch = strNullMatch & "; " & fld.Name ' prmField
                  End If
                End If
        End If
  Next fld

2. I had a problem using my datasets where matching nulls in the final field were not being detected. This resulted in those records being no matches.
I tried swopping the field order (FIXED) and swopping back (ALSO FIXED?)
Either way that or the new code above seems to have solved it ... at least for now

For anyone interested, attached are 2 more example datasets used in testing
These include hyperlink & boolean fields as well as text/date/number
Haven't yet tested currency but that should be fine as its just a number datatype.

Don't see any point testing any of the 'horrible' datatypes like MVFs or attachments

MajP · Feb 27, 2019

Here is an updated demo. It has a few new concepts. It is a work in progress and a long ways to a user friendly application

1) Allows you to choose fields to map between table 1 and 2. Assists in mapping like named fields.
2) Allows you to persist the mapping for any number of tables
3) Does the heavy lifting on building the matching query. This previously required the user to manually add parameters correctly named to each field.

I noticed some strange behavior with the original data so I may have broken something. If interested import your tables and give it a try.

isladogs · Feb 27, 2019

Thanks. I'll have a look over the next couple of days & let you know how it works for my own test data

More than 15 left joins for matching rows

isladogs

MVP / VIP

Attachments

jaryszek

Registered User.

Attachments

isladogs

MVP / VIP

jaryszek

Registered User.

isladogs

MVP / VIP

jaryszek

Registered User.

MajP

You've got your good things, and you've got mine.

isladogs

MVP / VIP

Attachments

MajP

You've got your good things, and you've got mine.

Attachments

isladogs

MVP / VIP

Similar threads

Users who are viewing this thread