is this query possible??

[pbolger]

I have a DB with 2 Tables, List1 and List2 (both tables have the same fields, Name, Address and Phone)

I want to run a query that will compare, say, the Name field from Table1 against Table2 and return the matching values.

IT IS NOT THIS SIMPLE THOUGH! – for eg in Table1, the Name1 is Paula Byrne – in Table2, the Name2 is Paula Ltd. I need both of these brought back.

Is it possible to do this? Is there some way I can base the query on the first few letters of a field??

Any help greatly appreciated,

Thanks
pbolger

 

[neileg]

Mmm....

Well, the straight answer is yes, if you use the Left() function this will take the specified number of characters from your name field. You can then match these from your two lists.

But, given the data you supplied, you will have to limit the number of characters to 3 so that Pat matches to Patrick. What if you have Joseph Bloggs in one list and J. Bloggs Limited in the other. You're down to matching only one character. This isn't going to be much help to you!

 

[pbolger]

thanks neileg.

that i suppose was a bad example to give you. i reckon we could have it to match the first 4-5 characters. could you maybe show me how you would write this query ?

thanks a lot.

patrick

 

[neileg]

Create a query based on one of your tables. Suppose the name field is called Name. Add a new field with the expression ShortName:Left([Name],4)
This will extract the first 4 chars of your name field.
Do the same on your second table.
Run the find duplicates query wizard and use these two queries as the basis. This will return all your matching records.

There are moe 'elegant' ways of doing this, but this works and you should be able to follow what is going on.