Solved

Add ID number to potential duplicate query

Posted on 2014-04-01
1
264 Views
Last Modified: 2014-04-03
Hello Experts.

I am working on a query that I just can't get to work the way I need.  I have written the following query that identifies subject names (subjectName) that exist more than once in the table:

SELECT multipleSubjectNameQry.subjectName
FROM multipleSubjectNameQry
GROUP BY multipleSubjectNameQry.subjectName
HAVING ((Count(*))>1);

Open in new window


But I need this query to also display the record number (primary key).  But, when I add the primary key field the query doesn't return anything because the results are no longer duplicates.  How can I modify this query so that it provides the list of subject names that exist more than once, along with their record numbers?

Any help would be greatly appreciated.  I am working with MS Access 2013 on Windows 7.

Also, I have tried the following, but it doesn't work either:

SELECT multipleSubjectNameQry.subjectName, multipleSubjectNameQry.myID
FROM multipleSubjectNameQry
GROUP BY multipleSubjectNameQry.subjectName, multipleSubjectNameQry.myID
HAVING (((Count(multipleSubjectNameQry.subjectName))>1));

Open in new window



Thanks,
J
0
Comment
Question by:ferguson_jerald
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 38

Accepted Solution

by:
PatHartman earned 500 total points
ID: 39970393
Use the Find Duplicates wizard to create the query.  You can have it return whatever you want.
0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

635 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question