Find duplicates based on Last Name and the initial of First Name

  • Thread starter Thread starter Jack Peyton
  • Start date Start date
J

Jack Peyton

I have the following Query that finds duplicates based on Last Name and
First Name. I would like to be able to find duplicates based on Last Name
and the initial of First Name. Any help will be appreciated.
Jack Peyton

SELECT TEmp.EmpLast, TEmp.EmpFirst, TEmp.EmpNo
FROM TEmp
WHERE (((TEmp.EmpLast) In (SELECT [EmpLast] FROM [TEmp] As Tmp GROUP BY
[EmpLast],[EmpFirst] HAVING Count(*)>1 And [EmpFirst] =
[TEmp].[EmpFirst])))
ORDER BY TEmp.EmpLast, TEmp.EmpFirst;
 
Group by [Last Name] and Left([First Name],1).

Jack said:
I have the following Query that finds duplicates based on Last Name and
First Name. I would like to be able to find duplicates based on Last Name
and the initial of First Name. Any help will be appreciated.
Jack Peyton

SELECT TEmp.EmpLast, TEmp.EmpFirst, TEmp.EmpNo
FROM TEmp
WHERE (((TEmp.EmpLast) In (SELECT [EmpLast] FROM [TEmp] As Tmp GROUP BY
[EmpLast],[EmpFirst] HAVING Count(*)>1 And [EmpFirst] =
[TEmp].[EmpFirst])))
ORDER BY TEmp.EmpLast, TEmp.EmpFirst;
Click to expand...
 
kingston,
Thanks for looking at this. I updated the GROUP BY portion of the query. I
am now getting the following message: "You tried to execute a query that
does not include the specified expression 'EmpLast' as part of an aggregate
function". Any suggestions?
Jack Peyton

Updated SQL
SELECT TEmp.EmpLast, TEmp.EmpFirst, TEmp.EmpNo
FROM TEmp
WHERE (((TEmp.EmpLast) In (SELECT [EmpLast] FROM [TEmp] As Tmp Group by
[Last Name] and Left([First Name],1) HAVING Count(*)>1 And [EmpFirst] =
[TEmp].[EmpFirst])))
ORDER BY TEmp.EmpLast, TEmp.EmpFirst;

kingston via AccessMonster.com said:
Group by [Last Name] and Left([First Name],1).

Jack said:
I have the following Query that finds duplicates based on Last Name and
First Name. I would like to be able to find duplicates based on Last Name
and the initial of First Name. Any help will be appreciated.
Jack Peyton

SELECT TEmp.EmpLast, TEmp.EmpFirst, TEmp.EmpNo
FROM TEmp
WHERE (((TEmp.EmpLast) In (SELECT [EmpLast] FROM [TEmp] As Tmp GROUP BY
[EmpLast],[EmpFirst] HAVING Count(*)>1 And [EmpFirst] =
[TEmp].[EmpFirst])))
ORDER BY TEmp.EmpLast, TEmp.EmpFirst;
Click to expand...
Click to expand...
 
I'm sorry. I didn't mean that literally. You have to use the actual field
names [EmpLast] and [EmpFirst]:

Group by [EmpLast] and Left([EmpFirst],1)

I was just suggesting the use of the function Left() to return the first
letter of the string.

Jack said:
kingston,
Thanks for looking at this. I updated the GROUP BY portion of the query. I
am now getting the following message: "You tried to execute a query that
does not include the specified expression 'EmpLast' as part of an aggregate
function". Any suggestions?
Jack Peyton

Updated SQL
SELECT TEmp.EmpLast, TEmp.EmpFirst, TEmp.EmpNo
FROM TEmp
WHERE (((TEmp.EmpLast) In (SELECT [EmpLast] FROM [TEmp] As Tmp Group by
[Last Name] and Left([First Name],1) HAVING Count(*)>1 And [EmpFirst] =
[TEmp].[EmpFirst])))
ORDER BY TEmp.EmpLast, TEmp.EmpFirst;
Group by [Last Name] and Left([First Name],1).
Click to expand...
[quoted text clipped - 9 lines]
[TEmp].[EmpFirst])))
ORDER BY TEmp.EmpLast, TEmp.EmpFirst;
Click to expand...
Click to expand...
Click to expand...
 
kingston,
I should have picked up on the LastName etc. I corrected the query, however,
I still get the same message, "You tried to execute a query that does not
include the specified expression 'EmpLast' as part of an aggregate
function". It looks like something needs to be added to to the query to make
it work.
Jack Peyton
Corrected Query
SELECT TEmp.EmpLast, TEmp.EmpFirst, TEmp.EmpNo
FROM TEmp
WHERE (((TEmp.EmpLast) In (SELECT [EmpLast] FROM [TEmp] As Tmp Group by
[EmpLast] and Left([EmpFirst],1) HAVING Count(*)>1 And [EmpFirst] =
[TEmp].[EmpFirst])))
ORDER BY TEmp.EmpLast, TEmp.EmpFirst;

kingston via AccessMonster.com said:
I'm sorry. I didn't mean that literally. You have to use the actual field
names [EmpLast] and [EmpFirst]:

Group by [EmpLast] and Left([EmpFirst],1)

I was just suggesting the use of the function Left() to return the first
letter of the string.

Jack said:
kingston,
Thanks for looking at this. I updated the GROUP BY portion of the query. I
am now getting the following message: "You tried to execute a query that
does not include the specified expression 'EmpLast' as part of an aggregate
function". Any suggestions?
Jack Peyton

Updated SQL
SELECT TEmp.EmpLast, TEmp.EmpFirst, TEmp.EmpNo
FROM TEmp
WHERE (((TEmp.EmpLast) In (SELECT [EmpLast] FROM [TEmp] As Tmp Group by
[Last Name] and Left([First Name],1) HAVING Count(*)>1 And [EmpFirst] =
[TEmp].[EmpFirst])))
ORDER BY TEmp.EmpLast, TEmp.EmpFirst;
Group by [Last Name] and Left([First Name],1).
Click to expand...
[quoted text clipped - 9 lines]
[TEmp].[EmpFirst])))
ORDER BY TEmp.EmpLast, TEmp.EmpFirst;
Click to expand...
Click to expand...
Click to expand...
 
Did your original query work? Can you explain why your sub-query is
structured the way it is? Specifically, what are you trying to achieve with
[EmpFirst] = [TEmp].[EmpFirst]? Test the subquery as an independent query to
see if it works before you make it a subquery.

In any case, if you simply want to find duplicates based on LastName and
First Initial, do a totals query with a calculated field:

Group by [LastName] & Left([FirstName],1)
Count [LastName] & Left([FirstName],1) > 1

The results of this query can then be fed into another query that results in
employee names and numbers using the calculated field: [LastName] & Left(
[FirstName],1)
HTH

Jack said:
kingston,
I should have picked up on the LastName etc. I corrected the query, however,
I still get the same message, "You tried to execute a query that does not
include the specified expression 'EmpLast' as part of an aggregate
function". It looks like something needs to be added to to the query to make
it work.
Jack Peyton
Corrected Query
SELECT TEmp.EmpLast, TEmp.EmpFirst, TEmp.EmpNo
FROM TEmp
WHERE (((TEmp.EmpLast) In (SELECT [EmpLast] FROM [TEmp] As Tmp Group by
[EmpLast] and Left([EmpFirst],1) HAVING Count(*)>1 And [EmpFirst] =
[TEmp].[EmpFirst])))
ORDER BY TEmp.EmpLast, TEmp.EmpFirst;
I'm sorry. I didn't mean that literally. You have to use the actual field
names [EmpLast] and [EmpFirst]:
Click to expand...
[quoted text clipped - 24 lines]
[TEmp].[EmpFirst])))
ORDER BY TEmp.EmpLast, TEmp.EmpFirst;
Click to expand...
Click to expand...
Click to expand...
 
kingston,
I will give it a try. Thanks for taking the time to help me with the query.
Jack Peyton

kingston via AccessMonster.com said:
Did your original query work? Can you explain why your sub-query is
structured the way it is? Specifically, what are you trying to achieve with
[EmpFirst] = [TEmp].[EmpFirst]? Test the subquery as an independent query to
see if it works before you make it a subquery.

In any case, if you simply want to find duplicates based on LastName and
First Initial, do a totals query with a calculated field:

Group by [LastName] & Left([FirstName],1)
Count [LastName] & Left([FirstName],1) > 1

The results of this query can then be fed into another query that results in
employee names and numbers using the calculated field: [LastName] & Left(
[FirstName],1)
HTH

Jack said:
kingston,
I should have picked up on the LastName etc. I corrected the query, however,
I still get the same message, "You tried to execute a query that does not
include the specified expression 'EmpLast' as part of an aggregate
function". It looks like something needs to be added to to the query to make
it work.
Jack Peyton
Corrected Query
SELECT TEmp.EmpLast, TEmp.EmpFirst, TEmp.EmpNo
FROM TEmp
WHERE (((TEmp.EmpLast) In (SELECT [EmpLast] FROM [TEmp] As Tmp Group by
[EmpLast] and Left([EmpFirst],1) HAVING Count(*)>1 And [EmpFirst] =
[TEmp].[EmpFirst])))
ORDER BY TEmp.EmpLast, TEmp.EmpFirst;
I'm sorry. I didn't mean that literally. You have to use the actual field
names [EmpLast] and [EmpFirst]:
Click to expand...
[quoted text clipped - 24 lines]
[TEmp].[EmpFirst])))
ORDER BY TEmp.EmpLast, TEmp.EmpFirst;
Click to expand...
Click to expand...
Click to expand...
 
SELECT TEmp.EmpLast
, TEmp.EmpFirst
, TEmp.EmpNo
FROM TEmp
WHERE TEmp.EmpLast & " " & Left(Temp.EmpFirst,1)
In
(SELECT [EmpLast] & " " & Left(EmpFirst,1)
FROM [TEmp] As Tmp
Group By [EmpLast] & " " & Left(EmpFirst,1)
HAVING Count(*)>1)
ORDER BY TEmp.EmpLast, TEmp.EmpFirst;



Jack Peyton said:
kingston,
I should have picked up on the LastName etc. I corrected the query,
however,
I still get the same message, "You tried to execute a query that does not
include the specified expression 'EmpLast' as part of an aggregate
function". It looks like something needs to be added to to the query to
make
it work.
Jack Peyton
Corrected Query
SELECT TEmp.EmpLast, TEmp.EmpFirst, TEmp.EmpNo
FROM TEmp
WHERE (((TEmp.EmpLast) In (SELECT [EmpLast] FROM [TEmp] As Tmp Group by
[EmpLast] and Left([EmpFirst],1) HAVING Count(*)>1 And [EmpFirst] =
[TEmp].[EmpFirst])))
ORDER BY TEmp.EmpLast, TEmp.EmpFirst;

kingston via AccessMonster.com said:
I'm sorry. I didn't mean that literally. You have to use the actual field
names [EmpLast] and [EmpFirst]:

Group by [EmpLast] and Left([EmpFirst],1)

I was just suggesting the use of the function Left() to return the first
letter of the string.

Jack said:
kingston,
Thanks for looking at this. I updated the GROUP BY portion of the query. I
am now getting the following message: "You tried to execute a query that
does not include the specified expression 'EmpLast' as part of an aggregate
function". Any suggestions?
Jack Peyton

Updated SQL
SELECT TEmp.EmpLast, TEmp.EmpFirst, TEmp.EmpNo
FROM TEmp
WHERE (((TEmp.EmpLast) In (SELECT [EmpLast] FROM [TEmp] As Tmp Group by
[Last Name] and Left([First Name],1) HAVING Count(*)>1 And [EmpFirst] =
[TEmp].[EmpFirst])))
ORDER BY TEmp.EmpLast, TEmp.EmpFirst;

Group by [Last Name] and Left([First Name],1).

[quoted text clipped - 9 lines]
[TEmp].[EmpFirst])))
ORDER BY TEmp.EmpLast, TEmp.EmpFirst;
Click to expand...
Click to expand...
Click to expand...
 
Back
Top