Partial Search and compare between 2 Access Tables

[chungiemo]

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

[rossmrn]

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

[chungiemo]

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?

[chungiemo]

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! ;)