Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    Unanswered: Duplicate Record

    Hi guys how do you hide duplicate records, how would I do a select statement for that

    Code:
    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 of

  2. #2
    Join Date
    Jan 2006
    Location
    Singapore
    Posts
    47
    what do you want to do ?

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


  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i personally don't "hide" duplicate rows, i prevent them from happening
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    I inherited this database R937

    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)

    Quote Originally Posted by khtan
    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.

  5. #5
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    Unhappy Actually I dont want to hide them

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

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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 --
    Code:
    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 Milwaukee
    now how are we supposed to know which row to keep?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    Unhappy I'm so frustrated I could scream

    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
    Last edited by desireemm; 04-15-09 at 17:23.

  8. #8
    Join Date
    Jan 2006
    Location
    USA
    Posts
    115
    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.
    Rajesh Patel

    Everybody says - mistake is the first step of success, but it's not true. The correction of the mistake is the first step of success.

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Take your offending table, and run this query against just that table (no, joins, no fancy stuff, just this query alone):
    Code:
    SELECT Count(AccountNo), Count(DISTINCT AccountNo)
       FROM OffendingTable
    -PatP

  10. #10
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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

  11. #11
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    Hi Pat

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

  12. #12
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    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?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  13. #13
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    Heres the data

    Quote Originally Posted by Brett Kaiser
    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?

    Code:
    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
    Last edited by r937; 01-16-07 at 16:13.

  14. #14
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    The values I want

    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

  15. #15
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    sorry, that looked like really private information so i obfuscated it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •