Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    May 2002
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    DISTINCT (xp/2k)

    Can anybody please explain how DISTINCT actually works? I seem to get a different answer everywhere I look and I am definitely getting some duplicates I cannot allow. If I am selecting four different fields, one which is a name that has to be unique but the other fields can all be duplicate or not, if DISTINCT won't work, what will? The query definition is being made in VBA, by the way, so I can't do anything in the Access query designer, if that makes any difference.

  2. #2
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    USA
    Posts
    379
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DISTINCT (xp/2k)

    I don't think DISTINCT will work for you ...
    DISTINCT omits records that contain duplicate data in the selected fields. To be included in the results of the query, the values for each field listed in the SELECT statement must be unique. If the SELECT clause contains more than one field, the combination of values from all fields must be unique for a given record to be included in the results.

    I'm not certain I understand what your situation, but could you SELECT DISTINCT on the field Last Name and then link that to the table to get all the records that link to those distinct Last Names?

    If I'm off on this, please post the code or a bit more detail.

  3. #3
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    NYC,USA,Earth
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DISTINCT (xp/2k)

    Distinct selects distinct values from a field in a table. So if you have two records with Jones as the last name only one will be returned. If there are more than one field in your select statement the combination of values from all columns must be unique for a record to be included in the results.

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: DISTINCT (xp/2k)

    As the others have said, DISTINCT won't do it for you. If you explain more about what you're actually trying to get out of this query, maybe someone can help you figure out what *will* work. For instance, a groupby query might be the answer, but that depends on exactly what you're trying to do. Oh, and even if the SQL is being built in code, there's nothing to stop you form creating the same query in the query grid to test the results.
    Charlotte

  5. #5
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Altnau, Thurgau, Switzerland
    Posts
    447
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DISTINCT (xp/2k)

    Have a try with the keyword DISTINCTROW (It's different than DISTINCT in how it 'filters' records out.). Technical descriptions of both should be in the help files.

  6. #6
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: DISTINCT (xp/2k)

    One thing to watch for if it appears that you are getting duplicate records in your query result is to check for spaces or null values at the end of the data in each field. "John" and John " are treated as two different fields in the distinct statement although the data set returned by the distinct will "look" as if they are the same record (They are not.) It might help to use Trim(Fieldname) in the query on each field to eliminate trailing spaces if they exist or the NZ function to remove nulls.

    HTH
    Regards,

    Gary
    (It's been a while!)

Posting Permissions

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