By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,503 Members | 2,788 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,503 IT Pros & Developers. It's quick & easy.

Help for Access VBScript compare 2 db with partial ID

P: 7
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
Jul 17 '07 #1
Share this Question
Share on Google+
1 Reply


ADezii
Expert 5K+
P: 8,597
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
Expand|Select|Wrap|Line Numbers
  1. Dim MyDB As DAO.Database, MyRS As DAO.Recordset
  2. Dim MyRS_2 As DAO.Recordset, intPartials As Integer
  3. Dim intComplete As Integer
  4.  
  5. Set MyDB = CurrentDb()
  6. Set MyRS = MyDB.OpenRecordset("Table 1", dbOpenSnapshot)
  7. Set MyRS_2 = MyDB.OpenRecordset("Table 2", dbOpenSnapshot)
  8.  
  9. MyRS.MoveFirst: MyRS_2.MoveFirst
  10.  
  11. Debug.Print "MATCH PATTERN ANALYSIS ON [Table 1].[Partial ID] ==> [Table 2].[Partial ID]"
  12. Debug.Print
  13.  
  14. Do While Not MyRS.EOF
  15.   Do While Not MyRS_2.EOF
  16.     If MyRS![Partial ID] = MyRS_2![Partial ID] Then
  17.       Debug.Print "100% Match on " & MyRS![FirstName] & " " & MyRS![LastName] & _
  18.                   ", Partial ID: " & MyRS![Partial ID] & ", Full ID: " & MyRS_2![Partial ID]
  19.                       intComplete = intComplete + 1
  20.     ElseIf Mid$(MyRS![Partial ID], 4) = MyRS_2![Partial ID] Then
  21.       Debug.Print "100% Match on " & MyRS![FirstName] & " " & MyRS![LastName] & _
  22.                   ", Partial ID: " & MyRS![Partial ID] & ", Full ID: " & MyRS_2![Partial ID]
  23.                       intComplete = intComplete + 1
  24.     ElseIf InStr(MyRS_2![Partial ID], Mid$(MyRS![Partial ID], 4)) > 0 And Len(Mid$(MyRS![Partial ID], 4)) > 0 Then
  25.       Debug.Print "'Partial Match' on " & MyRS![FirstName] & " " & MyRS![LastName] & _
  26.                   ", Partial ID: " & MyRS![Partial ID] & ", Full ID: " & MyRS_2![Partial ID]
  27.                       intPartials = intPartials + 1
  28.     End If
  29.       MyRS_2.MoveNext
  30.   Loop
  31.   MyRS_2.MoveFirst
  32.   MyRS.MoveNext
  33. Loop
  34.  
  35. Debug.Print
  36. Debug.Print
  37. Debug.Print "*****************************************************"
  38. Debug.Print "Number of 100% Matches: " & intComplete
  39. Debug.Print "Number of Partial Matches: " & intPartials
  40. Debug.Print "*****************************************************"
  41.  
  42. MyRS.Close
  43. MyRS_2.Close
OUTPUT:
Expand|Select|Wrap|Line Numbers
  1. MATCH PATTERN ANALYSIS ON [Table 1].[Partial ID] ==> [Table 2].[Partial ID]
  2.  
  3. 100% Match on ASIF ANWAR, Partial ID: WIG, Full ID: WIG
  4. 'Partial Match' on PAMELA KNOX, Partial ID: WIG1003546, Full ID: 51003546E
  5. 'Partial Match' on Scott Winton, Partial ID: WIG6408236, Full ID: 64082365R
  6. 'Partial Match' on SARAH NEVANS, Partial ID: WIG37976, Full ID: 50737976X
  7. 'Partial Match' on Gordon Mcdowall, Partial ID: WIS5134838, Full ID: 51348384L
  8. 100% Match on Tom Jones, Partial ID: WIG1234567, Full ID: 1234567
  9.  
  10.  
  11. *****************************************************
  12. Number of 100% Matches: 2
  13. Number of Partial Matches: 4
  14. *****************************************************
Jul 26 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.