[SOLVED]Pulling duplicates
Moderator: General Moderators
- evilmonkey
- Forum Regular
- Posts: 823
- Joined: Sun Oct 06, 2002 1:24 pm
- Location: Toronto, Canada
[SOLVED]Pulling duplicates
Hello,
I need a query that will pull duplicates from a table set up like this:
FirstName || LastName || UserID
Since UserID cannot be duplicated (but sometimes is in my case), I need to know what those are so I can deal with them. The problem is, there are tonnes of ways to weed duplicated out of queries, but I have no idea how to structure a query to specifically show duplicates. I'm using JET (MS ACCESS), but SQL is SQL, so a point in the right direction would be helpful.
Thanks!
I need a query that will pull duplicates from a table set up like this:
FirstName || LastName || UserID
Since UserID cannot be duplicated (but sometimes is in my case), I need to know what those are so I can deal with them. The problem is, there are tonnes of ways to weed duplicated out of queries, but I have no idea how to structure a query to specifically show duplicates. I'm using JET (MS ACCESS), but SQL is SQL, so a point in the right direction would be helpful.
Thanks!
Last edited by evilmonkey on Fri Feb 16, 2007 1:12 pm, edited 1 time in total.
- Kieran Huggins
- DevNet Master
- Posts: 3635
- Joined: Wed Dec 06, 2006 4:14 pm
- Location: Toronto, Canada
- Contact:
try something like:
(untested, might not work at all, but it's a starting point)
Code: Select all
SELECT * FROM table WHERE COUNT('UserID')>1 GROUP BY UserId- evilmonkey
- Forum Regular
- Posts: 823
- Joined: Sun Oct 06, 2002 1:24 pm
- Location: Toronto, Canada
Sorry mate, your idea didn't work. I found this while scouring the internet:
Any comments? THe only problem is, I cannot select the rest of the rows (i.e. the FirstName and LastName) rows because Access complains about them now being in the count().
Code: Select all
select UserID, count(UserID) from TableName
group by UserID
having count(*) > 1Yes.evilmonkey wrote:Sorry mate, your idea didn't work. I found this while scouring the internet:
Any comments?Code: Select all
select UserID, count(UserID) from TableName group by UserID having count(*) > 1
Change:
Code: Select all
count(*)Code: Select all
count(UserID)- evilmonkey
- Forum Regular
- Posts: 823
- Joined: Sun Oct 06, 2002 1:24 pm
- Location: Toronto, Canada
I did that, but UseerID is actually not a primary key. In the production world it will be (and won't be using access), but for now I'm trying to work through the problems in the data I was given. For instance, why the heck are there duplicate UserID's when UserID is suppossed to be (in the real world) primary. But yes, I did change as per your suggestion.
- evilmonkey
- Forum Regular
- Posts: 823
- Joined: Sun Oct 06, 2002 1:24 pm
- Location: Toronto, Canada
Okay, to avaoid confusion: I have data that needs to be migrated. Data has certain problems with it, including duplicate UserID's (I have no idea how that happenned, nor does it really concern me at this point). I have to find those problems and fix them to make sure that our techs can properly migrate the data.
- evilmonkey
- Forum Regular
- Posts: 823
- Joined: Sun Oct 06, 2002 1:24 pm
- Location: Toronto, Canada