Results 1 to 4 of 4
  1. #1
    WS Lounge VIP access-mdb's Avatar
    Join Date
    Dec 2009
    Location
    Oxfordshire, UK
    Posts
    1,723
    Thanks
    146
    Thanked 156 Times in 149 Posts

    Query problem in Access 2013

    I have the following query, with which I want to list the unique values in the field 'tags'. When I run this it tells me that I can't because it would create duplicate values in the index, primary key or relationship. But it's a select query and I'm not (as far as I can see) trying to change any table. What am I missing?


    Code:
    SELECT DISTINCT tblNewPhotographs.Tags
    FROM tblNewPhotographs
    ORDER BY tblNewPhotographs.Tags;

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    How did you design the query - writing SQL directly, or with the "wizard?" The code you posted is indeed a select query, not an action query, and would not insert any values in indexes or the table. It almost sounds like you may be dealing with a corrupt database or a corrupt index. I presume tblNewPhotographs does have one or more indexes - is Tags one of those fields?
    Wendell

  3. #3
    WS Lounge VIP access-mdb's Avatar
    Join Date
    Dec 2009
    Location
    Oxfordshire, UK
    Posts
    1,723
    Thanks
    146
    Thanked 156 Times in 149 Posts
    I created it via the QBE grid - as I do with all my queries. Tags is a plain vanilla field, so not an index.

    I've just tried creating a query on another table and had no problem, so I added tblNewPhotographs and deleted the other table and this one worked. But with one difference, I hadn't sorted the field
    Code:
    SELECT DISTINCT tblNewPhotographs.Tags
    FROM tblNewPhotographs;
    But than I added the sort and got the error. Tags is a long text field (what used to be called a memo field). Can you sort on one of those? Looks like I don't need to as the field is sorted anyway without the sort being added.

    I'm fairly new to 2013, moist of my Access work being on 2003, so there are areas where I'm a bit grey about....

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    Ah, the memo/long text field issue. You can sort on those kind of fields, but in a limited fashion and the DISTINCT clause does an implicit sort. For more background on sorting memo and long text fields see Sorting Memo Field - Mind Baffling Unfortunately the error messages you get from ACE (or Jet) aren't very informative in some cases and this appears to be one of them. Glad you were able to resolve the issue.
    Wendell

Posting Permissions

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