Matching records

[ozdemirozgur]

Hi,

i have two tables both of which has company codes and names and would like to match the records. However, one of the table used some abbreviations for the first name therefore cannot directly match. I need a code to match the company codes and then the first two letters of the name. However, I cannot use the seek method as it does not work with the first two letters. I can use if with left function but it is taking too much time to compare.

e.g
Table 1 : Company code and employee name (2343, Jonathan Google)
Table 2 : Company Code and employee name (2343, Jon G)

thanks, Ozgur

 

[plog]

Set up 2 queries, one for each table, that includes a custom field that essentially makes a temporary id field based on the criteria you listed.

SELECT *, ([IdFieldNameHere] & "-" & (Mid([NameFieldHere], 1,2) AS TempID FROM Table1;

SELECT *, ([IdFieldNameHere] & "-" & (Mid([NameFieldHere], 1,2) AS TempID FROM Table2;

Then create a query based on those 2 queries that links the TempID field in both. the resulting records are matches based on your criteria.