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
If you enjoyed this article, we invite you to learn more about how you can become a high achieving computer professional.
