Skip to content. | Skip to navigation

Personal tools
You are here: Home Knowledge Microsoft Access Error 3163 with Union Queries

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.

 

 

 

Document Actions
« June 2017 »
June
SuMoTuWeThFrSa
123
45678910
11121314151617
18192021222324
252627282930