If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
memo field truncation: new problem
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 [Table] WHERE [memo1rating] 0 UNION SELECT [memo2rating], [interviewID], [memo2] FROM [Table] 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 |
#2
|
|||
|
|||
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 [Table] WHERE [memo1rating] 0 UNION SELECT [memo2rating], [interviewID], [memo2] FROM [Table] 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. -- Marsh |
#3
|
|||
|
|||
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 Marshall Barton wrote: 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 [Table] WHERE [memo1rating] 0 UNION SELECT [memo2rating], [interviewID], [memo2] FROM [Table] 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. -- Marsh |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
MEMO FIELD | SEAN | Database Design | 3 | October 1st, 2004 04:33 AM |
Using sql insert with memo field truncates text data | Graham Meredith | General Discussion | 1 | September 1st, 2004 03:11 AM |
Please Help! A2K memo field shows eror "Too much text to edit" | Lauren Wilson | Using Forms | 1 | August 12th, 2004 04:02 AM |
Text field Or Memo Field | Terry | General Discussion | 2 | August 5th, 2004 04:32 AM |
Using IIF statement in Memo Field | J Cunningham | Running & Setting Up Queries | 5 | May 26th, 2004 09:19 AM |