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.Co unt - 1)
If CStr(table2.Row s(i)("ID").ToSt ring.ToUpper) Like "*" & Partial ID.ToUpper & "*" Then
accessionfound = True
rowIndex = i
Table1.Partial Status ("Found ID")
Table2.AppendTe xt(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
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.Co unt - 1)
If CStr(table2.Row s(i)("ID").ToSt ring.ToUpper) Like "*" & Partial ID.ToUpper & "*" Then
accessionfound = True
rowIndex = i
Table1.Partial Status ("Found ID")
Table2.AppendTe xt(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
Comment