Dublicate records find

[shrikant]

Hi all,

i want query to find duplicate records, i have two field in one table

Cusip and category

cusip and category are many or duplicates
but in one cusip category should be the same if not then provide the cusip which has different category used

like this
Cusip Category
123 R
456 P
123 R
456 P
678 Q
678 Q
123 A


result should be

Cusip Category
123 R
123 R
123 A

 

[plog]

You're gonna need a sub-query to identify duplicates. This is that SQL:

SELECT Cusip
FROM YourTableNameHere
GROUP BY Cusip
HAVING (((IIf(Max([Category])<>Min([Category]),1,0))=1));

Name the above query 'Duplicates_sub', then create a new query based on that which will give you your results. This is the final query SQL:

SELECT YourTableNameHere.*
FROM YourTableNameHere INNER JOIN Duplicates_sub ON YourTableNameHere.Cusip = Duplicates_sub.Cusip;

Be sure to replace all instances of 'YourTableNameHere' with the name of your actual table.

 

[shrikant]

Hi, Thanks for your reply really appreciate;
but while using the first query i.e. duplicate_sub; i am getting the error.

Erorr as Syntex Error (missing operator) in query expression 'Cusip'

I am trying to run this query from data SQL - DataDefinition.

Awaiting for the resolution.

Regards,
Shrikant:banghead:

 

[programmingmiss]

HI plog, you helped me before a I have a similar ? re: duplicates.

For a given field in 1 table, I want to select ONLY UNIQUE values in a query, I've been exporting a large file to Excel, where I cando this, but it would be more efficient to do in Access. Help? thx!!!

 

[plog]

shrikant--Can you post the exact SQL you are using to give you the error?

prog--start a new, I'm sure someone can give you the answer. From what you've posted, it sounds like google would be a good start.

 

[shrikant]

Hi Plog,

Once again thanks for quick response. Please find the below SQL coding.

Asset ID (Cusip)- is my One column
Eodfile – is my table
Category – is my another column


SELECT Asset ID
FROM Eodfile
GROUP BY Asset ID
HAVING (((IIf(Max([Category])<>Min([Category]),1,0))=1));

 

[plog]

Since you have spaces in your field name, you need to put brackets around it:

Asset ID -> [Asset ID]

Personally, I'd change it to remove the space.