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

Jul 17 '07 #1

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

OUTPUT:

Jul 26 '07 #2