How do I find duplicates in a concatenated field?

  • Thread starter Thread starter Robin Chapple
  • Start date Start date
R

Robin Chapple

I have a membership database with [LastName] and [FirstName] as two of
fields. Data is entered by members to a web based database. I need to
check that duplication does not take place.

I have a query based on the table with a concatenated field that gives
me the complete name:

naim:[LastName]&" "&[FirstName]

My plan was to search for duplicates on the [Naim] field.

I have used the wizard to construct the "Find Duplicates" query with
this error message:

"" You tried to execute a query that does not include the spcified
expression
'Count (*) >1 And [LastName]&" "&[FirstName]='

as part of an aggregate function.

The query qryDatabase performs to produce the concatenated field. The
"Find Duplicates" was made with the wizard. How do I find my
duplicates?

Thanks,
Robin Chapple
 
Made by the Wizard:

SELECT qryDatabase.Naim, qryDatabase.ID
FROM qryDatabase
WHERE (((qryDatabase.Naim) In (SELECT [Naim] FROM [qryDatabase] As Tmp
GROUP BY [Naim] HAVING Count(*)>1 )))
ORDER BY qryDatabase.Naim;
 
This SQL doesn't contain any concatenation expressions. Is the error message
that you're getting resulting from trying to run this query? Or from trying
to run qryDatabase on its own?

Post the SQL statement of qryDatabase as well.

--

Ken Snell
<MS ACCESS MVP>

Robin Chapple said:
Made by the Wizard:

SELECT qryDatabase.Naim, qryDatabase.ID
FROM qryDatabase
WHERE (((qryDatabase.Naim) In (SELECT [Naim] FROM [qryDatabase] As Tmp
GROUP BY [Naim] HAVING Count(*)>1 )))
ORDER BY qryDatabase.Naim;


Post the SQL statement that you're trying to use.
Click to expand...
Click to expand...
 
This SQL doesn't contain any concatenation expressions. Is the error message
that you're getting resulting from trying to run this query? Or from trying
to run qryDatabase on its own?
Click to expand...
The qryDatabase runs OK by itself. The problem come from the original
SQL made by the Wizard
Post the SQL statement of qryDatabase as well.
Click to expand...

SELECT tblDatabase.ID, tblDatabase.UserName, tblDatabase.Password,
tblDatabase.Title, tblDatabase.FirstName, tblDatabase.NickName,
tblDatabase.LastName, tblDatabase.Office, tblDatabase.Club,
tblDatabase.ClubWeb, tblDatabase.District, tblDatabase.DistrictWeb,
tblDatabase.EmailConsent, tblDatabase.Email, tblDatabase.Address,
tblDatabase.City, tblDatabase.State, tblDatabase.PostCode,
tblDatabase.Country, tblDatabase.Phone, tblDatabase.Fax,
tblDatabase.ICQ, tblDatabase.PHF, tblDatabase.MPHF,
tblDatabase.Benefactor, tblDatabase.Bequest, tblDatabase.SpName,
tblDatabase.SpRotarian, tblDatabase.Vocation, tblDatabase.Hobbies,
tblDatabase.PicFile, tblDatabase.Joined, tblDatabase.BornMonth,
tblDatabase.Bornday, tblDatabase.BornYear, tblDatabase.SpBornMonth,
tblDatabase.SpBornDay, tblDatabase.SpBornYear, tblDatabase.WedMonth,
tblDatabase.WedDay, tblDatabase.WedYear, tblDatabase.ROTIORG,
tblDatabase.Serious, tblDatabase.ROTIWiz, tblDatabase.ROTIGer,
tblDatabase.RotiLat, tblDatabase.LastVisit, [LastName] & " " &
[FirstName] AS Naim
FROM tblDatabase;
 
Hmmm.....

Let's try tweaking the SQL statement from the wizard:

SELECT qryDatabase.Naim, qryDatabase.ID
FROM qryDatabase
WHERE (((qryDatabase.Naim) In (SELECT Tmp.[Naim] FROM [qryDatabase] As Tmp
GROUP BY Tmp.[Naim] HAVING Count(Tmp.*)>1 )))
ORDER BY qryDatabase.Naim;


--

Ken Snell
<MS ACCESS MVP>


Robin Chapple said:
This SQL doesn't contain any concatenation expressions. Is the error
message
that you're getting resulting from trying to run this query? Or from
trying
to run qryDatabase on its own?
Click to expand...
The qryDatabase runs OK by itself. The problem come from the original
SQL made by the Wizard
Post the SQL statement of qryDatabase as well.
Click to expand...

SELECT tblDatabase.ID, tblDatabase.UserName, tblDatabase.Password,
tblDatabase.Title, tblDatabase.FirstName, tblDatabase.NickName,
tblDatabase.LastName, tblDatabase.Office, tblDatabase.Club,
tblDatabase.ClubWeb, tblDatabase.District, tblDatabase.DistrictWeb,
tblDatabase.EmailConsent, tblDatabase.Email, tblDatabase.Address,
tblDatabase.City, tblDatabase.State, tblDatabase.PostCode,
tblDatabase.Country, tblDatabase.Phone, tblDatabase.Fax,
tblDatabase.ICQ, tblDatabase.PHF, tblDatabase.MPHF,
tblDatabase.Benefactor, tblDatabase.Bequest, tblDatabase.SpName,
tblDatabase.SpRotarian, tblDatabase.Vocation, tblDatabase.Hobbies,
tblDatabase.PicFile, tblDatabase.Joined, tblDatabase.BornMonth,
tblDatabase.Bornday, tblDatabase.BornYear, tblDatabase.SpBornMonth,
tblDatabase.SpBornDay, tblDatabase.SpBornYear, tblDatabase.WedMonth,
tblDatabase.WedDay, tblDatabase.WedYear, tblDatabase.ROTIORG,
tblDatabase.Serious, tblDatabase.ROTIWiz, tblDatabase.ROTIGer,
tblDatabase.RotiLat, tblDatabase.LastVisit, [LastName] & " " &
[FirstName] AS Naim
FROM tblDatabase;
Click to expand...
 
Error Message:

Syntax error:

(((qryDatabase.Naim) In (SELECT Tmp.[Naim] FROM [qryDatabase] As Tmp
GROUP BY Tmp.[Naim] HAVING Count(Tmp.*)>1 )))

Robin
 
Sorry... I think I got a bit carried away with my Tmp's...and let's change
the Count setup a bit...

(((qryDatabase.Naim) In (SELECT Tmp.[Naim] FROM [qryDatabase] As Tmp
GROUP BY Tmp.[Naim] HAVING Count(Tmp.Naim)>1 )))


--

Ken Snell
<MS ACCESS MVP>

Robin Chapple said:
Error Message:

Syntax error:

(((qryDatabase.Naim) In (SELECT Tmp.[Naim] FROM [qryDatabase] As Tmp
GROUP BY Tmp.[Naim] HAVING Count(Tmp.*)>1 )))

Robin

Hmmm.....

Let's try tweaking the SQL statement from the wizard:

SELECT qryDatabase.Naim, qryDatabase.ID
FROM qryDatabase
WHERE (((qryDatabase.Naim) In (SELECT Tmp.[Naim] FROM [qryDatabase] As Tmp
GROUP BY Tmp.[Naim] HAVING Count(Tmp.*)>1 )))
ORDER BY qryDatabase.Naim;
Click to expand...
Click to expand...
 
Well, now, this is very interesting. I've not seen this behavior before.
I'll check with some experts on Jet SQL, but in the meantime, let's change
the wizard's query to this to see if we can get around the problem:

SELECT qryDatabase.Naim, qryDatabase.ID
FROM qryDatabase
WHERE DCount("*", "qryDatabase", "[Naim]='" & qryDatabase.Naim & "'")>1
ORDER BY qryDatabase.Naim;


--

Ken Snell
<MS ACCESS MVP>




Robin Chapple said:
I chickened out from copying this and then typing it!!

http://www.rotary9790.org.au/test/vba.htm

I can't understand why it is looking for the separate fields.

Robin

Hmmm.....

Let's try tweaking the SQL statement from the wizard:

SELECT qryDatabase.Naim, qryDatabase.ID
FROM qryDatabase
WHERE (((qryDatabase.Naim) In (SELECT Tmp.[Naim] FROM [qryDatabase] As Tmp
GROUP BY Tmp.[Naim] HAVING Count(Tmp.*)>1 )))
ORDER BY qryDatabase.Naim;
Click to expand...
Click to expand...
 
The embedded ' character is confusing Jet into thinking the end of the WHERE
clause has been reached. This should work:

SELECT qryDatabase.Naim, qryDatabase.ID
FROM qryDatabase
WHERE DCount("*", "qryDatabase", "[Naim]='" &
Replace(qryDatabase.Naim,"'","''",1,-1,1) & "'")>1
ORDER BY qryDatabase.Naim;


Additionally, could you test one other SQL for me to see if it works ok?

SELECT qryDatabase.Naim, qryDatabase.ID
FROM qryDatabase
WHERE qryDatabase.Naim In (SELECT [LastName] & " " & [FirstName]
AS Naim FROM tblDatabase As Tmp
GROUP BY [LastName] & " " & [FirstName]
HAVING Count([LastName] & " " & [FirstName])>1 )
ORDER BY qryDatabase.Naim;



--

Ken Snell
<MS ACCESS MVP>


SELECT tblDatabase.*, [LastName] & " " & [FirstName] AS Naim FROM
tblDatabase



Robin Chapple said:
That works but doesn't like one of my records:

http://www.rotary9790.org.au/test/vba.htm

Thanks for your efforts.

Robin

Well, now, this is very interesting. I've not seen this behavior before.
I'll check with some experts on Jet SQL, but in the meantime, let's change
the wizard's query to this to see if we can get around the problem:

SELECT qryDatabase.Naim, qryDatabase.ID
FROM qryDatabase
WHERE DCount("*", "qryDatabase", "[Naim]='" & qryDatabase.Naim & "'")>1
ORDER BY qryDatabase.Naim;
Click to expand...
Click to expand...
 
No, the error shouldn't have caused any damage to your database. Most
intriguing.... I am beginning to think that there is a bug here in ACCESS.

Go with the other method that worked.

Thanks.
--

Ken Snell
<MS ACCESS MVP>


Robin Chapple said:
Ken,

The first example worked like a charm and I have 11 miscreants.

The second one crashed the database and produced this:

http://www.rotary9790.org.au/test/vba.htm

Has it done damage?

Thanks,

Robin


The embedded ' character is confusing Jet into thinking the end of the
WHERE
clause has been reached. This should work:

SELECT qryDatabase.Naim, qryDatabase.ID
FROM qryDatabase
WHERE DCount("*", "qryDatabase", "[Naim]='" &
Replace(qryDatabase.Naim,"'","''",1,-1,1) & "'")>1
ORDER BY qryDatabase.Naim;


Additionally, could you test one other SQL for me to see if it works ok?

SELECT qryDatabase.Naim, qryDatabase.ID
FROM qryDatabase
WHERE qryDatabase.Naim In (SELECT [LastName] & " " & [FirstName]
AS Naim FROM tblDatabase As Tmp
GROUP BY [LastName] & " " & [FirstName]
HAVING Count([LastName] & " " & [FirstName])>1 )
ORDER BY qryDatabase.Naim;
Click to expand...
Click to expand...
 
One last test, if you would, please:

SELECT qryDatabase.Naim, qryDatabase.ID
FROM qryDatabase
WHERE qryDatabase.Naim In
(SELECT Tmp.[LastName] & " " & Tmp.[FirstName]
AS N FROM tblDatabase As Tmp
GROUP BY Tmp.[LastName] & " " & Tmp.[FirstName]
HAVING Count(Tmp.[LastName] & " " & Tmp.[FirstName])>1 )
ORDER BY qryDatabase.Naim;

--

Ken Snell
<MS ACCESS MVP>



Robin Chapple said:
Ken,

The first example worked like a charm and I have 11 miscreants.

The second one crashed the database and produced this:

http://www.rotary9790.org.au/test/vba.htm

Has it done damage?

Thanks,

Robin


The embedded ' character is confusing Jet into thinking the end of the
WHERE
clause has been reached. This should work:

SELECT qryDatabase.Naim, qryDatabase.ID
FROM qryDatabase
WHERE DCount("*", "qryDatabase", "[Naim]='" &
Replace(qryDatabase.Naim,"'","''",1,-1,1) & "'")>1
ORDER BY qryDatabase.Naim;


Additionally, could you test one other SQL for me to see if it works ok?

SELECT qryDatabase.Naim, qryDatabase.ID
FROM qryDatabase
WHERE qryDatabase.Naim In (SELECT [LastName] & " " & [FirstName]
AS Naim FROM tblDatabase As Tmp
GROUP BY [LastName] & " " & [FirstName]
HAVING Count([LastName] & " " & [FirstName])>1 )
ORDER BY qryDatabase.Naim;
Click to expand...
Click to expand...
 
Thanks, Robin. You have found a bug in ACCESS; sorry that you don't win an
award < grin ! > but we've forwarded the information to Microsoft for
documenting and (we hope) fixing.

Good luck!
--

Ken Snell
<MS ACCESS MVP>



Robin Chapple said:
Ken,

Access crashed as before. I have put the error message on the site but
it looks the same to me

http://www.rotary9790.org.au/test/vba.htm

One last test, if you would, please:

SELECT qryDatabase.Naim, qryDatabase.ID
FROM qryDatabase
WHERE qryDatabase.Naim In
(SELECT Tmp.[LastName] & " " & Tmp.[FirstName]
AS N FROM tblDatabase As Tmp
GROUP BY Tmp.[LastName] & " " & Tmp.[FirstName]
HAVING Count(Tmp.[LastName] & " " & Tmp.[FirstName])>1 )
ORDER BY qryDatabase.Naim;
Click to expand...
Click to expand...
 
Back
Top