Document Actions
Microsoft Access Error 3163 with Union Queries
This article contains the solution to this problem: When trying to use the SQL Statement 'Union' to combine the results of two different queries, I got an error that says: "The field is too small to accept the amount of data you attempted to add. Try inserting or pasting less data." In my case I was trying to query SugarCRM tables for Notes and Emails and combine those results into a single query.
The Problem: Combining Two Microsoft Access Queries into a Single Query using the SQL Statement 'UNION' results in Microsoft Access Error 3163
My original query which produced error 3163 looked like this:
SELECT [emails-for-accounts-query].*
FROM [emails-for-accounts-query];
UNION
SELECTÂ [notes-for-accounts-query].*
FROM [notes-for-accounts-query];
where the two queries in the above statement are as follows:
emails-for-accounts-query
SELECT
accounts.name AS account_name, emails.date_entered,
emails.name AS subject, emails.description AS [note]
FROM accounts INNER JOIN emails ON accounts.id=emails.parent_id
ORDER BY accounts.name, emails.date_entered;
notes-for-accounts-query
SELECT
accounts.name AS account_name, notes.date_entered,
notes.name AS subject, notes.description AS [note]
FROM notes INNER JOIN accounts ON notes.parent_id = accounts.id
ORDER BY accounts.name, notes.date_entered;
The Solution: Use the SQL Statement 'ALL' and/or 'ORDER BY' on the results
The following query works correctly:
SELECT [emails-for-accounts-query].*
FROM [emails-for-accounts-query];
UNION ALL
SELECT [notes-for-accounts-query].*
FROM [notes-for-accounts-query]
ORDER BY account_name, date_entered;
By using the 'ALL' statement and/or the ORDER BY syntax the error goes away and the correct result is returned.
See also: http://support.microsoft.com/?scid=kb;en-us;896950&spid=2509&sid=285