Results 1 to 11 of 11
  1. #1
    spyldbrat is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Jul 2015
    Posts
    73

    Dup data/Unique Values

    I have a query that I set in the properties as only showing unique values. When I ran the query, I got an error message that the field was too small to accept the amount of data I was trying to add. I found on the MS Support site that the characters were too long in my memo field. The site said to delete the memo field and then re-create the database using the memo line from the table and the new query.

    I ran the query without the memo line, all runs well and no dups are displayed. When I merge the table and query together as it said I should, everything now displays twice. I even set the properties on the merged database to unique values, and it still shows me all data twice? Any suggestions would be appreciated.

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    so, you are running an append query?
    and adding data to a memo field?
    replacing the data, or concatinating?

  3. #3
    spyldbrat is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Jul 2015
    Posts
    73
    I don't think I am appending (since what I am looking out on the net tells me I have select some sort append option which I did not do).

    What I was deleted the memo field from the original query. I then created a secondary query that used all the data from my original query (less the memo line of course) and the table in which the original query gets its data from. From the table, I only used the memo field in the secondary query.

  4. #4
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    When running a select query that contains a memo field, you cannot set the Unique Value or Unique Row property to True, or you will get that error.

    You can sort only in design view, before you run the query.

    Watch out for this one: if you Group By on a Memo field, the query runs, but the Memo field is truncated (I think to 255 characters, but I didn't count)

  5. #5
    spyldbrat is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Jul 2015
    Posts
    73
    And that was exactly what happened. One of the fields in the memo exceeded 255 characters. MS Support said to remove the memo field from query and to put the memo field back to the data, I should merge the new query with the table. My Query is not grouped by any fields. It's a just a strait query to remove all dup data. How can get the dup data removed and still keep the memo field in my end result?

  6. #6
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Create a query with the table key and Left(memofield,255) and group on that. Then join this in with the first query.

  7. #7
    spyldbrat is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Jul 2015
    Posts
    73
    Where do I put the table key (I am not familiar with this term)? Also, the name of the field in question is called "NOTES". Should it be notes instead of memofield? I tried to add both wordings to the criteria in design view but that obviously did not work.

  8. #8
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    The table key is the field you use to link to other tables in your query - sometimes it is the primary key but not necessarily. The point I was trying to make is that all you would need for this query is the truncated memo field and the key used for linking.

    Yes you must translate the field names - we don't know what your objects are called so change it to whatever the field is called in your database.

  9. #9
    spyldbrat is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Jul 2015
    Posts
    73
    If is am understanding you correctly, in design view I would add what you wrote above under the primary key field (in this case is the EEN?

  10. #10
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    You have one query - the one mentioned in post #3 which contains all the data grouped. The second query will contain just the memo field truncated and grouped. In order to have all the data in one query you will create a third query which shows all the data from query 1 and the memo from query 2. These two queries need to be joined together in the third query, it is called linking, a field (or fields) that are common between the two queries. You know your data structure, so you will know which fields are the same in both queries.

  11. #11
    spyldbrat is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Jul 2015
    Posts
    73
    Hello. Thank you for all your help. It took me a little while to get it working right, but I finally did!

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

Similar Threads

  1. Unique values
    By marwlo in forum Queries
    Replies: 2
    Last Post: 10-25-2016, 12:45 PM
  2. Displaying only unique values
    By MCCDOM in forum Forms
    Replies: 8
    Last Post: 11-13-2014, 06:14 AM
  3. Unique values
    By helpaccess in forum Queries
    Replies: 3
    Last Post: 09-19-2011, 03:46 PM
  4. Creating a unique ID for a new set of values
    By slaterino in forum Programming
    Replies: 1
    Last Post: 08-24-2010, 09:35 AM
  5. Count of Unique Values
    By vsmk in forum Queries
    Replies: 2
    Last Post: 03-14-2010, 12:07 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