Access 2013 Duplicate Finder Subquery Issue

geospatial

Active Member
Joined
Sep 2, 2008
Messages
289
I currently have the following SQL code
SQL:
SELECT qryAllKeys.KeyTitle, qryAllKeys.employeeFirstName, qryAllKeys.employeeLastName, qryAllKeys.employeeMiddleInitial, qryAllKeys.keyNumber, qryAllKeys.serialNumber
FROM qryAllKeys
WHERE (((qryAllKeys.KeyTitle)=(SELECT [KeyTitle] FROM [qryAllKeys] As Tmp GROUP BY [KeyTitle] HAVING Count(*)>1 )))
ORDER BY qryAllKeys.KeyTitle;
It does work unless there is more than one set of duplicates and then I get the error: "At most one record can be returned by this subquery". I am not sure how to fix this issue in the SQL Code.
 

JonXL

Active Member
Joined
Feb 5, 2018
Messages
280
Office Version
365, 2016
Platform
Windows
Does changing it to this help?

SQL:
SELECT qryAllKeys.KeyTitle, qryAllKeys.employeeFirstName, qryAllKeys.employeeLastName, qryAllKeys.employeeMiddleInitial, qryAllKeys.keyNumber, qryAllKeys.serialNumber
FROM qryAllKeys
WHERE (((qryAllKeys.KeyTitle) IN (SELECT [KeyTitle] FROM [qryAllKeys] As Tmp GROUP BY [KeyTitle] HAVING Count(*)>1 )))
ORDER BY qryAllKeys.KeyTitle;
 

geospatial

Active Member
Joined
Sep 2, 2008
Messages
289
Sorry for the delayed response. Changing it to what you had fives me the error "Your query does not include the specified expression 'Count(*)>1 AND [keyNumber] & "_"[serialNumber]=' as part of an aggregated function. Is that because its a concatenated field? Can I not use that?
 

xenou

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,585
Office Version
2013
Platform
Windows
Hi, Post your SQL now - the errors you report don't seem to match anything in the above queries really so would need to see exactly what you are doing.
 

geospatial

Active Member
Joined
Sep 2, 2008
Messages
289
SQL:
SELECT qryAllKeys.KeyTitle, qryAllKeys.employeeFirstName, qryAllKeys.employeeLastName, qryAllKeys.employeeMiddleInitial, qryAllKeys.keyNumber, qryAllKeys.serialNumber
FROM qryAllKeys
WHERE (((qryAllKeys.KeyTitle) IN (SELECT [KeyTitle] FROM [qryAllKeys] As Tmp GROUP BY [KeyTitle] HAVING Count(*)>1 )))
ORDER BY qryAllKeys.KeyTitle;
 

xenou

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,585
Office Version
2013
Platform
Windows
Your query works for me. Not sure what's going on. Weird thing is I don't even see this stuff in your query at all: 'Count(*)>1 AND [keyNumber] & "_"[serialNumber]='
 

geospatial

Active Member
Joined
Sep 2, 2008
Messages
289
Your query works for me. Not sure what's going on. Weird thing is I don't even see this stuff in your query at all: 'Count(*)>1 AND [keyNumber] & "_"[serialNumber]='
The only thing I can think of is that the the AND [keyNumber]&"_"[serialNumber]=' is the concatenated formula for [keyTitle]
 

JonXL

Active Member
Joined
Feb 5, 2018
Messages
280
Office Version
365, 2016
Platform
Windows
The only thing I can think of is that the the AND [keyNumber]&"_"[serialNumber]=' is the concatenated formula for [keyTitle]
Can you post that query, too? The concatenation formula doesn't really look right, but it'd be best to see it in the source query.
 

xenou

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,585
Office Version
2013
Platform
Windows
I suppose it might help if you post the query for qryAllKeys too.
 

Forum statistics

Threads
1,085,747
Messages
5,385,651
Members
401,964
Latest member
Broccolikid

Some videos you may like

This Week's Hot Topics

Top