Results 1 to 4 of 4
  1. #1
    vaiofoxx1 is offline Novice
    Windows XP Access 2007
    Join Date
    Mar 2012
    Posts
    2

    Sql code for Distinct column and two other Columns

    I'm trying to return three columns of information with one column being distinct values and the other two columns of data of the distinct values only. I'm using access and was wondering if the column I'm trying to get Distinct is affected by being alphanumeric.

    SELECT Order Num, Trade Date,Cost Center Code,Man-Semi-Auto
    FROM 2011 Report
    WHERE Order Num IN (SELECT DISTINCT Order Num FROM 2011 Report);



    I though this would work but it is not returning Distinct or unique entries

  2. #2
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    The subquery has DISTINCT, but the main query doesn't. Maybe if you could post an example of the source data and your desired result, we can recommend a method.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    vaiofoxx1 is offline Novice
    Windows XP Access 2007
    Join Date
    Mar 2012
    Posts
    2
    Quote Originally Posted by pbaldy View Post
    The subquery has DISTINCT, but the main query doesn't. Maybe if you could post an example of the source data and your desired result, we can recommend a method.

    Here is some sample data

    LTAZY6 40878 Sam manual
    LTAYY3 40878 roger auto
    LTBAG0 40889 roger manual
    LTAQS1 40878 Bob manual
    LTAZY6 40878 Sam manual
    LTAYY3 40878 roger auto
    LTBAG0 40889 roger manual
    LTAZY6 40878 Sam manual
    LTAYY3 40878 roger auto
    LTBBP4 40878 Chris manual
    LTAZY6 40878 Sam manual

    I would like it to show
    LTAZY6 12/1/2011 Sam manual
    LTAYY3 12/1/2011 roger auto
    LTBAG0 12/12/2011 roger manual
    LTAQS1 12/1/2011 Bob manual
    LTBBP4 12/1/2011 Chris manual

  4. #4
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Does this work?

    SELECT [Order Num], [Trade Date],[Cost Center Code],[Man-Semi-Auto]
    FROM [2011 Report]

    GROUP BY [Order Num], [Trade Date],[Cost Center Code],[Man-Semi-Auto]

    The spaces and symbols in your names will prove to be more trouble than they're worth in the long run.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 12-05-2011, 04:53 AM
  2. combining 3 columns into 1 new column!
    By joebox8 in forum Queries
    Replies: 7
    Last Post: 07-06-2011, 01:46 AM
  3. Distinct for one Column
    By crispy-bacon in forum Queries
    Replies: 2
    Last Post: 05-31-2011, 07:21 AM
  4. Replies: 5
    Last Post: 05-26-2010, 07:13 AM
  5. Replies: 2
    Last Post: 11-18-2009, 06:49 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums