• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 329
  • Last Modified:

SQL-How to extract records that have ID is the same

I have made this up to illustrate what I need. I have records of the following format:
Id, field, field2
And the sample data is as follow
1, D1, D2
1, DD1, DD2
2, D12, D22
3, D13, D23
3, DD13, DD23
4, D14, D24
5, D15, D25
5, DD15, DD25
6, D16 , D26
7, D17, D27
I like to create a generic query that can can select only Id = 1, 3,5
I would also like to create a query that take out the records that have no duplicate like Id = 2,4,6,7
Thanks
0
tommym121
Asked:
tommym121
  • 2
  • 2
2 Solutions
 
dgranCommented:
My first query would be:

SELECT Table1.ID_Field
FROM Table1
GROUP BY Table1.ID_Field
HAVING (((Count(Table1.ID_Field))>1));

Then I would create a second query as follows:

SELECT Table1.ID_Field, Table1.Field1, Table1.Field2
FROM Query1 INNER JOIN Table1 ON Query1.ID_Field = Table1.ID_Field;

I've attached the .accdb as well for review.
Database8.accdb
0
 
Matthew KellyCommented:
Select records without duplicate
SELECT Id FROM tbl GROUP BY Id HAVING count(*) = 1

Open in new window


Select records with duplicate
SELECT * FROM tbl WHERE Id IN (SELECT Id FROM tbl GROUP BY Id HAVING count(*) > 1)

Open in new window


Delete duplicates
DELETE FROM tbl WHERE Id IN (SELECT Id FROM tbl GROUP BY Id HAVING count(*) > 1)

Open in new window


Ensure the selects give the data you are expecting before running the delete
0
 
dgranCommented:
Are you wanting to actually delete records from the table or just remove them from being displayed?

When showing records such as ID=1, are you wanting to keep all instances or just keep/look at D1 or DD1?  You can use Min and Max in this case to preserve one or the other.
0
 
tommym121Author Commented:
I am transferring unique data record from one to another table.  The duplicate one need further proceassing  based on  fileld1 and filed2 to extract a single record out of the duplicate.
0
 
tommym121Author Commented:
Thanks.
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now