Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    Feb 2006
    Posts
    71
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Have a table with single entry and also duplicates of order numbers. Looking for a way to query table to only show one instance of every order number, whether it is a duplicate or not.
    Creating a Duplicate Query displays the dups and not the single entry records

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    Use either a query with a distinct clause
    eg
    SELECT Distinct OrderNo FROM OrderTable .......

    or a query with a GROUP BY clause
    eg
    SELECT OrderNo, ....
    FROM OrderTable
    GROUP BY OrderNo

  3. #3
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts
    Quote Originally Posted by pillarcas View Post
    Have a table with single entry and also duplicates of order numbers. Looking for a way to query table to only show one instance of every order number, whether it is a duplicate or not.
    Creating a Duplicate Query displays the dups and not the single entry records
    What are you trying to show in your query? Just the Order Number field or many fields?

    To just show the Order Number field:

    SELECT DISTINCT OrderNumber from tblOrders

    To show many fields, maybe something like:

    SELECT OrderNumber, First(OrderDate) AS FirstOfOrderDate FROM tblOrders GROUP BY OrderNumber
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

Posting Permissions

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