Why You Should Avoid Using Memo Fields for Grouping in MS Access ?

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 access database 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.

Microsoft access database 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 Microsoft access database 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 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 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 Microsoft access database crash. To avoid this, users by habit should learn to backup and apply regular database management practices.