Results 1 to 6 of 6
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Duplicates (Office XP Pro)

    Hi

    I have a code field with some duplicated numbers is there a query sql script to to select just these codes?

    Thanks in advance

    Braddy

    <img src=/S/brickwall.gif border=0 alt=brickwall width=25 height=15>
    If you are a fool at forty, you will always be a fool

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Duplicates (Office XP Pro)

    Build a grouping query that counts the number of records when grouped by code. Initially this will show how many of each. Then set a criterion of >1 on the count field.
    <pre>SELECT Table3.code, Count(Table3.key) AS CountOfkey
    FROM Table3
    GROUP BY Table3.code
    HAVING (((Count(Table3.key))>1));
    </pre>

    Regards
    John



  3. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Duplicates (Office XP Pro)

    Hi John

    Thank you very much for your reply It worked fine, but I wonder if I could impose on you further to tell me how to select other fields in the table aswell for I need to know if the duplicate codes have different prices.

    Novice User
    If you are a fool at forty, you will always be a fool

  4. #4
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Duplicates (Office XP Pro)

    You can't add the price field to that query because it only shows one record per code. Save that query then create another one;
    <pre>SELECT Table3.code, Table3.price
    FROM Table3 INNER JOIN Query1 ON Table3.code = Query1.code;
    </pre>

    I have called the first query query1. My new query uses both table3 and query1, joined on the code field.
    (Sorry about the dreadful names.)
    Regards
    John



  5. #5
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Duplicates (Office XP Pro)

    Hi John

    Thanks for your reply I am going to be away for a few days I wil try it when I get back.

    Thanks again

    Braddy
    If you are a fool at forty, you will always be a fool

  6. #6
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Duplicates (Office XP Pro)

    Hi John

    That is majestic just what I needed thank you very much.

    <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    If you are a fool at forty, you will always be a fool

Posting Permissions

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