Results 1 to 6 of 6
  1. #1
    Sreedhart is offline Novice
    Windows XP Access 2003
    Join Date
    May 2010
    Posts
    6

    Get all the distinct combinations from 2 columns of a table

    Dear all,



    I have table: Table1 with a number of columns in it. Out of it there are 2 columns in it namely: "Card" and "Number"
    I need to find out the distinct combination of these 2 rows.

    Eg:

    Card Number
    0 1
    0 1
    0 2
    0 1
    0 2
    1 1
    1 3

    my query should return:

    Card Number
    0 1
    0 2
    0 3

    Can anyone give me a simple query for the above

    I tried the following query but it does not work:
    Select Distinct(ABC.Card, ABC.Number) from ABC;

    Regards
    sreedhar

  2. #2
    Rawb is offline Expert
    Windows XP Access 2000
    Join Date
    Dec 2009
    Location
    Somewhere
    Posts
    875
    I'm not sure I understand what you're wanting. . .

    Why wouldn't you also want the following results returned as well:

    Card Number
    1 1
    1 3

    Both of those would be considered distinct combinations as well, wouldn't they?

  3. #3
    ConneXionLost's Avatar
    ConneXionLost is offline Simulacrum
    Windows XP Access 2003
    Join Date
    Jan 2010
    Location
    Victoria, Canada
    Posts
    291
    Same point as Rawb makes, how can you get:

    Card Number
    0 3

    As a distinct result when it isn't in your original data set?

  4. #4
    Sreedhart is offline Novice
    Windows XP Access 2003
    Join Date
    May 2010
    Posts
    6
    Hi ConneXionLost, Rawb,

    Both of you are correct. I am extremely sorry for not providing you the correct info.

    I need that unique combination.

    I also tried:

    Select Distinct (Number) as No, ABC.Card from ABC

    but this is not exactly helping me the way I want. I am still seeking for your help.

    Sorry once again
    sreedhar

  5. #5
    Rawb is offline Expert
    Windows XP Access 2000
    Join Date
    Dec 2009
    Location
    Somewhere
    Posts
    875
    I just threw together a DB with 1 Table and 1 Query to see what I could come up with.

    Table1 only has 2 Fields: Card and Numbar (Number), both of which are Long Integers. There is no Primary Key nor any Indexes.

    The Query's SQL was as follows:
    Code:
    SELECT DISTINCT Table1.Card, Table1.Numbar
    FROM Table1;
    This Query appears to do exactly what you want, although if there are other Fields in the database that are being returned as well, that could throw off the Query.

    the DISTINCT keyword in a SQL Query affects the entire Query, so as you add Fields to your Query, it increases the number of potential duplicates. For example, if you're only querying those two fields, DISTINCT will do what you want. If you add a third Field to the Query however, you can get results like this:

    Card Number ThirdField
    1 1 1
    1 1 2
    1 2 1
    1 2 3
    2 1 3
    2 1 4

    Notice that the Card and Number fields repeat. This is because the ThirdField Field makes the combination unique from the others.

  6. #6
    Sreedhart is offline Novice
    Windows XP Access 2003
    Join Date
    May 2010
    Posts
    6

    Thumbs up

    Thank you very much Rawb. This query is helping me well.

    I think I tried this query before, but was unsuccessful (dont know why).

    Anyways, thanks a lot.

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

Similar Threads

  1. Table with look-up columns
    By Tallica in forum Database Design
    Replies: 3
    Last Post: 03-02-2010, 02:09 PM
  2. Replies: 3
    Last Post: 01-21-2010, 08:10 PM
  3. Replies: 1
    Last Post: 10-09-2009, 11:52 AM
  4. ms access version combinations
    By marianne in forum Access
    Replies: 1
    Last Post: 08-05-2009, 07:37 AM
  5. Replies: 3
    Last Post: 09-19-2008, 02:19 AM

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