duplicate names, first 2 charaters only

[morningla]

How can I display results if just the first 2 characters of both these fields: First name, Last name match?

eg.

1. cathy mcdonald
2. carmen mcfarlane

will be displayed as duplicates.

 

[PHV]

Something like this ?
SELECT A.*
FROM yourTable AS A INNER JOIN (
SELECT Left([First name],2) AS F2, Left([Last name],2) AS L2 FROM yourTable GROUP BY Left([First name],2), Left([Last name],2) HAVING Count(*)>1
) AS B ON Left(A.[First name],2)=B.F2 AND Left(A.[Last name],2)=B.L2

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[traingamer]

try (tested)

SELECT table.Fname, table.lname, table.id
FROM table
WHERE (((Left([table.Fname],2)) In (SELECT Left([Fname],2) FROM [table] As Tmp GROUP BY Left([Fname],2),Left([lname],2) HAVING Count(*)>1  And Left([lname],2) = left([table].[lname],2))))
ORDER BY Left([table.Fname],2), Left([table.lname],2);

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[traingamer]

Dang, slow typing again.

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[morningla]

thanks for the reply, i'm still trying it out. I'm fairly new with access. The only thing I know so far is very simple citeria.

The code you gave me, do i need to write that as a new macro? or can i put it under my citeria in query?

My feilds are First Name and Last Name and my table is called SeasonalHireTable. But i've never had to include the table name before b/c I've always been putting citeria under First Name.

Thanks.

 

[PHV]

In the SQL pane of the query window:
SELECT A.*
FROM SeasonalHireTable AS A INNER JOIN (
SELECT Left([First Name],2) AS F2, Left([Last Name],2) AS L2 FROM SeasonalHireTable GROUP BY Left([First Name],2), Left([Last Name],2) HAVING Count(*)>1
) AS B ON Left(A.[First Name],2)=B.F2 AND Left(A.[Last Name],2)=B.L2;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[morningla]

i was able to narrow it to

SELECT SeasonalHireTable.[Last Name], SeasonalHireTable.[First Name], SeasonalHireTable.[Middle Initial], SeasonalHireTable.[Applicant ID]
FROM SeasonalHireTable
WHERE (((Left([First Name],2)) In (SELECT Left([First Name],2) FROM [SeasonalHireTable] As Tmp GROUP BY Left([First Name],2),Left([Last Name],2) HAVING Count(*)>1 And Left([Last Name],2) = left([Last Name],2))) AND ((Left([Last Name],2)) In (SELECT Left([Last Name],2) FROM [SeasonalHireTable] As Tmp GROUP BY Left([Last Name],2),Left([First Name],2) HAVING Count(*)>1 And Left([First Name],2) = left([First Name],2))))
ORDER BY Left([First Name],2), Left([Last Name],2);

but it's showing all cases where
last name OR first name have repeating 2 characters
what do i need to change to make it show ONLY when
last name AND first name repeats?

 

[PHV]

morningla, just to know, have you tried my suggestion ?

 

[morningla]

ok, so the sql is already working, but there are a few names sneaking by, when repeating characters in first/last names overlap. see below examples:

Last Name First Name
Brassard Brett
McLellan Brad
Onciul Brad
Ontkean Braedon
Brown Christopher
Brochu Chelsey
McElroy Chris
Skrypnuk Dan
Skelly David
Stefaniuk Greg
Starko Graham
Bobbett James
Schowalter Jay
Schweizer Jason
Brodeur John
McKellep Joseph
McKnight Joshua
Stellingwerff Jordan
Boyd Kenmir
Boyd Keith
Bow Lindsey
Bookham Lindsey
McKnight Ryan
McFarlane Ryan

like the first two names, I don't need to see.

 

[PHV]

Result of my last suggestion in ac2003 with the data of your last post:[tt]
Last Name First Name
Onciul Brad
Ontkean Braedon
Brown Christopher
Brochu Chelsey
Skrypnuk Dan
Skelly David
Schweizer Jason
Schowalter Jay
McKellep Joseph
McKnight Joshua
Boyd Kenmir
Boyd Keith
Bow Lindsey
Bookham Lindsey
McFarlane Ryan
McKnight Ryan[/tt]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[morningla]

My version of Access is 2002, maybe that's why it keeps crashing?

 

[PHV]

A workaround.
Create a saved query named, say, qryDup:
SELECT Left([First Name],2) AS F2, Left([Last Name],2) AS L2
FROM SeasonalHireTable
GROUP BY Left([First Name],2), Left([Last Name],2)
HAVING Count(*)>1;
Now your query:
SELECT A.*
FROM SeasonalHireTable AS A INNER JOIN qryDup AS B
ON Left(A.[First Name],2)=B.F2 AND Left(A.[Last Name],2)=B.L2;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[morningla]

it worked great, thanks! is there a way to customize what fields would be display? it's displaying everything now and won't let me remove or edit anything.