why you should avoid using memo fields n.
Skip this Video
Loading SlideShow in 5 Seconds..
Why You Should Avoid Using Memo Fields for Grouping in MS Access ? PowerPoint Presentation
Download Presentation
Why You Should Avoid Using Memo Fields for Grouping in MS Access ?

Loading in 2 Seconds...

play fullscreen
1 / 6

Why You Should Avoid Using Memo Fields for Grouping in MS Access ? - PowerPoint PPT Presentation

  • Uploaded on

If you are looking for Access Database related Ebook ;\nPlease go to : https://www.amazon.com/Microsoft-Access-Build-Database-Queries-ebook/dp/B077ZFDV9L/ref=sr_1_5?s=digital-text&ie=UTF8&qid=1519245676&sr=1-5

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
Download Presentation

PowerPoint Slideshow about 'Why You Should Avoid Using Memo Fields for Grouping in MS Access ?' - BenBeitler14235

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
a memo field can store a huge quantity

A memo field can store a huge quantity of information, allowing up to 65,536 characters, with different choices. In the most recent release of Microsoft access database, it can store up to 1 GB of characters and enables rich text formatting. However, it is often advised to avoid the use of memo fields for grouping in Access.

memo fields in the 2009 edition of microsoft

Memo Fields

In the 2009 edition of Microsoft Access and including the recent version, there was a bug in which the memo field would show incorrect and erroneous characters under specific circumstances. If the user used the‘GROUP BY’ query on a memo field or if the query contains a JOIN on un-indexed records, the memo field would display incorrect results. Microsoft Access would just show random characters in place of the contents of the given memo field.

MS Access fundamentally truncates the memo and the most widely known factors that cause the truncation are aggregation, uniqueness, formatting, and union queries.  For instance, a union query that joins the records from various tables and then de-duplicates them. It analyses the memo field which then results in truncation.

Why Memo Fields Are Inefficient

The main reason why Access does not allow more than 255 characters is that it would hamper the performance to a great extent, as string operations are disk and processor intensive. Some data sources process strings as bytes while some use Unicode so it becomes incompatible. Though it saves the data to the table, still it becomes inefficient in working with the extra data.

the characters may not be similar under various

The characters may not be similar under various settings and the JET engine (Microsoft database engine) is rigidly case-sensitive.

For example, you have two tables — Table1 and Table2 with the first having two fields (Field A and Field B) and the second having only one. In Table1, let’s assume that Field A is a number (long integer) and Field B is a long text and both fields contain two records. Table2 has a long integer number field that also has two existing records. The two tables do not have an index.

The query will combine the two tables and the groups with the fields in Table1.  But in due course, it will display a wrong result for Field B. That is the reason for avoiding the use of Memo fields when grouping.

workaround to avoid truncation you may use either


  • To avoid truncation, you may use either of these two alternatives:
  • Do not user ’Group By’ on the memo field. As a workaround, select ‘First’ option in the Total row under the memo field. This technique not only avoids the bug, it also lets JET output the entire memo field instead of truncating it at 255 characters.
  • Learn to user and Index records involved in the JOIN relationship.
  • Despite being an exceptionally well-known database management system, Microsoft Access is far from being foolproof. Indeed, even the information stored in Access database can be at risk during a database crash. To avoid this, users by habit should learn to backup and apply regular database management practices.
contact us ben beitler ben@accessdatabasetutorial

Contact us Ben Beitler


(+44) 7881 502400

United Kingdom