472,779 Members | 1,987 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,779 software developers and data experts.

Help for Access VBScript compare 2 db with partial ID

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
1 2884
ADezii
8,834 Expert 8TB
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

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

Similar topics

1
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...
3
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...
1
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...
11
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...
8
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. ...
9
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...
1
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 =...
5
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...
2
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...
3
isladogs
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...
0
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...
0
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...
0
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...
0
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 ...
0
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...
0
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=()=>{
0
by: Mushico | last post by:
How to calculate date of retirement from date of birth
2
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...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.