Select based on 1st character

  • Thread starter Thread starter dc
  • Start date Start date
D

dc

HI, I have a query that finds duplicates based on [firstname] and [lastname]
which works fine however what we have discovered is that if the name has a
variation i.e. Rob instead of Robert it wont detect it. I would like to
build an If statement so that if the 1st character (or 1st few characters)
are alike to bring them into the data. Does anyone know how to build this?
 
Post the SQL statement that you're using right now; we can show how to
modify it using non-equi-joins.
 
Hi Ken, below is the sql statement I am currently using:

SELECT [Dups Comparison].[First Name], [Dups Comparison].[Last Name]
FROM [Dups Comparison]
GROUP BY [Dups Comparison].[First Name], [Dups Comparison].[Last Name]
HAVING (((Count([Dups Comparison].[Last Name]))>1))
ORDER BY [Dups Comparison].[Last Name];

Thank you for your assistance.


Ken Snell (MVP) said:
Post the SQL statement that you're using right now; we can show how to
modify it using non-equi-joins.

--

Ken Snell
<MS ACCESS MVP>


dc said:
HI, I have a query that finds duplicates based on [firstname] and
[lastname]
which works fine however what we have discovered is that if the name has a
variation i.e. Rob instead of Robert it wont detect it. I would like to
build an If statement so that if the 1st character (or 1st few characters)
are alike to bring them into the data. Does anyone know how to build
this?
Click to expand...
Click to expand...
 
Something like this should get you started for a "dups comparison" query
assuming that you're looking for duplicates within the same table (Note
that you cannot create/build this query in the Design view; you'll need to
type the SQL statement in the SQL view directly) -- also, the SQL statement
you posted will not be amenable to this structure because you need to have
the Dups Comparison query have this nonequi-join structure:

SELECT TableName.[First Name], TableName.[Last Name]
FROM TableName INNER JOIN TableName AS T
ON Left(TableName.[First Name], 3) = Left(T.[First Name], 3)
AND Left(TableName.[Last Name], 3) = Left(T.[Last Name], 3);


If you're looking for duplicates in two different tables, then you'd use
something like this:

SELECT TableName.[First Name], TableName.[Last Name]
FROM TableName INNER JOIN OtherTableName
ON Left(TableName.[First Name], 3) = Left(OtherTableName.[First Name], 3)
AND Left(TableName.[Last Name], 3) = Left(OtherTableName.[Last Name], 3);


--

Ken Snell
<MS ACCESS MVP>


dc said:
Hi Ken, below is the sql statement I am currently using:

SELECT [Dups Comparison].[First Name], [Dups Comparison].[Last Name]
FROM [Dups Comparison]
GROUP BY [Dups Comparison].[First Name], [Dups Comparison].[Last Name]
HAVING (((Count([Dups Comparison].[Last Name]))>1))
ORDER BY [Dups Comparison].[Last Name];

Thank you for your assistance.


Ken Snell (MVP) said:
Post the SQL statement that you're using right now; we can show how to
modify it using non-equi-joins.

--

Ken Snell
<MS ACCESS MVP>


dc said:
HI, I have a query that finds duplicates based on [firstname] and
[lastname]
which works fine however what we have discovered is that if the name
has a
variation i.e. Rob instead of Robert it wont detect it. I would like
to
build an If statement so that if the 1st character (or 1st few
characters)
are alike to bring them into the data. Does anyone know how to build
this?
Click to expand...
Click to expand...
Click to expand...
 
Perhaps the following would work for you.

SELECT YourTable.*
FROM YourTable
WHERE LastName in
(SELECT LastName
FROM Table as Tmp
GROUP BY LastName, LEFT(FirstName,3)
HAVING Count(*) > 1 and Left(Tmp.FirstName,3) =
YourTable.Left(firstName,3))

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
Thank you to you both, I'll give them both a try, I appreciate the
information so much!!

John Spencer said:
Perhaps the following would work for you.

SELECT YourTable.*
FROM YourTable
WHERE LastName in
(SELECT LastName
FROM Table as Tmp
GROUP BY LastName, LEFT(FirstName,3)
HAVING Count(*) > 1 and Left(Tmp.FirstName,3) =
YourTable.Left(firstName,3))

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

dc said:
HI, I have a query that finds duplicates based on [firstname] and
[lastname]
which works fine however what we have discovered is that if the name has a
variation i.e. Rob instead of Robert it wont detect it. I would like to
build an If statement so that if the 1st character (or 1st few characters)
are alike to bring them into the data. Does anyone know how to build
this?
Click to expand...
Click to expand...
 
Back
Top