Left statement used as a variable

  • Thread starter Thread starter Keith
  • Start date Start date
K

Keith

This seems like something that I should be able to do very
easily, but for some reason I'm having problems.

Ultimate goal: Get a script that can assist in finding
dupe records.

Way I would like to accomplish this: I want to search
based on the first few letterse of the lastname. I can do
this easily by using left(lastname, 3).

I then want to take this data and apply it to a search
that will filter out records. For example, let's say I
have two records in the database where the last names are
Smith. I execute a left command that outputs Smi. I then
want to take this Smi and find all the records that start
with Smi.

I'm pretty much trying to do something that would be like

select firstname, lastname, last(lastname, 3) as Test
from employees
where lastname LIKE Test*;

Thanks,

Keith
 
Try something like this SQL.

SELECT FirstName, LastName
FROM Employees
WHERE Left(LastName,3) In
(SELECT Left(T.LastName,3)
FROM Employees as T
GROUP BY (Left(T.LastName,3)
Having Count(T.LastName) > 1)

The Find Duplicates query wizard will build a query to find duplicates based on
the last name. Then you could study that and modify it to get something like
the above query.
 
Back
Top