comparing 1 table, need to identify only duplicates

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

lakerfan - 29 Nov 2005 19:26 GMT

I want to compare values in 1 table and identify only duplicates
Sam Armas

Reply to this Message

John Spencer - 29 Nov 2005 22:34 GMT

Have you tried using the query wizard for Duplicate queries?

The SQL that would be generated would look something like

SELECT Tablename.*
FROM TableName
WHERE TheDuplicateField IN
(SELECT TheDuplicateField
FROM TableName
GROUP BY TheDuplicateField
HAVING COUNT(TheDuplicateField) > 1)

> I want to compare values in 1 table and identify only duplicates
> Sam Armas

Reply to this Message

lakerfan - 30 Nov 2005 17:20 GMT

Hello John,

I entered the sequel script below and are getting a strange message, can you
explain the message?

SELECT [USR_UID_Values] AS Expr1, USR_UID_Values.USR
FROM (USR_UID_Values)
WHERE (((USR_UID_Values.USR) In (SELECT USR FROM (USR_UID_Values) GROUP BY
USR HAVING COUNT(USR)>1)))

MESSAGE: ENTER PARAMETER VALUE?

AND THAN IT IDENTIFIES THE TABLE NAME ABOVE (USR_UID_Values), I don't
understand the value its looking for?

Signature

Sam Armas

> Have you tried using the query wizard for Duplicate queries?
>
[quoted text clipped - 10 lines]
> > I want to compare values in 1 table and identify only duplicates
> > Sam Armas

Reply to this Message

John Spencer - 30 Nov 2005 17:44 GMT

Hmm. I would expect to see [] around field and table names, and not ()

Do you have a field named USR_UID_Values. You seem to have a table with
that name? Guessing that you want all the fields from the table.

SELECT [USR_UID_Values].*
FROM USR_UID_Values
WHERE USR_UID_Values.USR In (SELECT TMP.USR FROM USR_UID_Values as TMP GROUP
BY
TMP.USR HAVING COUNT(TMP.USR)>1)

MESSAGE: ENTER PARAMETER VALUE?

AND THAN IT IDENTIFIES THE TABLE NAME ABOVE (USR_UID_Values), I don't
understand the value its looking for?

> Hello John,
>
[quoted text clipped - 26 lines]
>> > I want to compare values in 1 table and identify only duplicates
>> > Sam Armas

Reply to this Message

lakerfan - 30 Nov 2005 18:40 GMT

Hello John,

Did you even read the email, the answer you provided is the same for the
original question, please review and let me know if you have any insight.

Thanks,

Signature

Sam Armas

> Hmm. I would expect to see [] around field and table names, and not ()
>
[quoted text clipped - 42 lines]
> >> > I want to compare values in 1 table and identify only duplicates
> >> > Sam Armas

Reply to this Message

lakerfan - 30 Nov 2005 21:51 GMT

I update the script with your recommendations about the bracket vs
parenthesis, I now have syntex errors as described below.

Also, you are right about the table and field:

Table: USR_UID_VALUES
Field: USR

Error Message: On the line below

WHERE (([USR_UID_Values.USR] In (SELECT[USR] FROM [USR_UID_Values] GROUP BY
USR HAVING COUNT[USR]>1)))

Signature

Sam Armas

> Hmm. I would expect to see [] around field and table names, and not ()
>
[quoted text clipped - 42 lines]
> >> > I want to compare values in 1 table and identify only duplicates
> >> > Sam Armas

Reply to this Message

John Spencer - 01 Dec 2005 00:23 GMT

Yes, I read your message and I did try to give you some guidance.

Your brackets are wrong in the where clause. you should have
[USR_UID_Values].[USR] and not [USR_UID_Values.USR]

Also you seem to be missing a space in the sub-query between SELECT and [USR]

And you are missing parentheses in Count([Usr])>1

WHERE (([USR_UID_Values].[USR] In (SELECT [USR] FROM [USR_UID_Values] GROUP BY
USR HAVING COUNT([USR])>1)))

I think the above is sytactically correct. Try it and if you get another error,
post back.

> I update the script with your recommendations about the bracket vs
> parenthesis, I now have syntex errors as described below.
[quoted text clipped - 61 lines]
> > >> > I want to compare values in 1 table and identify only duplicates
> > >> > Sam Armas

Reply to this Message

lakerfan - 01 Dec 2005 14:52 GMT

Hello John, I made the changes you suggested and are having the VALUE PROBLEM
that we initially had. A pop up window comes up and asks for a Parameter
Value to be included in the USR_UID_Values table.

I don't understand what value its looking for?

I tried entrying random values 2, 30, 64 with no positive results.

SELECT [USR_UID_Values] AS Expr1, USR_UID_Values.USR
FROM [USR_UID_Values]
WHERE (([USR_UID_Values].[USR] In (SELECT [USR] FROM [USR_UID_Values] GROUP
BY USR HAVING COUNT([USR])>1)))

Signature

Sam Armas

> Yes, I read your message and I did try to give you some guidance.
>
[quoted text clipped - 76 lines]
> > > >> > I want to compare values in 1 table and identify only duplicates
> > > >> > Sam Armas

Reply to this Message

John Spencer - 01 Dec 2005 15:47 GMT

Do you have a FIELD named USR_UID_Values in the TABLE USR_UID_Values?
If not, then that is what is being asked for.

You can reach me at the mail domain

CHPDM DOT UMBC dot EDU

Put SPENCER in front of the domain name

> Hello John, I made the changes you suggested and are having the VALUE
> PROBLEM
[quoted text clipped - 101 lines]
>> > > >> > I want to compare values in 1 table and identify only duplicates
>> > > >> > Sam Armas

Reply to this Message

lakerfan - 01 Dec 2005 14:57 GMT

Hello John,

Also, I have a Hypersnap of the script and error. If you give me your email
address, I will send it over to you as an attached document.

Sam

Signature

Sam Armas

> Yes, I read your message and I did try to give you some guidance.
>
[quoted text clipped - 76 lines]
> > > >> > I want to compare values in 1 table and identify only duplicates
> > > >> > Sam Armas

Reply to this Message

lakerfan - 01 Dec 2005 19:58 GMT

Hello John,

I have to fields in the table (USR, UID). I entered both of them.

All I am trying to do is compare the USR Field to determine if there are any
duplicates, I didn't realize it was so complicated.

I entered both of those table entries in the pop up window with no error,
but also, no results.

Help

Signature

Sam Armas

> Yes, I read your message and I did try to give you some guidance.
>
[quoted text clipped - 76 lines]
> > > >> > I want to compare values in 1 table and identify only duplicates
> > > >> > Sam Armas

Reply to this Message

John Spencer - 02 Dec 2005 12:49 GMT

Let's back up and start over.

In the database window, on the queries tab
Select Insert: Query from the Menu
In the dialog box, Select Find Duplicates Query Wizard
In the next window, select your Table "[USR_UID_Values]
In the next window, select the field "Usr" (if that is the field with
duplicates)
In the next window, select any additional fields you want to display.
In the next window, Name your query and click Finish

That should build and run the query for you.

I mentioned using the Query Wizard in the first line of my first response.
I included the code example, in case your wizard was "incompetent" ;-). By
that I mean some installations have problems with the query wizards working.

Hope this solves the problem.

> Hello John,
>
[quoted text clipped - 99 lines]
>> > > >> > I want to compare values in 1 table and identify only duplicates
>> > > >> > Sam Armas

Reply to this Message

lakerfan - 02 Dec 2005 15:00 GMT

Hello John,

Thanks for your help, I went to the Wizard last night and it definitely
worked. We can close this one out,

Thanks,

Signature

Sam Armas

> Let's back up and start over.
>
[quoted text clipped - 118 lines]
> >> > > >> > I want to compare values in 1 table and identify only duplicates
> >> > > >> > Sam Armas

Reply to this Message