Large Union query truncates memo field 1

[Cypdog]

I have a large union query (joining 20+ queries) that is doing exactly what I want only it is truncating one of the memo fields. These fields are not truncated in the source queries. Each one of the 20+ queries returns values for different sets of scenarios and then the union query combines the data so they can be exported to excel and sorted either by location or employee. Is there anyway to keep the memo field from truncating in the union query? I have already prevented it from happening in the source queries but those methods do not seem to work for the union query. Thanks in advance.

 

[PHV]

I have already prevented it from happening in the source queries
How you did that ?

 

[Cypdog]

Usually I put first in the total column of the memo field that is being truncated, I think that is the case for all of the source queries here that decided to truncate my memo field.

 

[Cypdog]

here is what it looks like, i am not super skilled at sql.

SELECT [query1].[field1], [query1].[field2], [query1].[field3], [query1].[field4], [query1].[field5], [query1].[field6], [query1].[field7], [query1].[field8], [query1].[field9], [query1].[field10]
From [query1]
union
SELECT [query2].[field1], [query2].[field2], [query2].[field3], [query2].[field4], [query2].[field5], [query2].[field6], [query2].[field7], [query2].[field8], [query2].[field9], [query2].[field10]
From [query2]
union
SELECT [query3].[field1], [query3].[field2], [query3].[field3], [query3].[field4], [query3].[field5], [query3].[field6], [query3].[field7], [query3].[field8], [query3].[field9], [query3].[field10]
From [query3]
union
repeated for all queries


This is repeated again until all the queries are connected by the union statement. Occasionally field4 and field8 are replaced by [firstoffield4] and [firstoffield8] where it was apropriate to make those source queries work properly.

 

[Genimuse]

No comment on the query, but I have found that Jet SQL seems to truncate memos less often if they are the very last items in the list of requested fields. I'm not sure you could do that with the unions, however, but perhaps the trick will help if they're at least the last items in each individual select.

 

[PHV]

Have you tried UNION ALL ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Cypdog]

Yes, I have tried that and it still truncates field4 and field8 if they are over 255. but it is still not truncated in the source queries just when it comes out of the union query.

 

[Cypdog]

Here is what wound up working: I made a second query using the union query and the table containing the un-truncated memo field, everything in the same order only pulling the memo fields from the source rather than from the union query. This gave me too many records, so I then used group by and placed "first" in the memo fields that truncated. This still gave me too many records so in the sql statement i used SELECT DISTINCTROW. That combination solved my problem. I don't know why that last combination works but I have used it before and have had similar success. Thanks to all for your help, i hope someone else can use this.

 

[vjh]

Your solution also worked for me. My query was relatively simple, but the UNION truncated my memo field. Seems a bit of a roundabout way of going at it, but since it worked, I'm happy!

Thanks for posting your solution.

vj

 

[jrshaw03]

Cypdog,

Hopefully you are still active on the forum. Could you please post some of the SQL you used to overcome the truncation problem?

Thank you very much,

Jimmy