UNION vs. UNION ALL query (1 Viewer)

Baldrick

Registered User.
Local time
Today, 16:58
Joined
Jul 2, 2001
Messages
35
I am writing reports from a SQL Server DB with Access.

Access will let me do a UNION ALL query, but a UNION query (which would eliminate dupes)does not work. I get an error message about Can't do union on Memo or OLE Object field...

Why would it work on one and not the other?
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 16:58
Joined
Feb 19, 2002
Messages
42,976
A Union All query does not need to do any grouping. It simply returns all selected rows from each recordsource. A Union query needs to find duplicates in order to eliminate them. This requires the creation of a string comprised of EVERY character from each row so that the recordset can be sorted and then each row can be compared to the previous one to identify the duplicate ones. Since memo fields can be up to 64k, this exceeds dramatically the limit Access uses for a record length which is 2k. Even though the individual memo fields may contain only small numbers of characters, there is no way for Access to determine this in advance.

A possible solution is to create queries that extract only the first 255 characters from each memo field using the Left() function. Then base the union query on the other queries rather than the underlying tables.
 

Baldrick

Registered User.
Local time
Today, 16:58
Joined
Jul 2, 2001
Messages
35
Thanks for the feedback Pat. I'll try your suggestion.
 

Baldrick

Registered User.
Local time
Today, 16:58
Joined
Jul 2, 2001
Messages
35
Pat,

Thanks for your advice. I used the Left() function in the existing data field in my query "CLT_IMPCT: Left([CLT_IMPCT_TXT],255)" and it worked beautifully.
 

Users who are viewing this thread

Top Bottom