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
|
|||
|
|||
UNION Query truncating Memo fields
I came to this group to ask why I was losing most of my memo fields in
a UNION query, but John Spencer posted earlier that "Crosstab queries, summary queries, Union queries, and Queries that use Distinct or DistinctRow will all truncate a memo field to 255 characters so Access can perform the required functionality of eliminating duplicates." I guess my question, then, is how do I get around this problem? I have a query that is running on two separate mechanisms. The first is that the record has a certain value in one field AND three other fields are not null. The other mechanism is that one of seven fields is not null. Currently, I have a UNION query that works just fine, cramming together all seven iterations where each of the seven fields is not null and the other criteria apply. I am sure that this is a crude method of doing it, but I did not know any other way. Since this UNION query does everything but display my entire memo fields, perhaps I need to find a new method. Any ideas? Matt |
#2
|
|||
|
|||
UNION Query truncating Memo fields
Hi, My name is Amy Vargo. Thank you for using the Microsoft Access Newsgroups. This issue has already been reported as a bug. To try to work around the issue, try the following: 1.Turn each Select statement into a separate append query, and append the data from each query to one main table. 2.Create the union query but do not include any Memo fields in the union query. Create a select query based on this union query joined to a regular Select query or table that contains the Memo field. Because this query is a Select query, when you run it, it will show all characters of the Memo field. I hope this helps! If you have additional questions on this topic, please respond back to this posting. Regards, Amy Vargo Microsoft Access Engineer This posting is provided 'AS IS' with no warranties, and confers no rights. You assume all risk for your use. © 2001 Microsoft Corporation. All rights reserved. |
#3
|
|||
|
|||
UNION Query truncating Memo fields
Matt,
I'm not sure why you don't just write a where clause, something like: SELECT T.* FROM yourTable T WHERE (SomeField = X AND AnotherField IS NOT NULL AND SecondField IS NOT NULL AND ThirdField IS NOT NULL) OR (SomeField1 IS NOT NULL OR SomeField2 IS NOT NULL OR SomeField3 IS NOT NULL OR SomeField4 IS NOT NULL OR SomeField5 IS NOT NULL OR SomeField6 IS NOT NULL OR SomeField7 IS NOT NULL) If there is a good reason for not doing this, then you might be able to use your union query (as a subquery) to identify the unique values of the primary key fields in this table, and then join the subquery to your table on the PK values, something like below (untested). SELECT T.* FROM yourTable T INNER JOIN (SELECT PKField1, PKField2, ... FROM yourTable WHERE SomeField = X AND AnotherField IS NOT NULL AND SecondField IS NOT NULL AND ThirdField IS NOT NULL UNION SELECT PKField1, PKField2, ... FROM yourTable WHERE SomeField1 IS NOT NULL OR SomeField2 IS NOT NULL OR SomeField3 IS NOT NULL OR SomeField4 IS NOT NULL OR SomeField5 IS NOT NULL OR SomeField6 IS NOT NULL OR SomeField7 IS NOT NULL) T1 ON T.pkField1 = T1.pkfield1 AND T.pkField2 = T1.pkfield2 -----Original Message----- I came to this group to ask why I was losing most of my memo fields in a UNION query, but John Spencer posted earlier that "Crosstab queries, summary queries, Union queries, and Queries that use Distinct or DistinctRow will all truncate a memo field to 255 characters so Access can perform the required functionality of eliminating duplicates." I guess my question, then, is how do I get around this problem? I have a query that is running on two separate mechanisms. The first is that the record has a certain value in one field AND three other fields are not null. The other mechanism is that one of seven fields is not null. Currently, I have a UNION query that works just fine, cramming together all seven iterations where each of the seven fields is not null and the other criteria apply. I am sure that this is a crude method of doing it, but I did not know any other way. Since this UNION query does everything but display my entire memo fields, perhaps I need to find a new method. Any ideas? Matt . |
#4
|
|||
|
|||
UNION Query truncating Memo fields
1. If the 2 mechanisms act on the same Table, there is no
need to use the Union Query. You on need 2 sets of criteria joined by OR. Something like: SELECT * FROM YourTable WHERE ( (FieldA = Something) AND (FieldB Is Not Null) AND (FieldC Is Not Null) AND (FieldD Is Not Null) ) OR ( (Field1 Is Not Null) OR (Field2 Is Not Null) OR (Field3 Is Not Null) OR (Field4 Is Not Null) OR (Field5 Is Not Null) OR (Field6 Is Not Null) OR (Field7 Is Not Null) ) Note the order of operations for the Boolean expression! 2. If the 2 mechanisms act on different Tables, use "UNION ALL" rather than "UNION". Union eliminates duplicates and therefore it has to compare String values, hence 255 character. "UNION ALL" does not need to compare values since it does not eliminate duplicate. 3. Personally, I retrieve Memo Field value separate and only when required. HTH Van T. Dinh MVP (Access) -----Original Message----- I came to this group to ask why I was losing most of my memo fields in a UNION query, but John Spencer posted earlier that "Crosstab queries, summary queries, Union queries, and Queries that use Distinct or DistinctRow will all truncate a memo field to 255 characters so Access can perform the required functionality of eliminating duplicates." I guess my question, then, is how do I get around this problem? I have a query that is running on two separate mechanisms. The first is that the record has a certain value in one field AND three other fields are not null. The other mechanism is that one of seven fields is not null. Currently, I have a UNION query that works just fine, cramming together all seven iterations where each of the seven fields is not null and the other criteria apply. I am sure that this is a crude method of doing it, but I did not know any other way. Since this UNION query does everything but display my entire memo fields, perhaps I need to find a new method. Any ideas? Matt . |
#5
|
|||
|
|||
UNION Query truncating Memo fields
Van T. Dinh wrote:
1. If the 2 mechanisms act on the same Table, there is no need to use the Union Query. You on need 2 sets of criteria joined by OR. Something like: SELECT * FROM YourTable WHERE ( (FieldA = Something) AND (FieldB Is Not Null) AND (FieldC Is Not Null) AND (FieldD Is Not Null) ) OR ( (Field1 Is Not Null) OR (Field2 Is Not Null) OR (Field3 Is Not Null) OR (Field4 Is Not Null) OR (Field5 Is Not Null) OR (Field6 Is Not Null) OR (Field7 Is Not Null) ) Note the order of operations for the Boolean expression! This works great, although I had to change the term joining the two sets with 'and' (probably because I was not very clear on what I wanted). I am new to SQL, and I had no idea that you could join sets in this way. For more complex sets in the future, though, I will definitely consider the different ways of joining queries that Amy and anonymous suggested. Thanks for all of your help! Matt |
Thread Tools | |
Display Modes | |
|
|