memo field truncation: new problem

[whincup]

My UNION query seems to be truncating memo fields, even with no grouping
statement in the SQL.

I am reporting the results of some research interviews. For each
interview, there are memo fields, each with a rating (1 - 4) based on
content. The table has:
"interviewID" (Number) as its key, and fields
"memo1" (memo)
"memo1rating" (Number)
"memo2"
"memo2rating"
etc.

I want to end up being able to report all the memos in order of rating
and interviewID, without distinguishing whether they come from the memo1
column or the memo2 column:
rated 1 -- ID & memo, ID & memo, ID & memo,
rated 2 -- ID & memo, ID & memo, ID & memo,
etc.
One interview can have several memos at the same rating.

My simple Union Query works great, except that it truncates the memo field:
SELECT [memo1rating], [interviewID], [memo1]
FROM

WHERE [memo1rating] <> 0
UNION SELECT [memo2rating], [interviewID], [memo2]
FROM

WHERE [memo2rating] <> 0;

The truncation even takes place when I strip out the WHERE clauses.

1. Is there any way to fix the query?

2. Is there some other way of approaching this that does not involve a
UNION query?

Thanks for any help you can give.

-- Greg Whincup

 

[Marshall Barton]

My UNION query seems to be truncating memo fields, even with no grouping
statement in the SQL.

I am reporting the results of some research interviews. For each
interview, there are memo fields, each with a rating (1 - 4) based on
content. The table has:
"interviewID" (Number) as its key, and fields
"memo1" (memo)
"memo1rating" (Number)
"memo2"
"memo2rating"
etc.

I want to end up being able to report all the memos in order of rating
and interviewID, without distinguishing whether they come from the memo1
column or the memo2 column:
rated 1 -- ID & memo, ID & memo, ID & memo,
rated 2 -- ID & memo, ID & memo, ID & memo,
etc.
One interview can have several memos at the same rating.

My simple Union Query works great, except that it truncates the memo field:
SELECT [memo1rating], [interviewID], [memo1]
FROM

WHERE [memo1rating] <> 0
UNION SELECT [memo2rating], [interviewID], [memo2]
FROM

WHERE [memo2rating] <> 0;

The truncation even takes place when I strip out the WHERE clauses.

1. Is there any way to fix the query?

2. Is there some other way of approaching this that does not involve a
UNION query?

Memo fields will be truncated by any operation that causes a
compare. DISTINCT, ORDER BY, GROUP BY, UNION are the ones
that immediately come to mind.

In your case, I don't think you need a UNION, try using
UNION ALL instead.

If that doesn't work, leave the memo field out of the query
and use another query to Join the results of the Union back
to the original table. Or, you could use code in the report
to look up the memo.

 

[whincup]

Marsh --

Thanks for the tips. I eventually got it working by:

1. Duplicating the full Table I was working from in a Query, so as to be
sure not to perform any compare operations on the data in the table;
2. Making a Union All query to pick out the memo fields I was interested
in, including a text field that included the leftmost 250 characters of
all the memo fields, and running all my compare operations on that text
field;
3. Making a third query that compared the leftmost 50 of the text field
to each of the memo fields in Query 1, then inserting the corresponding
memo field from the original table.

Not sure it's the most efficient way I could have used your suggestions,
but it got there. Thanks again.

-- Greg Whincup

whincup wrote:

My UNION query seems to be truncating memo fields, even with no grouping
statement in the SQL.

I am reporting the results of some research interviews. For each
interview, there are memo fields, each with a rating (1 - 4) based on
content. The table has:
"interviewID" (Number) as its key, and fields
"memo1" (memo)
"memo1rating" (Number)
"memo2"
"memo2rating"
etc.

I want to end up being able to report all the memos in order of rating
and interviewID, without distinguishing whether they come from the memo1
column or the memo2 column:
rated 1 -- ID & memo, ID & memo, ID & memo,
rated 2 -- ID & memo, ID & memo, ID & memo,
etc.
One interview can have several memos at the same rating.

My simple Union Query works great, except that it truncates the memo field:
SELECT [memo1rating], [interviewID], [memo1]

FROM

WHERE [memo1rating] <> 0
UNION SELECT [memo2rating], [interviewID], [memo2]

FROM

WHERE [memo2rating] <> 0;

The truncation even takes place when I strip out the WHERE clauses.

1. Is there any way to fix the query?

2. Is there some other way of approaching this that does not involve a
UNION query?

Memo fields will be truncated by any operation that causes a
compare. DISTINCT, ORDER BY, GROUP BY, UNION are the ones
that immediately come to mind.

In your case, I don't think you need a UNION, try using
UNION ALL instead.

If that doesn't work, leave the memo field out of the query
and use another query to Join the results of the Union back
to the original table. Or, you could use code in the report
to look up the memo.