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.
--
Thank you for your help

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



"Kirt84" <(E-Mail Removed)> wrote in message
news:B7962392-C87B-4D48-8A11-(E-Mail Removed)...
>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.
> --
> Thank you for your help

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


"JK" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>
>
>
> "Kirt84" <(E-Mail Removed)> wrote in message
> news:B7962392-C87B-4D48-8A11-(E-Mail Removed)...
>>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.
>> --
>> 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


"JK" wrote:

> 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
>
>
>
> "Kirt84" <(E-Mail Removed)> wrote in message
> news:B7962392-C87B-4D48-8A11-(E-Mail Removed)...
> >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.
> > --
> > Thank you for your help
>
>
>

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


"Wayne-I-M" wrote:

> 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
>
>
> "JK" wrote:
>
> > 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
> >
> >
> >
> > "Kirt84" <(E-Mail Removed)> wrote in message
> > news:B7962392-C87B-4D48-8A11-(E-Mail Removed)...
> > >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.
> > > --
> > > Thank you for your help
> >
> >
> >

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


"Kirt84" wrote:

> Where do i put this code?
> --
> Thank you for your help
>
>
> "Wayne-I-M" wrote:
>
> > 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
> >
> >
> > "JK" wrote:
> >
> > > 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
> > >
> > >
> > >
> > > "Kirt84" <(E-Mail Removed)> wrote in message
> > > news:B7962392-C87B-4D48-8A11-(E-Mail Removed)...
> > > >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.
> > > > --
> > > > Thank you for your help
> > >
> > >
> > >

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)

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

Thanks

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

--
Thank you for your help


"Wayne-I-M" wrote:

> 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)
>
> --
> Wayne
> Manchester, England.
> Enjoy whatever it is you do
>

Try this

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


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


"Kirt84" wrote:

> Thanks
>
> but I get an error
> "Syntax error (missing operator) in query expression 'ECMA WHERE
> Count(ECMA)>1"
>
> --
> Thank you for your help
>
>
> "Wayne-I-M" wrote:
>
> > 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)
> >
> > --
> > Wayne
> > Manchester, England.
> > Enjoy whatever it is you do
> >