Fix Access UNION Query Aggregate Function Error with Long Text
Question details
The user is experiencing an aggregate-function error in an Access UNION query when attempting to use the 'Last' function to prevent a Long Text field from being truncated.
- Product
- Microsoft Access
- Device & OS
- not provided
- Scenario
- Combining records from multiple tables with similar structures (e.g., Cashier and Kluis entities) into a single result set without losing data in Long Text fields.
- Observed behavior
- Using the 'Last' aggregate function on a Long Text field within the UNION query results in an error or undefined behavior, as 'Last' is unreliable without an explicit ordering clause.
Verify the primary keys or unique identifiers in your source tables (like Cashier and Kluis), as you will need them to create reliable joins in subsequent queries.
Exclude Long Text from the UNION Query and Join Later
The most reliable method to prevent Long Text truncation and avoid aggregate function errors is to perform the UNION operation on standard fields first, then join the results back to the original tables.
Microsoft Access automatically treats Long Text fields as standard Short Text (truncating at 255 characters) when processing UNION queries to remove duplicates. Bypassing the Long Text field during the UNION phase prevents this limitation entirely.
Open SQL View and write your UNION query selecting only the primary key identifiers and any other required standard fields from your tables (e.g., Cashier and Kluis). Completely omit the Long Text field.
Save this initial query with a recognizable name, such as 'qryBaseUnion'.
Go to the Create tab and open Query Design. Add your newly saved 'qryBaseUnion' along with the original tables that contain the Long Text fields.
Create a join between the primary keys in 'qryBaseUnion' and the original tables. Add the Long Text field to your query grid from the original tables, then run the query.
Define an Explicit ORDER BY Clause
If you must use the 'Last' function, you need to establish a deterministic sort order so Access knows which record is actually the last one.
Use UNION ALL Instead of UNION
Switching your syntax to UNION ALL bypasses the duplicate-checking process that causes Access to truncate Long Text fields.
Looking for a Lightweight Alternative to Microsoft Office?
While Microsoft Access handles highly complex database tasks, WPS Office is an exceptional free alternative for managing your daily document, spreadsheet, and presentation needs. It is lightweight, fast, and fully compatible with mainstream Office formats.
- 1. Download WPS Office: Visit the official WPS website to download the free installation package for your operating system.
- 2. Install and Set Up: Run the installer and follow the quick prompts. WPS Office is lightweight and installs in minutes.
- 3. Open Your Files Seamlessly: Double-click any existing Microsoft Office document to open it in WPS Office without losing any formatting.
Frequently Asked Questions
Why does Microsoft Access truncate Long Text fields to 255 characters in UNION queries?
A standard UNION query automatically groups records to eliminate duplicates. Because Access cannot group on Long Text (formerly Memo) fields, it converts them to Short Text and cuts them off at 255 characters to perform the grouping operation.
What is the difference between UNION and UNION ALL?
UNION combines the results of two queries and removes duplicate rows, which requires data grouping. UNION ALL simply appends the results together without checking for duplicates, meaning it is faster and does not trigger Long Text truncation.
Why does the 'Last' aggregate function return the wrong data?
In Microsoft Access, 'Last' refers to the chronological sequence in which records were physically entered into the table, not the alphabetic or numeric order. Unless an explicit ORDER BY clause is applied, 'Last' will return unpredictable results.
