Rene
|
| Posted: 07/22/2003, 1:12 AM |
|
Hi,
I would like to have an sql statement that lists duplicates in a table
I wanted to check only two fields of the table UserID, Activity
if they are two records with the same user and activity then it lists them
how can i do that
the primary key is an autonumber?
|
|
|
|
rrodgers
|
| Posted: 07/22/2003, 11:59 AM |
|
You don't say what the db is, so here is some info on mssql.
http://www.microsoft.com/technet/treeview/default.asp?u...ips/ivb9714.asp
rob
|
|
|
|
Rene
|
| Posted: 07/22/2003, 10:47 PM |
|
The database is access .. I just wanna know the logic of getting duplicate values .. not duplicate records .. just certain fields
|
|
|
|
Rene
|
| Posted: 07/22/2003, 10:47 PM |
|
The database is access .. I just wanna know the logic of getting duplicate values .. not duplicate records .. just certain fields
|
|
|
|
IT Farmer
|
| Posted: 07/23/2003, 4:56 PM |
|
Rene,
Primary Key is autonumber that only applies for that table, if you want to use that as a relationshipe between two tables you will run in problems (i think).
SELECT Table1.UserID, Table1.Activity, Table1.Autonumber
FROM Table1
WHERE (((Table1.UserID) In (SELECT [UserID] FROM [Table1] As Tmp GROUP BY [UserID],[Activity] HAVING Count(*)>1 And [Activity] = [Table1].[Activity])))
ORDER BY Table1.UserID, Table1.Activity;
That should do it.
This finds the records where UserID and Activity Are duplicate
|
|
|
|
IT Farmer
|
| Posted: 07/23/2003, 4:56 PM |
|
Rene,
Primary Key is autonumber that only applies for that table, if you want to use that as a relationshipe between two tables you will run in problems (i think).
SELECT Table1.UserID, Table1.Activity, Table1.Autonumber
FROM Table1
WHERE (((Table1.UserID) In (SELECT [UserID] FROM [Table1] As Tmp GROUP BY [UserID],[Activity] HAVING Count(*)>1 And [Activity] = [Table1].[Activity])))
ORDER BY Table1.UserID, Table1.Activity;
That should do it.
This finds the records where UserID and Activity Are duplicate in table1
|
|
|
|
|
|
