display duplicate values in column

[Guest]

I have a table with Approx 900 records. I want to be able to filter all
duplicate values that exist in a column (ECMA). This is because I want to
make this Field the Primary key.

 

[JK]

Kirt,

This should work:


SELECT [ECMA], ... some other fields .. FROM [YrTable]
WHERE DCount("[ECMA]","YourTable","[ECMA]=" & [ECMA])>1
ORDER BY ... whatever ...;

SELECT Countries.Country, Countries.DST_ID
FROM Countries
WHERE DCount("[DST_ID]","Countries","[DST_ID]=" & [DST_ID])>1
ORDER BY Countries.DST_ID;

Regards/JK

 

[JK]

It just occurred to me,

If you want to make [ECMA] a primary key you will also need to test for Null
values in it, if relevant:


SELECT [ECMA], ... some other fields .. FROM [YourTable]
WHERE DCount("[ECMA]","YourTable","[ECMA]=" & [ECMA])>1 OR IsNull([ECMA])
ORDER BY ... whatever ...;


Regards/JK

Kirt,

This should work:


SELECT [ECMA], ... some other fields .. FROM [YrTable]
WHERE DCount("[ECMA]","YourTable","[ECMA]=" & [ECMA])>1
ORDER BY ... whatever ...;

SELECT Countries.Country, Countries.DST_ID
FROM Countries
WHERE DCount("[DST_ID]","Countries","[DST_ID]=" & [DST_ID])>1
ORDER BY Countries.DST_ID;

Regards/JK

I have a table with Approx 900 records. I want to be able to filter all
duplicate values that exist in a column (ECMA). This is because I want to
make this Field the Primary key.

 

[Guest]

Hi Kirt

You could KJ's excellent answer or

SELECT COUNT(ECMA)
AS SomeName FROM TableName;

This has the benefit of showing which records are relevant. You can then
alter the duplicate entries. The reason for this is that the primary key
really should not be something that can be altered by a user (the primary key
is best left hidden and is normally best used simply as a unique reference to
identify specific records).

Hope this helps


--
Wayne
Manchester, England.
Enjoy whatever it is you do

Kirt,

This should work:


SELECT [ECMA], ... some other fields .. FROM [YrTable]
WHERE DCount("[ECMA]","YourTable","[ECMA]=" & [ECMA])>1
ORDER BY ... whatever ...;

SELECT Countries.Country, Countries.DST_ID
FROM Countries
WHERE DCount("[DST_ID]","Countries","[DST_ID]=" & [DST_ID])>1
ORDER BY Countries.DST_ID;

Regards/JK

I have a table with Approx 900 records. I want to be able to filter all
duplicate values that exist in a column (ECMA). This is because I want to
make this Field the Primary key.

 

[Guest]

Where do i put this code?
--
Thank you for your help

Hi Kirt

You could KJ's excellent answer or

SELECT COUNT(ECMA)
AS SomeName FROM TableName;

This has the benefit of showing which records are relevant. You can then
alter the duplicate entries. The reason for this is that the primary key
really should not be something that can be altered by a user (the primary key
is best left hidden and is normally best used simply as a unique reference to
identify specific records).

Hope this helps


--
Wayne
Manchester, England.
Enjoy whatever it is you do

Kirt,

This should work:


SELECT [ECMA], ... some other fields .. FROM [YrTable]
WHERE DCount("[ECMA]","YourTable","[ECMA]=" & [ECMA])>1
ORDER BY ... whatever ...;

SELECT Countries.Country, Countries.DST_ID
FROM Countries
WHERE DCount("[DST_ID]","Countries","[DST_ID]=" & [DST_ID])>1
ORDER BY Countries.DST_ID;

Regards/JK

I have a table with Approx 900 records. I want to be able to filter all
duplicate values that exist in a column (ECMA). This is because I want to
make this Field the Primary key.

 

[Guest]

Its just a section of the sql.

I have thought about another way. This should only produce results where
there is more than 1 record with the same data in a field. (you can change
the >1 to >2, >3 or whatever to get different results)

Create a new query.
Insert the table containing the data.
Click View at the top of the page.
Select SQL view
Insert this- - -
Select Email, Count(ECMA) FROM TableName Group By ECMA WHERE Count(ECMA)

1

Of course you will need to change the TableName to whatever it is.

Just to note this query will not do anything other than show you which ECMA
fields you may want to alter. Of course you can still delete them - that’s
up to you - but I have found that letting any code (that I have written)
simply delete records in normally a big mistake - especially with me coding


Anyway – give it a try and then decide what you want to do after you can
see the results.

Hope this helps




--
Wayne
Manchester, England.
Enjoy whatever it is you do

Where do i put this code?
--
Thank you for your help

Hi Kirt

You could KJ's excellent answer or

SELECT COUNT(ECMA)
AS SomeName FROM TableName;

This has the benefit of showing which records are relevant. You can then
alter the duplicate entries. The reason for this is that the primary key
really should not be something that can be altered by a user (the primary key
is best left hidden and is normally best used simply as a unique reference to
identify specific records).

Hope this helps


--
Wayne
Manchester, England.
Enjoy whatever it is you do

Kirt,

This should work:


SELECT [ECMA], ... some other fields .. FROM [YrTable]
WHERE DCount("[ECMA]","YourTable","[ECMA]=" & [ECMA])>1
ORDER BY ... whatever ...;

SELECT Countries.Country, Countries.DST_ID
FROM Countries
WHERE DCount("[DST_ID]","Countries","[DST_ID]=" & [DST_ID])>1
ORDER BY Countries.DST_ID;

Regards/JK



I have a table with Approx 900 records. I want to be able to filter all
duplicate values that exist in a column (ECMA). This is because I want to
make this Field the Primary key.

 

[Guest]

ooops - typo - (made to to many junk e mails programmes for clients (not
meant to call em junk though - new word is "targeted mails") Not my fault -
I just write the code.

Should be

Select ECMA, Count(ECMA) FROM TableName Group By ECMA WHERE Count(ECMA)

 

[Guest]

Thanks

but I get an error
"Syntax error (missing operator) in query expression 'ECMA WHERE
Count(ECMA)>1"

 

[Guest]

Try this

SELECT ECMA
FROM TableName GROUP BY ECMA HAVING COUNT(ECMA) > 1