Solved

Add ID number to potential duplicate query

Posted on 2014-04-01
1
258 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
1 Comment
 
LVL 34

Accepted Solution

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

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

772 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now