UNION Query truncating Memo fields

  • Thread starter Thread starter Matthew DeAngelis
  • Start date Start date
M

Matthew DeAngelis

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
 
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.
 
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
 
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)
 
Van said:
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!
Click to expand...


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
 
Back
Top