Query Exceeds the limit of data for a single record

[Chi]

I have designed a database for tracking results from surveys filled out by
participants attending various workshops. I am running into problems with a
query that sums, counts, and groups together each question on the survey. The
field data type for the question is "text". I have a total of 15 questions on
the survey. When I run the query, I receive an error message stating that the
form or report is based on a query in which it exceeds the limit of data for
a single record. My table field properties are set at 255 characters. Some of
my questions may be larger than 255. How might I be able to increase the
character limit on a text field so that my query will continue to sum, count
and group? I tried to use the memo data type but my report would only show up
to 200 characters. I expanded box on my report to allow more space, however,
it still does not work. Any suggestions on how to resolve this issue with the
query exceeding the limit of data for a single record and how to allow the
question to appear completely on my report? Thanks!

 

[John Spencer]

A query can only return 2000 characters in any one row. There is no way to
change the limit on the number of characters.

If you change your text fields to memo fields, then you may be able to get
the results you want depending on the structure of your table.

I am guessing that you have 15 question fields each with a maximum length of
255 characters. I have no idea what your query structure looks like or your
table structure.


--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Chi]

Thank you John,

Your explanations are very useful to me!

Even though, I changed the text fields to Memo fields, I still got the same
message stating that the form or report is based on a query in which it
exceeds the limit of data for a single record.

Yes! You were right. I have 15 question fields each with a maximum length of
255 characters. In the query, I use each question for three times - group,
count and sum. Does that make a problem?

Here is the report’s design view:

Question1 Question1 Question1 Question2 Question2
Question2 ....
Count group sum count group
sum


Thank you
Chi

 

[John Spencer]

More important is what does the query look like.

Open the query in design view
Select VIEW : SQL from the menu
Copy and paste the resultant text.

I really can't see what you are attempting to do or how you could do what
you seem to want with your current design.

First grouping, counting and summing fields will always truncate the fields
to a maximum of 255 characters.

Are your responses to the questions actually that long?

Your only choice may be to do the processing in sections of 7 or 8 questions
so you don't exceed the number of characters limitations.

BY the way how do you sum text responses?
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Chi]

Thank you John,

---------
SELECT Information.SQuestion1, Count(Information.SQuestion1) AS
CountOfSQuestion1, Sum(Information.SA1) AS SumOfSA1, Count(Information.SA1)
AS CountOfSA1, Information.SQuestion2, Count(Information.SQuestion2) AS
CountOfSQuestion2, Sum(Information.SA2) AS SumOfSA2, Count(Information.SA2)
AS CountOfSA2, Information.SQuestion3, Count(Information.SQuestion3) AS
CountOfSQuestion3, Sum(Information.SA3) AS SumOfSA3, Count(Information.SA3)
AS CountOfSA3, Information.SQuestion4, Count(Information.SQuestion4) AS
CountOfSQuestion4, Sum(Information.SA4) AS SumOfSA4, Count(Information.SA4)
AS CountOfSA4, Information.SQuestion5, Count(Information.SQuestion5) AS
CountOfSQuestion5, Sum(Information.SA5) AS SumOfSA5, Count(Information.SA5)
AS CountOfSA5, Information.SQuestion6, Count(Information.SQuestion6) AS
CountOfSQuestion6, Sum(Information.SA6) AS SumOfSA6, Count(Information.SA6)
AS CountOfSA6, Information.SQuestion7, Count(Information.SQuestion7) AS
CountOfSQuestion7, Sum(Information.SA7) AS SumOfSA7, Count(Information.SA7)
AS CountOfSA7, Information.SQuestion8, Count(Information.SQuestion8) AS
CountOfSQuestion8, Sum(Information.SA8) AS SumOfSA8, Count(Information.SA8)
AS CountOfSA8, Information.SQuestion9, Count(Information.SQuestion9) AS
CountOfSQuestion9, Sum(Information.SA9) AS SumOfSA9, Count(Information.SA9)
AS CountOfSA9, Information.SQuestion10, Count(Information.SQuestion10) AS
CountOfSQuestion10, Sum(Information.SA10) AS SumOfSA10,
Count(Information.SA10) AS CountOfSA10, Information.SQuestion11,
Count(Information.SQuestion11) AS CountOfSQuestion11, Sum(Information.SA11)
AS SumOfSA11, Count(Information.SA11) AS CountOfSA11,
Information.SQuestion12, Count(Information.SQuestion12) AS
CountOfSQuestion12, Sum(Information.SA12) AS SumOfSA12,
Count(Information.SA12) AS CountOfSA12, Information.SQuestion13,
Count(Information.SQuestion13) AS CountOfSQuestion13, Sum(Information.SA13)
AS SumOfSA13, Count(Information.SA13) AS CountOfSA13,
Information.SQuestion14, Count(Information.SQuestion14) AS
CountOfSQuestion14, Sum(Information.SA14) AS SumOfSA14,
Count(Information.SA14) AS CountOfSA14, Information.SQuestion15,
Count(Information.SQuestion15) AS CountOfSQuestion15, Sum(Information.SA15)
AS SumOfSA15, Count(Information.SA15) AS CountOfSA15
FROM Information
GROUP BY Information.SQuestion1, Information.SQuestion2,
Information.SQuestion3, Information.SQuestion4, Information.SQuestion5,
Information.SQuestion6, Information.SQuestion7, Information.SQuestion8,
Information.SQuestion9, Information.SQuestion10, Information.SQuestion11,
Information.SQuestion12, Information.SQuestion13, Information.SQuestion14,
Information.SQuestion15;

Thank you
Chi

 

[John Spencer]

You can try truncating the question to the first 50 characters
Does SQuestion1 always have the same text? If so, you could try truncating
the question text to the first 50 (or so) characters. Or substituting your
own short text in place of SQuestion1.

SELECT Left(Information.SQuestion1,50) as Q1
, Count(Information.SQuestion1) AS CountOfSQuestion1
, Sum(Information.SA1) AS SumOfSA1
, Count(Information.SA1) AS CountOfSA1

OR
SELECT "Q1: Subject is red" as Q1,
, Count(Information.SQuestion1) AS CountOfSQuestion1
, Sum(Information.SA1) AS SumOfSA1
, Count(Information.SA1) AS CountOfSA1

You would also have to modify the group by to match what you are putting in
the select clause. I can't think of any way to handle this data the way you
have structured it.
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Brandon Baum]

I had a similar problem with the full text from a memo field not showing up in a report I made. I wish I could say why it didn't work, but I found that if I put it in the Page header or footer the full text was not displayed. I'll have to dig up the program and find out where I ended up putting the memo field, but see if there is any way to move the memo value in your report to a different section.

Sorry seems like a lame answer, but it worked for me.

 

[fredg]

I had a similar problem with the full text from a memo field not
showing up in a report I made. I wish I could say why it didn't
work, but I found that if I put it in the Page header or footer the
full text was not displayed. I'll have to dig up the program and
find out where I ended up putting the memo field, but see if there
is any way to move the memo value in your report to a different
section.

Sorry seems like a lame answer, but it worked for me.

This post is just hanging on it's own, without any previous thread to
relate to. I would suggest, whenever replying to a post, that you keep
your new post in the same thread.

In any event, a Report's Page Header and Page Footer do not have a
CanGrow property, so any control you place in it (even if that
control's CanGrow property is set to Yes) will be cut off when it
reaches the bottom of the Page Header or Footer.