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
1 2949 ADezii 8,834
Recognized Expert Expert
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
- 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 the institution
databank to avoid frauds on my system.
The idea is to build a "new user register page" and with a CPF field.
So, when the person enter his CPF, the ASP page would check on the
institution databank and validate or not the...
|
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 this ASP. How about some help.
PDR
-----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
|
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 number through my version, i (seemingly) get no
output. I would greatfully appreciate any pointers or help on this issue.
My version:
http://www.graphics-line.co.uk/mycode.txt
|
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 where the job is
running, the job runs sucessfully, PDF files got generated, everything
is good. If I scheduled the job to run at the time that I am not
logged into the server, Access is not able to print to the printer.
The error is pretty...
|
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. If no one exists with H, then
go to I, etc. If its near the end, say 'V', and the last person is a 'T'
then it should work its way back up the alphabet. I was trying to loop as
if the Char's were ints but I am having problems
I have buttons...
| |
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 server, WMP 10.0 .
If I type the full path in an IE address field it plays the file in WMP
When I test my Web page ( running the debugger in VS.)
The datagrid has a column called "MsgFile" with the full path to the wave
file e.g....
|
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 = "SELECT Customers.CustomerName, OrderLines.JobNumber,
Orders.PONumber, OrderLines.OrderQuantity,
|
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 conn=Server.CreateObject("ADODB.Connection")
|
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 is used to access and read the value stored in it
C.A variable is allocated or deallocated in memory during runtime
D.A variable can be initialized at the time of its creation or later
2. The.……types feature facilitates the definition of classes...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |