comparing 1 table, need to identify only duplicates

  • Thread starter Thread starter Guest
  • Start date Start date
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)
 
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?
 
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?

lakerfan said:
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?


--
Sam Armas


John Spencer said:
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)
Click to expand...
Click to expand...
 
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,
--
Sam Armas


John Spencer said:
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?

lakerfan said:
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?


--
Sam Armas


John Spencer said:
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)



lakerfan wrote:

I want to compare values in 1 table and identify only duplicates
Sam Armas
Click to expand...
Click to expand...
Click to expand...
 
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)))




--
Sam Armas


John Spencer said:
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?

lakerfan said:
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?


--
Sam Armas


John Spencer said:
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)



lakerfan wrote:

I want to compare values in 1 table and identify only duplicates
Sam Armas
Click to expand...
Click to expand...
Click to expand...
 
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.

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)))



--
Sam Armas

John Spencer said:
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?

lakerfan said:
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?


--
Sam Armas


:

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)



lakerfan wrote:

I want to compare values in 1 table and identify only duplicates
Sam Armas
Click to expand...
Click to expand...
Click to expand...
 
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)))

--
Sam Armas


John Spencer said:
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.

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)))



--
Sam Armas

John Spencer said:
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,

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?


--
Sam Armas


:

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)



lakerfan wrote:

I want to compare values in 1 table and identify only duplicates
Sam Armas
Click to expand...
Click to expand...
Click to expand...
 
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
--
Sam Armas


John Spencer said:
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.

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)))



--
Sam Armas

John Spencer said:
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,

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?


--
Sam Armas


:

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)



lakerfan wrote:

I want to compare values in 1 table and identify only duplicates
Sam Armas
Click to expand...
Click to expand...
Click to expand...
 
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


lakerfan said:
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)))

--
Sam Armas


John Spencer said:
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.

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)))



--
Sam Armas

:

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,

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?


--
Sam Armas


:

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)



lakerfan wrote:

I want to compare values in 1 table and identify only duplicates
Sam Armas
Click to expand...
Click to expand...
Click to expand...
 
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
--
Sam Armas


John Spencer said:
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.

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)))



--
Sam Armas

John Spencer said:
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,

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?


--
Sam Armas


:

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)



lakerfan wrote:

I want to compare values in 1 table and identify only duplicates
Sam Armas
Click to expand...
Click to expand...
Click to expand...
 
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.

lakerfan said:
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
--
Sam Armas


John Spencer said:
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.

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)))



--
Sam Armas

:

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,

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?


--
Sam Armas


:

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)



lakerfan wrote:

I want to compare values in 1 table and identify only duplicates
Sam Armas
Click to expand...
Click to expand...
Click to expand...
 
Hello John,

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

Thanks,
--
Sam Armas


John Spencer said:
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.

lakerfan said:
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
--
Sam Armas


John Spencer said:
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.

lakerfan wrote:

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)))



--
Sam Armas

:

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,

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?


--
Sam Armas


:

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)



lakerfan wrote:

I want to compare values in 1 table and identify only duplicates
Sam Armas
Click to expand...
Click to expand...
Click to expand...
 
Back
Top