SQL query 1

[wwwjont]

I have two tables ie table1 and table2.

The columns in table1 are:
unique_id1, forename, surname, address, dob, gender.

The columns in table1 are:
forename, surname, address, dob, gender, unique_id2

I want to take each record in table 1 match it to its corresponding record in table 2 and add the unique_id2 column to this new table. (unique_id1 and unique_id2 are different). The problem is that users have put in incorrect forenames, surnames, dob etc. I have decided to use dob, address and surname from table1 and try and find a match in table2. I am using Access and have a basic understanding of sql.

The code that written so far is
SELECT e.unique_ID1, e.address, e.forename, e.surname, e.dob, e.gender, w. unique_ID1
FROM Table1 AS e, Table2 AS w
WHERE e.dob=w.dob;

Based on the date of birth but I also need to add surname and address to make the search relatively accurate. I have ran into problems when I start adding address and surname. Any help would be very much appreciated.

 

[Remou]

Your links are broken.

Try:

SELECT e.unique_ID1, e.address, e.forename, e.surname, e.dob, e.gender, w. unique_ID1
FROM Table1 AS e
INNER JOIN Table2 AS w
ON e.dob=w.dob
AND e.surname=w.surname
WHERE left(e.address,10) = left(w.address,10)

It is probably better to limit the number of characters matched from the address to try and cut down on mismatches due to common abbreviations and so forth.

You may also like to consider a soundex column (or columns) in each table for a slightly fuzzier match on surname and forename.

http://lessthandot.com