SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Wizard silver trophybronze trophy asp_funda's Avatar
    Join Date
    Jun 2003
    Location
    ether
    Posts
    4,479
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Question get 2 distinct columns in Access

    hi
    I'm trying to get distinct values from 2 columns in access. Until now, here's what I've been using
    Code:
    SELECT DISTINCT month FROM myTable
    It returns all distinct/unique values of the column month.
    But there's another column named year & what I want is to get the distinct value of both month & year. Like, consider the following data in the table:

    Code:
    month   ||  year
    ------------------
    3	||   2000
    3	||   2000
    3	||   2000
    6	||   2000
    6	||   2000
    4	||   2001
    4	||   2001
    7	||   2001
    5	||   2003
    5	||   2003
    6	||   2003
    3	||   2004
    Now, in the above table data, there are 3 instances of 3/2000, 2 instances of 6/2000, 2 instances of 4/2000, 2 instances of 5/2003 & one instance each of 7/2001, 6/2003 & 3/2004.

    What I want is to get only the unique entries. These are 2 columns of one table & I want one instance of each occurence.

    Can anyone help?
    Our lives teach us who we are.
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    Me - Photo Blog - Personal Blog - Dev Blog
    iG:Syntax Hiliter -- Colourize your code in WordPress!!

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,021
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Code:
    select distinct [month], [year] from yourtable
    tip: do not use reserved words like MONTH or YEAR to name your columns -- you are only asking for syntax errors
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Wizard silver trophybronze trophy asp_funda's Avatar
    Join Date
    Jun 2003
    Location
    ether
    Posts
    4,479
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    Code:
    select distinct [month], [year] from yourtable
    do I put the braces([ ]) too? b'coz without braces, I don't get desired results.



    Quote Originally Posted by r937
    tip: do not use reserved words like MONTH or YEAR to name your columns -- you are only asking for syntax errors
    Thanks for the TIP.
    Our lives teach us who we are.
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    Me - Photo Blog - Personal Blog - Dev Blog
    iG:Syntax Hiliter -- Colourize your code in WordPress!!

  4. #4
    SitePoint Enthusiast
    Join Date
    Apr 2004
    Location
    TX
    Posts
    71
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yup, the brackets are needed...for the reason Rudy brought up..."month" and "year" are keywords in Access...brackets specify a field name, w/out 'em you're selecting keywords, not field names.

  5. #5
    SitePoint Wizard silver trophybronze trophy asp_funda's Avatar
    Join Date
    Jun 2003
    Location
    ether
    Posts
    4,479
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Oh OK. I got it working. Changed my field names(stupid of me to forget such a common thing, never have done that before, I think I'm getting mad lately, need a vacation ).
    Now it works quite well, without the braces.

    Thanks Rudy & null.
    Our lives teach us who we are.
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    Me - Photo Blog - Personal Blog - Dev Blog
    iG:Syntax Hiliter -- Colourize your code in WordPress!!


Bookmarks

Posting Permissions

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