Duplicate Record

Hi guys how do you hide duplicate records, how would I do a select statement for that
In (SELECT [AccountNo] FROM [2006_CheckHistory] As Tmp GROUP BY [AccountNo] HAVING Count(*)>1 )
I have about had it with this database I have been asked to make a report out ofwhat do you want to do ?

Lists only unique AccountNo ?
select distinct AccountNo from [2006_CheckHistory]|||i personally don't "hide" duplicate rows, i prevent them from happening|||so there was nothing I could do about the duplicates, they are making the query difficult to create, and this thing is driving me crazy. Some took data and just threw it into a database and called it a day (was a VB programmer)

what do you want to do ?

Lists only unique AccountNo ?
select distinct AccountNo from [2006_CheckHistory]

I'm trying to make the AccountNo unique, you dont need it entered more then once in a table.|||I want to delete them I just want to create a query that shows me all the duplicates and then delete them, leaving only one AccountNo instead of several. Does that make sense??|||okay, you want one row per AccountNo

what about the other columns? which values would you like from those?

here's an example of the problem you could be facing --
AccountNo Name Age City
1234 Fred 23 Chicago
1234 Fred 23 Waukegan
1234 Fred 26 Chicago
1234 Todd 23 Chicago
5678 Mary 16 Milwaukee
5678 Mary 15 Milwaukee
5678 Mary 17 Milwaukeenow how are we supposed to know which row to keep?|||Yeah, the problem that I need to work around is when I have it as a report I cant create a total per AccountNo it just totals it for all the records rather then per account... like if there is an account for number 000111222 then I cant get the total amount for just that record. I need one record to show and its total for that year and this is such a BIG PAIN. I didnt create this database so its a matter of some serious fixing. The problem is gettign this done before the 22nd of January...ACCCKKKK HELP PLEASE!!!!!!!!!!!!!!

Then as soon as I'm done I'm going to do some serious restructuring|||It seems that you are talking about one table, but think about those tables which are related with this one.

Whatever you plan, plan considering all related tables in the database.|||Take your offending table, and run this query against just that table (no, joins, no fancy stuff, just this query alone):SELECT Count(AccountNo), Count(DISTINCT AccountNo)
FROM OffendingTable-PatP|||Oh yeah, while you are at it could you explain what you consider to be a "duplicate record" ? Does that mean that all columns in more than one row are identical? Does it mean that one column (AccontNo) in more than one row are identical? Does it mean something completely different that I haven't thought of yet?

-PatP|||Thank you so much guys,

It means that there is identical [AccountNo] in the table, these are businesses, now when the business changes ownership they indicate that with a letter like A, B..etc, or if theres equipment at that business thats indicated with a letter. I was thinking of making the AccountNo Unique by getting rid of the duplicates and making it unique. And having a seperate table with the A, B and the date they changed hands linked to the AccountNo table, since this is table would reflect changes to the [AccountNo] table. The [AccountNo] table would have the information concering the business, like address, name of business..etc. Does that make sense??|||Only problem is what about the other columns...which values do you want? Does it matter to you? It usually does.

Post the DDL of the table and some sample data, and what you want in the final table...

And what are the report req's?|||Only problem is what about the other columns...which values do you want? Does it matter to you? It usually does.

Post the DDL of the table and some sample data, and what you want in the final table...

And what are the report req's?

OwnerManagerID AccountNo CheckDate CheckNumber CheckAmount OwnerAddress1 NameFor1099 OwnerCity OwnerState OwnerZip
A 00010002 2006-09-20 37065 $621.75 11070 BLAH-DE-BLAH FOL-DER-OL EUREKA KS 90120-1234
A 00010002 2006-03-20 19006 $679.25 11070 BLAH-DE-BLAH FOL-DER-OL EUREKA KS 90120-1234
A 00010002 2006-10-23 38341 $587.13 11070 BLAH-DE-BLAH FOL-DER-OL EUREKA KS 90120-1234
A 00010002 2006-02-16 28548 $723.38 11070 BLAH-DE-BLAH FOL-DER-OL EUREKA KS 90120-1234
A 00010002 2006-06-20 33119 $994.00 11070 BLAH-DE-BLAH FOL-DER-OL EUREKA KS 90120-1234
A 00010002 2006-08-23 35806 $578.75 11070 BLAH-DE-BLAH FOL-DER-OL EUREKA KS 90120-1234
A 00010002 2006-07-24 34551 $488.50 11070 BLAH-DE-BLAH FOL-DER-OL EUREKA KS 90120-1234
A 00010002 2006-05-18 31820 $573.50 11070 BLAH-DE-BLAH FOL-DER-OL EUREKA KS 90120-1234
A 00010002 2006-04-21 30734 $582.88 11070 BLAH-DE-BLAH FOL-DER-OL EUREKA KS 90120-1234
A 00010002 2006-12-20 40892 $614.00 11070 BLAH-DE-BLAH FOL-DER-OL EUREKA KS 90120-1234
A 00010002 2006-01-17 24366 $427.75 11070 BLAH-DE-BLAH FOL-DER-OL EUREKA KS 90120-1234
A 00010002 2006-11-17 39614 $730.00 11070 BLAH-DE-BLAH FOL-DER-OL EUREKA KS 90120-1234
A 00010004 2006-06-22 23676 $1,972.79 123 SESAME STREET #937 LUXE SUITES DRY GULCH TX 90937-0937
A 00010004 2006-02-09 23644 $1,737.02 123 SESAME STREET #937 LUXE SUITES DRY GULCH TX 90937-0937
A 00010004 2006-03-10 23660 $1,632.50 123 SESAME STREET #937 LUXE SUITES DRY GULCH TX 90937-0937
A 00010004 2006-05-31 32492 $1,586.44 123 SESAME STREET #937 LUXE SUITES DRY GULCH TX 90937-0937
A 00010004 2006-09-12 36608 $1,778.60 123 SESAME STREET #937 LUXE SUITES DRY GULCH TX 90937-0937
A 00010004 2006-07-20 34540 $1,715.68 123 SESAME STREET #937 LUXE SUITES DRY GULCH TX 90937-0937
A 00010004 2006-04-11 30257 $1,951.40 123 SESAME STREET #937 LUXE SUITES DRY GULCH TX 90937-0937
A 00010004 2006-08-07 35355 $1,362.58 123 SESAME STREET #937 LUXE SUITES DRY GULCH TX 90937-0937
A 00010004 2006-01-06 25876 $1,834.49
A 00010004 2006-12-06 40426 $1,647.72 123 SESAME STREET #937 LUXE SUITES DRY GULCH TX 90937-0937
A 00010004 2006-11-13 39605 $1,795.44 123 SESAME STREET #937 LUXE SUITES DRY GULCH TX 90937-0937
A 00010004 2006-11-06 39164 $1,524.11 123 SESAME STREET #937 LUXE SUITES DRY GULCH TX 90937-0937|||I want in the ownermanager_table Ownermanagerid, Address, city, state zip..ect, AccountNo, fedtaxid.

That should be one table and the other table should be the check amount, checkdate, name of bank..etc|||sorry, that looked like really private information so i obfuscated it|||Thank you appreciate it|||WOOHOO I GOT THE QUERY I NEEDED ITS OVER THIS HORRIBLE NIGHTMARE IS OVER....WOOOHOOOOOOOO!!!!!!!!!!!!!!!!!!!

I'M SO HAPPY I COULD SCREAM...Good grief this person just took live DATA and stuck in a table and just called it a day, no documentation no nothing. The whole thing is structured so badly its a nightmare...EEEEEEEEEEEEEEEEEE!!!!!!!!!!!!!!!!!!!! !!!!!!!!!!

Thank you guys