Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Select Distinct Combo (2002-SP2)

    Have a combo box linked to a table, and its pulling up every record, instead of just unique values.

    the SQL in the row source of the combo is:-

    SELECT DISTINCTROW [External Document Registar].[External Documents ID], [External Document Registar].[Document Location] FROM [External Document Registar];

    Have also tried it without the word ROW, i have another combo box that is similar, and yet Does work.

  2. #2
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Select Distinct Combo (2002-SP2)

    Steve,
    You could link your combo box to a query that only shows Unique Fields (querying only the field you want to populate your combo box).
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What's more, you deserve to be hacked." -Richard Clarke

  3. #3
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Select Distinct Combo (2002-SP2)

    Or you could use a total query :
    SELECT DISTINCT [External Document Registar].[External Documents ID], [External Document Registar].[Document Location]
    FROM [External Document Registar]
    GROUP BY [External Document Registar].[External Documents ID], [External Document Registar].[Document Location]
    Francois

  4. #4
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Select Distinct Combo (2002-SP2)

    Francois,.. .that SQL again, shows every single entry !!!

    Whats strange here, is that i have another combo box, thats similar SELECT DISTINCTROW etc... and that works. So something weird about the table.

    Edit:

    Ok making a Query and pointing it to that works, with THIS SQL in the query

    SELECT DISTINCT [External Document Registar].[Document Location]
    FROM [External Document Registar]
    GROUP BY [External Document Registar].[External Documents ID], [External Document Registar].[Document Location]
    ORDER BY [External Document Registar].[Document Location];

    BUT.. copying this SQL into the rowsource of the combo DOES NOT work.. just gives me a whole load of blanks.

    Ho Hum ! <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    Thanks

  5. #5
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Select Distinct Combo (2002-SP2)

    If my sql return all records, then the combination of your ID's and Location are all unique.
    Do you want unique locations ? But with what ID ? You can use First, Last, Max or Min in the total query.
    Francois

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Select Distinct Combo (2002-SP2)

    The reason that the query works is that you only display the location field. It can be much simpler, however: use either

    SELECT DISTINCT [Document Location]
    FROM [External Document Registar];

    or

    SELECT [Document Location]
    FROM [External Document Registar]
    GROUP BY [Document Location];

    By all means, use the query name as row source for your combo box. It is more efficient than using an SQL string, since Access can optimize a stored query. You must also set the Column Count property to 1 and clear the Column Widths property.

Posting Permissions

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