Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2008
    Posts
    13

    Unanswered: Help - Report SQL question

    Hi, I need help to find the SQL on how to group this type of data.

    I basically need to create a report that finds all duplicates in a table and print them out on a report. I have tried Groupby option but that will not show me all the dups it will only show me the 1 record that is a dup but I need to show that data.

    Can anyone help? Thanks!

    It should show up like this.


    7/16/00 11:12:52 PM
    010017 CESAR CASTILLO INC
    319730 44982974 01/13/98 1,226.99 22203957 02/23/98 2,955.11 438
    337130 50323811 07/01/98 1,226.99 22490966 07/31/98 5,906.30 797
    328911 47843912 04/13/98 984.00 22349650 05/14/98 2,033.83 65
    329622 47932059 04/21/98 984.00 22360609 05/21/98 5,498.43 891
    322376 45753356 02/11/98 712.80 22245536 03/17/98 2,463.34 997
    326199 46575314 03/17/98 712.80 22298114 04/16/98 855.76 997
    327453 46890429 03/26/98 504.00 22313598 04/27/98 2,686.68 199
    328936 47643783 04/13/98 504.00 22347259 05/13/98 8,218.69 217
    331199 48520080 05/05/98 504.00 22383183 06/04/98 6,860.54 797
    334442 49292093 06/04/98 504.00 22436933 07/06/98 2,415.82 612
    Last edited by mrseller; 09-07-08 at 22:48.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Make your duplicate query a subquery and join your data back to it
    Code:
    SELECT *
    FROM   table1 t1
     INNER
      JOIN (
            SELECT col1
            FROM   table1
            GROUP
                BY col1
            HAVING Count(*) > 1
           ) As [duplicates]
        ON t1.col1 = duplicates.col1
    George
    Home | Blog

  3. #3
    Join Date
    Sep 2008
    Posts
    13

    Grouping

    Thanks for the reply. The thing is, I need to group this report by vender but if the vender only has 1 set of duplicates then skip that record. It's pretty confusing I know!

    Does the below sql work in a recordset? Do I need to extract the duplicates first then do a sub query on the main sql? Any help would be great.

    Thanks!

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Try run the subquery on its own
    Code:
            SELECT col1
            FROM   table1
            GROUP
                BY col1
            HAVING Count(*) > 1
    George
    Home | Blog

  5. #5
    Join Date
    Sep 2008
    Posts
    13

    Another Question

    I have another question that relates to the above query. I have an Amount field that I need to check for dups BUT... if the dups are a negative or positive same number amount, I need to treat them the same. I have tried the Abs conversion but it does not work in the SQL, it does not even come out in the results, ex. 182400 and -182400 these have to show up in the report as if were a match. Thanks for everyone that has helped so far!

  6. #6
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Abs doesn't work in SQL? I find that hard to believe!

    Perhaps if you could show us this non-working SQL?
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

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