Hi
I have a project with access
it has 2 non-related tables in access, which should have been related by ID, due to the poor data entry standards I am trying to match records from table 1 to table 2. I hvae tried the simple "Like" operator in SQL but it does not like matching to another table but instead a specified criteria
Example
Table 1
ID: WIG102898
Firstname: John
Lastname: Smith
DOB: 29/05/89
Table 2
ID: 10289856
Firstname: John
Lastname: Smith
DOB: 29/05/89
as you can see ID is very similar and looking for a solution to find records with similar ID, firstname, lastname, and DOB thus removing duplicate records matching the partial ID
I believe I need to compare from 1 table's partial ID to another tables's partial ID, is this possible with SQL or an in-depth VB Script is required?
any help with this would be great and appreciated and a detailed solution
3 2882
You could try opening a query in access. Bring in the fields from the first table ID/Firstname/Lastname/DOB
In an empty window in the query type
GetLenght: Len(ID) ( in the Field: area.)
Then in the next window type
PartialID: Right(ID,GetLen ght-3)
This will give 102898 as a linkable field in this query. Open another query and bring in the next table. In a blank window type
PartialID:Left( ID,6). Again giving you 102898 as a linkable field
You can the link both queries by the PartialID field either in another query or in the relationship area in access
Thanks for the reply rossmrn
It worked for what I posted on the forum but what if the numbers do not match in their relative position, as some records in Table 2 ID have been entered by the 6 first numbers, while some have been entered by the 6 last numbers taken, in addition some records in table 2 ID contains either 10 or 9 characters in the field.
Table 1
ID: 50998787E
Firstname: John
Lastname: Smith
Table 2
ID: WIG998787 or ID: WIG509987
Firstname: John
Lastname: Smith
Is there a way for Access to find a partial ID with this in mind?
A BETTER EXAMPLE OF THE FORMAT OF WHAT AM TRYING TO MATCH WITH THE PARTIAL ID
Table 1
ID: WIG737976, Firstname: SARAH, Lastname: NEVANS
WIG995472 STEVEN DORIS
WIG995473 LEE HANNAH
WIG995476 PAUL GOWRAN
WIG998787 TRACY MONTEITH
WIG1003546 PAMELA KNOX
WIG1085360 STEPHEN LANG
WIS5134838 Gordon Mcdowall
Table 2
ID: 50737976X, Firstname: SARAH, Lastname: NEVANS
50995472K STEVEN DORIS
50995473M LEE HANNAH
50995476B PAUL GOWRAN
50998787E TRACY MONTEITH
51003546E PAMELA KNOX
51085360L STEPHEN LANG
51348384L Gordon Mcdowall
any advice would be great! ;)
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Megan |
last post by:
Hi everybody-
I'm helping a friend with a music database. She has an old one and is
creating a new one. She wants to compare records and fields in the old
database with records and fields in the new database. For instance,
her old database has a table with Band Info in it. Her new database
also has a table with Band Info in it but slightly different. I was
wondering if there was an easy way to compare the fields from similar
tables in...
|
by: Prakash RudraRaju |
last post by:
Hi,
I have recently migrated MSAccess Tables (nearly 120) to MySQL. To test
successful migration I want to compare all tables. I have linked MySQL
tables through ODBC connection.
I want to create a report that compares all records between all tables in
both databases. I am looking for a report with differences in number of
records and differences in fields if 2 records are different. Probabaly it
can be acheived through query, but I...
|
by: Vincent |
last post by:
has any one seen a program to compare mdbs'. I have ran into a few of
them, but none seem to really do that job.
Basically what I need to do is, take 2 access mdb's and check the
differences between the 2. i am talking about tables, forms, queries,
the whole ball of wax. Most of the programs jus do tables, that is
the easy part.
Also I was wondering how you would compate text fiels progammatically
and kick back a report using...
|
by: geronimo_me |
last post by:
Hi,
I am atempting to compare part of a field with the whole of another
field in access. Is this possible?
Basically I have 2 tables with the following info:
Table1
Field1 = MR.
|
by: dfs9 |
last post by:
In the article "Delete Duplicate Records From Access Tables"
By Danny Lesandrini writes the following:
This final suggestion is the most flexible and accurate. Given any
table, it generates a recordset of appropriate fields (excluding memo
and binary image fields) and dynamically loops through the fields'
collection to perform the recordset compare.
In this example, two recordsets are used, one being a clone of the
other. When a...
| |
by: SQL Learner |
last post by:
Hi Alex (Kuznetsov) and All,
This is to follow up with my last post, "Link two tables using partial
word match".
How can I UPDATE table using partial word match?
How can I write a SQL statement to do so? (I am using MS-Access.)
The two tables and the expected result are listed below:
Thank you in advance!
|
by: Edd E |
last post by:
Hi,
I have a database to store my analyses (Access 2002, WinXp), the basic structure is:
TABLE 1 = Sample Info
TABLE 2 = Analysis type 1
TABLE 3 = Analysis type 2
TABLE 4 = Analysis type 3
....where each table stores results of a different type of analysis
|
by: chungiemo |
last post by:
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...
|
by: tbucha3 |
last post by:
I have two tables that I have to get information from to produce another table. Both tables come from separate resources, one has partial accounts with addresses and the other has complete account numbers with names. I need all of the information on one report and I'm struggling to write an SQL to do a partial number match.
Table1
ACCT Address
123 123 street
789 15 crescent
Table 2
Acct Name
12345 Tom
|
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: 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: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
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: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
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...
| |