Access 2013 Duplicate Finder Subquery Issue

[geospatial]

I currently have the following SQL code

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]

Does changing it to this help?

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]

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]

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]

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]

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]

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]

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]

I suppose it might help if you post the query for qryAllKeys too.