Is there a way to search on a particular field and see if
there are duplicate entries in a data table?




Fred

Fred Atkinson wrote:
> Is there a way to search on a particular field and see if
> there are duplicate entries in a data table?

You could use:

SELECT COUNT(*) AS Times, <column> FROM <table> GROUP BY <column> HAVING
Times>1 ORDER BY Times DESC;

You will get each value that is more than once in the table, with the number
of times it appears.

Remember to change the <column> and <table> to the values you want to use.


--

//Aho

On Fri, 21 Mar 2008 12:03:51 +0100, "J.O. Aho" <user@example.net>
wrote:

>SELECT COUNT(*) AS Times, <column> FROM <table> GROUP BY <column> HAVING
>Times>1 ORDER BY Times DESC;


Great. I was able to clean up my data considerably with that
syntax. Thank you very much.


Is there any way to find records in a table that has one field
of a table is NOT a subset of another field ignoring the case
altogether?

For example:

Field 1 Field 2
as John turns around JOHN
Garry lost his watch Gerry


The first record (JOHN) would be a match. Therefore, it would
NOT be returned. Therefore, it would NOT be returned in the query.

The second record (Gerry) would not be a match because Garry
is not equal to Gerry. Therefore, it WOULD be returned in the query.
I'm looking only for records that field 2 is not contained in
field 1.

Regards,




Fred

Fred Atkinson <fatkinson@mishmash.com> wrote in
news:uvkau35mva5ecs6c70ihm6gnanv5qh0b9s@4ax.com:

> On Fri, 21 Mar 2008 12:03:51 +0100, "J.O. Aho" <user@example.net>
> wrote:
>
>>SELECT COUNT(*) AS Times, <column> FROM <table> GROUP BY <column>
>>HAVING Times>1 ORDER BY Times DESC;
>
>
> Great. I was able to clean up my data considerably with that
> syntax. Thank you very much.
>
>
> Is there any way to find records in a table that has one field
> of a table is NOT a subset of another field ignoring the case
> altogether?
>
> For example:
>
> Field 1 Field 2
> as John turns around JOHN
> Garry lost his watch Gerry
>
>
> The first record (JOHN) would be a match. Therefore, it would
> NOT be returned. Therefore, it would NOT be returned in the query.
>
> The second record (Gerry) would not be a match because Garry
> is not equal to Gerry. Therefore, it WOULD be returned in the query.
> I'm looking only for records that field 2 is not contained in
> field 1.
>
> Regards,
>
>
>
>
> Fred
>
>
>
>

Yes, there is a way with a simple SELECT statement.
Why do your questions make me think of homework assignments?

On Sat, 22 Mar 2008 23:57:58 GMT, "Ana C. Dent"
<anacedent@hotmail.com> wrote:

>Fred Atkinson <fatkinson@mishmash.com> wrote in
>news:uvkau35mva5ecs6c70ihm6gnanv5qh0b9s@4ax.com :
>
>> On Fri, 21 Mar 2008 12:03:51 +0100, "J.O. Aho" <user@example.net>
>> wrote:
>>
>>>SELECT COUNT(*) AS Times, <column> FROM <table> GROUP BY <column>
>>>HAVING Times>1 ORDER BY Times DESC;
>>
>>
>> Great. I was able to clean up my data considerably with that
>> syntax. Thank you very much.
>>
>>
>> Is there any way to find records in a table that has one field
>> of a table is NOT a subset of another field ignoring the case
>> altogether?
>>
>> For example:
>>
>> Field 1 Field 2
>> as John turns around JOHN
>> Garry lost his watch Gerry
>>
>>
>> The first record (JOHN) would be a match. Therefore, it would
>> NOT be returned. Therefore, it would NOT be returned in the query.
>>
>> The second record (Gerry) would not be a match because Garry
>> is not equal to Gerry. Therefore, it WOULD be returned in the query.
>> I'm looking only for records that field 2 is not contained in
>> field 1.
>>
>> Regards,
>>
>>
>>
>>
>> Fred
>>
>>
>>
>>
>
>Yes, there is a way with a simple SELECT statement.
>Why do your questions make me think of homework assignments?

I couldn't answer that question. I'm just trying to clean up
a database I have constructed.

Regards,



Fred

Fred Atkinson <fatkinson@mishmash.com> wrote in
news:7libu3hlmgoe57duu07q0nn8kscoufufou@4ax.com:

> On Sat, 22 Mar 2008 23:57:58 GMT, "Ana C. Dent"
> <anacedent@hotmail.com> wrote:
>
>>Fred Atkinson <fatkinson@mishmash.com> wrote in
>>news:uvkau35mva5ecs6c70ihm6gnanv5qh0b9s@4ax.co m:
>>
>>> On Fri, 21 Mar 2008 12:03:51 +0100, "J.O. Aho" <user@example.net>
>>> wrote:
>>>
>>>>SELECT COUNT(*) AS Times, <column> FROM <table> GROUP BY <column>
>>>>HAVING Times>1 ORDER BY Times DESC;
>>>
>>>
>>> Great. I was able to clean up my data considerably with that
>>> syntax. Thank you very much.
>>>
>>>
>>> Is there any way to find records in a table that has one field
>>> of a table is NOT a subset of another field ignoring the case
>>> altogether?
>>>
>>> For example:
>>>
>>> Field 1 Field 2
>>> as John turns around JOHN
>>> Garry lost his watch Gerry
>>>
>>>
>>> The first record (JOHN) would be a match. Therefore, it would
>>> NOT be returned. Therefore, it would NOT be returned in the query.
>>>
>>> The second record (Gerry) would not be a match because Garry
>>> is not equal to Gerry. Therefore, it WOULD be returned in the
query.
>>> I'm looking only for records that field 2 is not contained in
>>> field 1.
>>>
>>> Regards,
>>>
>>>
>>>
>>>
>>> Fred
>>>
>>>
>>>
>>>
>>
>>Yes, there is a way with a simple SELECT statement.
>>Why do your questions make me think of homework assignments?
>
> I couldn't answer that question. I'm just trying to clean up
> a database I have constructed.
>
> Regards,
>
>
>
> Fred
>

I suggest if you are serious about doing thing the right way within the
database, then you should read up on Third Normal Form.

On Sun, 23 Mar 2008 03:26:59 GMT, "Ana C. Dent"
<anacedent@hotmail.com> wrote:

>Third Normal Form

That seems like a good thing to learn. But I don't think that
it would do what I'm seeking to do here.

If anyone can tell me how to query to do this, I'd really be
grateful.

Regards,



Fred