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

1
Why You Should Avoid Using Memo Fields for
Grouping in MS Access ?
2
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.
3
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 theGROUP 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.
4
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, lets
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.  
5
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
database crash. To avoid this, users by habit
should learn to backup and apply regular database
management practices.
6
Contact us
Ben Beitler
ben_at_accessdatabasetutorial.com
(44) 7881
502400
United Kingdom
London
https//www.accessdatabasetutorial.com/