Personal tools
You are here: Home Knowledge Microsoft Access Error 3163 with Union Queries
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




Computer Professionals


Learning What You Don't Know
Can Help You Earn More and
Give You Greater Security

Improve your computer career or business with reliable information from True Blade Systems.

Subscribe to our free newsletter.
No obligation - unsubscribe anytime.

Name
Email

We like privacy and won't share your info. Subscribe now to get valuable career and consulting information from True Blade Systems - free.