combining 2 queries (union) causes field to be truncated

[dan]

I am trying to make a query from 2 tables
the first field (selection in the 1st table and song in the 2nd table) is a
text field of 105 chrs
the second field (lyrics in both tables) is a memo field

each query worked by itself but when I combine them with UNION the memo
field is truncated to about 237 chrs - is this somehow being changed to a
text field?

what am I doing wrong?

SELECT [CD Records - Access 2002].SELECTION, [CD Records - Access
2002].lyrics
FROM [CD Records - Access 2002]
WHERE ((([CD Records - Access 2002].lyrics)>"-"))
UNION SELECT [LYRICS DATA BASE - songs I don't have].song, [LYRICS DATA
BASE - songs I don't have].lyrics
FROM [LYRICS DATA BASE - songs I don't have];

 

[Sylvain Lafontaine]

Try with UNION ALL instead of UNION in order to eliminate the
sorting/grouping step; don't know if it will work in your case but you loose
nothing to try it.

 

[dan]

looks like it's working....

I thought I had tried that - guess not?


I don't understand it but it worked
http://www.music.bizerks.com/lyrics.html

thanks

Try with UNION ALL instead of UNION in order to eliminate the
sorting/grouping step; don't know if it will work in your case but you
loose nothing to try it.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

I am trying to make a query from 2 tables
the first field (selection in the 1st table and song in the 2nd table) is
a text field of 105 chrs
the second field (lyrics in both tables) is a memo field

each query worked by itself but when I combine them with UNION the memo
field is truncated to about 237 chrs - is this somehow being changed to a
text field?

what am I doing wrong?

SELECT [CD Records - Access 2002].SELECTION, [CD Records - Access
2002].lyrics
FROM [CD Records - Access 2002]
WHERE ((([CD Records - Access 2002].lyrics)>"-"))
UNION SELECT [LYRICS DATA BASE - songs I don't have].song, [LYRICS DATA
BASE - songs I don't have].lyrics
FROM [LYRICS DATA BASE - songs I don't have];

 

[Sylvain Lafontaine]

When you don't use the UNION ALL command but the simpler UNION instead, the
result of the UNION is sorted and grouped in order to eliminate the
duplicates (even if there are none) but the sorting on a memo field involves
truncation; hence your first result.

If you don't have duplicates or don't want them to be eliminated; using
UNION ALL instead of UNION will also be faster.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

looks like it's working....

I thought I had tried that - guess not?


I don't understand it but it worked
http://www.music.bizerks.com/lyrics.html

thanks

Try with UNION ALL instead of UNION in order to eliminate the
sorting/grouping step; don't know if it will work in your case but you
loose nothing to try it.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

I am trying to make a query from 2 tables
the first field (selection in the 1st table and song in the 2nd table)
is a text field of 105 chrs
the second field (lyrics in both tables) is a memo field

each query worked by itself but when I combine them with UNION the memo
field is truncated to about 237 chrs - is this somehow being changed to
a text field?

what am I doing wrong?

SELECT [CD Records - Access 2002].SELECTION, [CD Records - Access
2002].lyrics
FROM [CD Records - Access 2002]
WHERE ((([CD Records - Access 2002].lyrics)>"-"))
UNION SELECT [LYRICS DATA BASE - songs I don't have].song, [LYRICS DATA
BASE - songs I don't have].lyrics
FROM [LYRICS DATA BASE - songs I don't have];