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
1 2760
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
-
*****************************************************
Post your reply Sign in to post your reply or Sign up for a free account.
Similar topics
1 post
views
Thread by Caliangelas |
last post: by
|
3 posts
views
Thread by PDR |
last post: by
|
1 post
views
Thread by James Leech |
last post: by
|
11 posts
views
Thread by Grasshopper |
last post: by
|
8 posts
views
Thread by Jack Addington |
last post: by
|
9 posts
views
Thread by Morris Neuman |
last post: by
|
1 post
views
Thread by David |
last post: by
| | | | | | | | | | | | |