limit for data exceeded message

[Bob]

I have a fairly simple query that needs to take 2 memo
fields and mid$() them into 32 component parts of 250
characters each (16 parts per memo). When I do this, I get
a message 'This form or report is based on a query that
exceeds the limit for data in a single record'. I am not
aware of what limit it is referring to and neither Access'
help nor the Knowledge Base at microsoft.com provides any
reference to this message. If anyone is familiar with this
message and can provide some insight as to how to get past
it, that would be very helpful. Thanks.

 

[Tom Ellison]

Dear Bob:

You could divide each row into 32 component rows, instead of 32
columns in the same row.

SELECT Column1, Column2, 1 AS ComponentSeq,
MID$(MemoColumn1, 1, 250) AS PartValue
FROM Your Table

SELECT Column1, Column2, 2 AS ComponentSeq,
MID$(MemoColumn2, 251, 250) AS PartValue
FROM Your Table

Create a total of 32 select queries like these, 16 taking the
PartValue from each of the 2 memos. Transform them into Append
queries and you'll have properly normalized data.

I have used Column1, Column2 to represent any other columns (not
necessarily 2 of them) in your source table you need to preserve in
the destination you're creating.

Your destination table would be composed to take data matching these
select queries.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[John Spencer (MVP)]

And to finish Tom's reply.

There is a 2,000 character limit on any row (not including memo fields - other
than the 8-byte(?) pointer to the data). That is the limit you are running
into. Try looking in help under Specifications - Microsoft Access database
table specifications. Admittedly, the query specifications don't give you the
same information - an oversight on MS part?

 

[Tom Ellison]

Sorry, I didn't finish. I must have been low on keyboard fluid! <g>

John has correctly explained the cause of your problem. I saw another
problem, having the data not normalized, and tried only to give a
solution.

Following John's information, you could theoretically create the 16
columns as memos and it would probably work, depending on what other
columns you have, except that in Jet you cannot CAST() the type of
your data, so I don't see where you would have any control over making
the 16 columns memos. So, you're really stuck. I cannot think of a
solution to this right now, nor could I yesterday. But 16 memo
columns would be 256 out of the 2000 byte limit, whereas you're
creating 250 * 16 = 4000 bytes just from the 16 columns. And that is
certainly where the first "problem" lies.

The other problem, that of having non-normalized data (repeating
columns of undifferentiated data) is one that should be of critical
interest to you anyway, and my suggestion should solve both problems.

When I normalize data in this way, I also make a habbit of adding an
integer value, such as ComponentSeq in my example, to track the only
difference there is between the 250 byte elements of your memos, that
being the position of each entry. If there is any meaning to their
sequence, then that information would not be lost. Besides, this
created a unique natural key to the data. Be sure to continue
numbering ComponentSeq from 9 to 16 for the second memo column to
maintain this unique ordering. (It is unique assuming there is a
unique key on the data you have now, that is.)

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts