Duplicate query based on partial match

[Philly44]

I have duplicate query setup that works fine providing I only want to match against the entire lastname. I need it to be able to match against the first 3 characters of the lastname. I have tried using the left function to no avail. Is there any other way I can query for duplicates against the first 3 chars of a string?

Thanx in advance

 

[Chance1234]

how about creating a select query and adding an extra columkn in the query with the first three letters of the surname in there

so fndName:left([Surname],3)

the create a query based on that query and do your duplicates doobrie ?

 

[Philly44]

Thancks but I tried that an Access nevewr comes back from that type of query

 

[Maquis]

This query may help you some. It definitely has disadvantages in that it will only show you the first 3 characters of the last name and no other fields in the table, but it will run quickly.

Chance's answer is the way to get what you want, but if you have many records in your table it really will suck the memory right out of your computer.


SELECT DISTINCTROW Left([LastName],3) AS First3, Count([LastName) AS CountOfLastName FROM [yourtable]
GROUP BY Left([LastName],3)
HAVING (((Count(LastName)>1));



Here's a variation of Chance's query. It runs quickly on my system, but it could drag on yours.

SELECT DISTINCTROW Left([LastName],3) AS First3, recID, FirstName
FROM [yourtable]
WHERE (((Left([LastName],3)) In (SELECT Left([LastName],3) FROM [yourtable] As Tmp GROUP BY Left([LastName],3) HAVING Count(*)>1 )))
ORDER BY Left([LastName],3);
Maq B-)
<insert witty signature here>

 

[Philly44]

Thanks a bunch guys the code works. Slow as heck but hey thats the nature of the beast. The user is fine with it anyways.