Help for Access VBScript compare 2 db with partial ID

[chungiemo]

Hi

thought I would do another thread as this one is a bit different from the previous problem

I am looking for a solution to the relating problem

Comparing 2 access databases with 2 tables, they are non-related tables, but should have been related by the ID, and reason for this was due to the poor data entry standards. I am trying to compare both databases with a Partial ID in Table 1 and match it to the Full ID in Table 2 with the first and lastname and date of birth.

Note the poor data entry standards are not consistent i.e. all records come with a prefix of 3 chars i.e. “WIG”, records are 9 or 10 string char, and some users have included the first 6 numbers, or last 6 numbers, or from the 2nd number to the last etc.

I have tried the simple "Like" operator in SQL but it does not like matching to another table but instead specified criteria must be entered

Example of Tables

Table 1
Partial ID: Firstname: Lastname:
WIG ASIF ANWAR
WIG1003546 PAMELA KNOX
WIG6408236 Scott Winton
WIG737976 SARAH NEVANS
WIS5134838 Gordon Mcdowall

Table 2
Partial ID: Firstname: Lastname:
WIG ASIF ANWAR
51003546E PAMELA KNOX
64082365R Scott Winton
50737976X SARAH NEVANS
51348384L Gordon Mcdowall

I would like to have 3 new fields of Partial Status, Full Record, Count in Table 1

Partial Status (result of search)
"No match found" or "Match found"

Full Record (If found display full record ID)
WIG ID: 51003546E

Count (count the no of occurrences of match to highlight discrepancies)
The no of matches found: 1

what i believe the solution is.....

Dim Partial ID As String
Dim Count As Integer
Partial ID = Table1.ID
Dim Partialfound As Boolean = False
Count = 0
For i As Integer = 0 To (table2.Rows.Count - 1)
If CStr(table2.Rows(i)("ID").ToString.ToUpper) Like "*" & Partial ID.ToUpper & "*" Then
accessionfound = True
rowIndex = i
Table1.Partial Status ("Found ID")
Table2.AppendText(table2.Rows(i))
rowIndex += 1
Count = Count + 1
End If
Next
If (accessionfound = False) Then
Table1.Partial Status ("Cannot find the requested WIG, "Not in Table")
End If
<>

Anybody got a better solution or help fix this code if it’s the correct direction

[ADezii]

Hi

thought I would do another thread as this one is a bit different from the previous problem

I am looking for a solution to the relating problem

Comparing 2 access databases with 2 tables, they are non-related tables, but should have been related by the ID, and reason for this was due to the poor data entry standards. I am trying to compare both databases with a Partial ID in Table 1 and match it to the Full ID in Table 2 with the first and lastname and date of birth.

Note the poor data entry standards are not consistent i.e. all records come with a prefix of 3 chars i.e. “WIG”, records are 9 or 10 string char, and some users have included the first 6 numbers, or last 6 numbers, or from the 2nd number to the last etc.

I have tried the simple "Like" operator in SQL but it does not like matching to another table but instead specified criteria must be entered

Example of Tables

Table 1
Partial ID: Firstname: Lastname:
WIG ASIF ANWAR
WIG1003546 PAMELA KNOX
WIG6408236 Scott Winton
WIG737976 SARAH NEVANS
WIS5134838 Gordon Mcdowall

Table 2
Partial ID: Firstname: Lastname:
WIG ASIF ANWAR
51003546E PAMELA KNOX
64082365R Scott Winton
50737976X SARAH NEVANS
51348384L Gordon Mcdowall

I would like to have 3 new fields of Partial Status, Full Record, Count in Table 1

Partial Status (result of search)
"No match found" or "Match found"

Full Record (If found display full record ID)
WIG ID: 51003546E

Count (count the no of occurrences of match to highlight discrepancies)
The no of matches found: 1

what i believe the solution is.....

Dim Partial ID As String
Dim Count As Integer
Partial ID = Table1.ID
Dim Partialfound As Boolean = False
Count = 0
For i As Integer = 0 To (table2.Rows.Count - 1)
If CStr(table2.Rows(i)("ID").ToString.ToUpper) Like "*" & Partial ID.ToUpper & "*" Then
accessionfound = True
rowIndex = i
Table1.Partial Status ("Found ID")
Table2.AppendText(table2.Rows(i))
rowIndex += 1
Count = Count + 1
End If
Next
If (accessionfound = False) Then
Table1.Partial Status ("Cannot find the requested WIG, "Not in Table")
End If
<>

Anybody got a better solution or help fix this code if it’s the correct direction

Dim MyDB As DAO.Database, MyRS As DAO.Recordset
Dim MyRS_2 As DAO.Recordset, intPartials As Integer
Dim intComplete As Integer
 
Set MyDB = CurrentDb()
Set MyRS = MyDB.OpenRecordset("Table 1", dbOpenSnapshot)
Set MyRS_2 = MyDB.OpenRecordset("Table 2", dbOpenSnapshot)
 
MyRS.MoveFirst: MyRS_2.MoveFirst
 
Debug.Print "MATCH PATTERN ANALYSIS ON [Table 1].[Partial ID] ==> [Table 2].[Partial ID]"
Debug.Print
 
Do While Not MyRS.EOF
  Do While Not MyRS_2.EOF
    If MyRS![Partial ID] = MyRS_2![Partial ID] Then
      Debug.Print "100% Match on " & MyRS![FirstName] & " " & MyRS![LastName] & _
                  ", Partial ID: " & MyRS![Partial ID] & ", Full ID: " & MyRS_2![Partial ID]
                      intComplete = intComplete + 1
    ElseIf Mid$(MyRS![Partial ID], 4) = MyRS_2![Partial ID] Then
      Debug.Print "100% Match on " & MyRS![FirstName] & " " & MyRS![LastName] & _
                  ", Partial ID: " & MyRS![Partial ID] & ", Full ID: " & MyRS_2![Partial ID]
                      intComplete = intComplete + 1
    ElseIf InStr(MyRS_2![Partial ID], Mid$(MyRS![Partial ID], 4)) > 0 And Len(Mid$(MyRS![Partial ID], 4)) > 0 Then
      Debug.Print "'Partial Match' on " & MyRS![FirstName] & " " & MyRS![LastName] & _
                  ", Partial ID: " & MyRS![Partial ID] & ", Full ID: " & MyRS_2![Partial ID]
                      intPartials = intPartials + 1
    End If
      MyRS_2.MoveNext
  Loop
  MyRS_2.MoveFirst
  MyRS.MoveNext
Loop
 
Debug.Print
Debug.Print
Debug.Print "*****************************************************"
Debug.Print "Number of 100% Matches: " & intComplete
Debug.Print "Number of Partial Matches: " & intPartials
Debug.Print "*****************************************************"
 
MyRS.Close
MyRS_2.Close

OUTPUT:

MATCH PATTERN ANALYSIS ON [Table 1].[Partial ID] ==> [Table 2].[Partial ID]
 
100% Match on ASIF ANWAR, Partial ID: WIG, Full ID: WIG
'Partial Match' on PAMELA KNOX, Partial ID: WIG1003546, Full ID: 51003546E
'Partial Match' on Scott Winton, Partial ID: WIG6408236, Full ID: 64082365R
'Partial Match' on SARAH NEVANS, Partial ID: WIG37976, Full ID: 50737976X
'Partial Match' on Gordon Mcdowall, Partial ID: WIS5134838, Full ID: 51348384L
100% Match on Tom Jones, Partial ID: WIG1234567, Full ID: 1234567
 
 
*****************************************************
Number of 100% Matches: 2
Number of Partial Matches: 4
*****************************************************