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 2884
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
-
*****************************************************
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Caliangelas |
last post by:
Hello,
I need a routine to check for a number called CPF (just like Social
Security Number in USA). I already have a validation routine for that
number, but I still need to check if it exists on...
|
by: PDR |
last post by:
Help for a ASP newby. Can anyone help me with a very basic problem. I would
like to learn about this ASP stuff. I know a little of HTML and hev built a
few web pages but I would like to know about...
|
by: James Leech |
last post by:
Hi guys
I am trying to use an Access function that I found on the web from within an
asp page, but I can't seem to correct all the subtle differances between the
two languages. When I run a...
|
by: Grasshopper |
last post by:
Hi,
I am automating Access reports to PDF using PDF Writer 6.0. I've
created a DTS package to run the reports and schedule a job to run this
DTS package. If I PC Anywhere into the server on...
|
by: Jack Addington |
last post by:
I want to scroll through the alphabet in order to scroll some data to the
closest name that starts with a letter. If the user hits the H button then
it should scroll to the letter closest to H. ...
|
by: Morris Neuman |
last post by:
Im working with VS 2005 and trying to use a Hyperlink field in a datagrid to
play a wave file that is not located in the website folders but is in a plain
folder on the same machine, windows 2003...
|
by: David |
last post by:
Hi,
I cannot get the following (MS Access) SQL statement working in my asp
page, please can anyone help me ? Thanks :-)
------------------------------------------------
<%
strQuery =...
|
by: liv |
last post by:
<%@LANGUAGE="VBSCRIPT" %>
<%
dim id_utilizator, conn, rs
id_utilizator = Session("id_utilizator")
if id_utilizator ="" then
Response.Redirect("logare_ut.asp")
end if
set...
|
by: hcaptech |
last post by:
This is my Test.can you help me ?
1.Which of the following statement about C# varialble is incorrect ?
A.A variable is a computer memory location identified by a unique name
B.A variable's name...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 2 August 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM)
The start time is equivalent to 19:00 (7PM) in Central...
|
by: erikbower65 |
last post by:
Using CodiumAI's pr-agent is simple and powerful. Follow these steps:
1. Install CodiumAI CLI: Ensure Node.js is installed, then run 'npm install -g codiumai' in the terminal.
2. Connect to...
|
by: erikbower65 |
last post by:
Here's a concise step-by-step guide for manually installing IntelliJ IDEA:
1. Download: Visit the official JetBrains website and download the IntelliJ IDEA Community or Ultimate edition based on...
|
by: kcodez |
last post by:
As a H5 game development enthusiast, I recently wrote a very interesting little game - Toy Claw ((http://claw.kjeek.com/))。Here I will summarize and share the development experience here, and hope it...
|
by: Taofi |
last post by:
I try to insert a new record but the error message says the number of query names and destination fields are not the same
This are my field names
ID, Budgeted, Actual, Status and Differences
...
|
by: Rina0 |
last post by:
I am looking for a Python code to find the longest common subsequence of two strings. I found this blog post that describes the length of longest common subsequence problem and provides a solution in...
|
by: lllomh |
last post by:
Define the method first
this.state = {
buttonBackgroundColor: 'green',
isBlinking: false, // A new status is added to identify whether the button is blinking or not
}
autoStart=()=>{
|
by: Mushico |
last post by:
How to calculate date of retirement from date of birth
|
by: DJRhino |
last post by:
Was curious if anyone else was having this same issue or not....
I was just Up/Down graded to windows 11 and now my access combo boxes are not acting right. With win 10 I could start typing...
| |