Access Union Query with Multiple Tables

[chittenden]

I am trying to use a union query to combine the records in 3 tables with identical field structure. I am receiving a message that states "Too Many Fields Defined" Is there a limitation to the number of fields which can be used in a union query. Each of my tables has 103 fields. When I eliminated one table from the query it works fine. The actual query is below:

SELECT [Address (1)], [Address (2)], [Birth Date], [City], [Client Number], [Company Name], [Date (Hire)], [Date (Last Check)], [Date (Last P\E)], [Date (Last Raise)], [Date (Next Raise)], [Date (Termination)], [Department], [Dependants (Fed)], [Dependants (State)], [Division], [Gender], [Home Labor Number], [Income Tax State], [Marital Status (Fed)], [Marital Status (State)], [Name (First)], [Name (Last)], [Name (MI)], [Phone], [Rate (1)], [Rate (2)], [Rate (3)], [Salary], [SSN], [State], [Status], [Title], [Weeks Worked], [Zip Code], [Regular Accum (H)], [Regular Accum ($)], [O/Time Accum (H)], [O/Time Accum ($)], [Salary Accum ($)], [Other Accum (H)], [Other Accum ($)], [Vacation Accum (H)], [Vacation Accum ($)], [Sick Accum (H)], [Sick Accum ($)], [Holiday Accum (H)], [Holiday Accum ($)], [Persnal Accum (H)], [Persnal Accum ($)], [Bonus Accum ($)], [Wedding Accum ($)], [Funeral Accum ($)], [Monthly Accum ($)], [Grpterm Accum ($)], [Special Accum ($)], [ST Sick Accum ($)], [LT_3PSK Accum ($)], [Clergy Accum ($)], [Clerg2 Accum ($)], [Revhous Accum ($)], [Clrgtx Accum ($)], [Clrhse Accum ($)], [Autoclr Accum ($)], [Auto Tx Accum ($)], [Stipend Accum ($)], [1099$$ Accum ($)], [Hlthpre Per Pay], [Depcare Per Pay], [Med 125 Per Pay], [FICA Accum], [3PSFica Accum], [Co Fica Accum], [Med Fica Accum], [3PSMedc Accum], [Co Medc Accum], [T S A Per Pay], [TSA Ex Per Pay], [401k $ Per Pay], [401k % Per Pay], [Co401k Per Pay], [401k Cu Per Pay], [Multi Code Accum], [Multi Code Accum 2], [Fed Wth Accum], [3PSFedw Accum], [State Accum], [Chldsup Accum], [Chldsup Accum 2], [Advance Accum], [MVP Accum], [Health Accum], [Lifeins Accum], [Disabil Accum], [Reimb Per Pay], [Misc-1 Per Pay], [T L Pay Per Pay], [Pension Per Pay], [Pledge Per Pay], [A/R Per Pay], [Un Way Per Pay], [Uniform Per Pay], [HSACFee Per Pay]
FROM D102;

UNION ALL SELECT [Address (1)], [Address (2)], [Birth Date], [City], [Client Number], [Company Name], [Date (Hire)], [Date (Last Check)], [Date (Last P\E)], [Date (Last Raise)], [Date (Next Raise)], [Date (Termination)], [Department], [Dependants (Fed)], [Dependants (State)], [Division], [Gender], [Home Labor Number], [Income Tax State], [Marital Status (Fed)], [Marital Status (State)], [Name (First)], [Name (Last)], [Name (MI)], [Phone], [Rate (1)], [Rate (2)], [Rate (3)], [Salary], [SSN], [State], [Status], [Title], [Weeks Worked], [Zip Code], [Regular Accum (H)], [Regular Accum ($)], [O/Time Accum (H)], [O/Time Accum ($)], [Salary Accum ($)], [Other Accum (H)], [Other Accum ($)], [Vacation Accum (H)], [Vacation Accum ($)], [Sick Accum (H)], [Sick Accum ($)], [Holiday Accum (H)], [Holiday Accum ($)], [Persnal Accum (H)], [Persnal Accum ($)], [Bonus Accum ($)], [Wedding Accum ($)], [Funeral Accum ($)], [Monthly Accum ($)], [Grpterm Accum ($)], [Special Accum ($)], [ST Sick Accum ($)], [LT_3PSK Accum ($)], [Clergy Accum ($)], [Clerg2 Accum ($)], [Revhous Accum ($)], [Clrgtx Accum ($)], [Clrhse Accum ($)], [Autoclr Accum ($)], [Auto Tx Accum ($)], [Stipend Accum ($)], [1099$$ Accum ($)], [Hlthpre Per Pay], [Depcare Per Pay], [Med 125 Per Pay], [FICA Accum], [3PSFica Accum], [Co Fica Accum], [Med Fica Accum], [3PSMedc Accum], [Co Medc Accum], [T S A Per Pay], [TSA Ex Per Pay], [401k $ Per Pay], [401k % Per Pay], [Co401k Per Pay], [401k Cu Per Pay], [Multi Code Accum], [Multi Code Accum 2], [Fed Wth Accum], [3PSFedw Accum], [State Accum], [Chldsup Accum], [Chldsup Accum 2], [Advance Accum], [MVP Accum], [Health Accum], [Lifeins Accum], [Disabil Accum], [Reimb Per Pay], [Misc-1 Per Pay], [T L Pay Per Pay], [Pension Per Pay], [Pledge Per Pay], [A/R Per Pay], [Un Way Per Pay], [Uniform Per Pay], [HSACFee Per Pay]
FROM D164;

UNION ALL SELECT [Address (1)], [Address (2)], [Birth Date], [City], [Client Number], [Company Name], [Date (Hire)], [Date (Last Check)], [Date (Last P\E)], [Date (Last Raise)], [Date (Next Raise)], [Date (Termination)], [Department], [Dependants (Fed)], [Dependants (State)], [Division], [Gender], [Home Labor Number], [Income Tax State], [Marital Status (Fed)], [Marital Status (State)], [Name (First)], [Name (Last)], [Name (MI)], [Phone], [Rate (1)], [Rate (2)], [Rate (3)], [Salary], [SSN], [State], [Status], [Title], [Weeks Worked], [Zip Code], [Regular Accum (H)], [Regular Accum ($)], [O/Time Accum (H)], [O/Time Accum ($)], [Salary Accum ($)], [Other Accum (H)], [Other Accum ($)], [Vacation Accum (H)], [Vacation Accum ($)], [Sick Accum (H)], [Sick Accum ($)], [Holiday Accum (H)], [Holiday Accum ($)], [Persnal Accum (H)], [Persnal Accum ($)], [Bonus Accum ($)], [Wedding Accum ($)], [Funeral Accum ($)], [Monthly Accum ($)], [Grpterm Accum ($)], [Special Accum ($)], [ST Sick Accum ($)], [LT_3PSK Accum ($)], [Clergy Accum ($)], [Clerg2 Accum ($)], [Revhous Accum ($)], [Clrgtx Accum ($)], [Clrhse Accum ($)], [Autoclr Accum ($)], [Auto Tx Accum ($)], [Stipend Accum ($)], [1099$$ Accum ($)], [Hlthpre Per Pay], [Depcare Per Pay], [Med 125 Per Pay], [FICA Accum], [3PSFica Accum], [Co Fica Accum], [Med Fica Accum], [3PSMedc Accum], [Co Medc Accum], [T S A Per Pay], [TSA Ex Per Pay], [401k $ Per Pay], [401k % Per Pay], [Co401k Per Pay], [401k Cu Per Pay], [Multi Code Accum], [Multi Code Accum 2], [Fed Wth Accum], [3PSFedw Accum], [State Accum], [Chldsup Accum], [Chldsup Accum 2], [Advance Accum], [MVP Accum], [Health Accum], [Lifeins Accum], [Disabil Accum], [Reimb Per Pay], [Misc-1 Per Pay], [T L Pay Per Pay], [Pension Per Pay], [Pledge Per Pay], [A/R Per Pay], [Un Way Per Pay], [Uniform Per Pay], [HSACFee Per Pay]
FROM D166;


Thanks

 

[Golom]

I can't find anything that specifically says so but you might try something like

SELECT ... all those fields ...
FROM 
(
SELECT ... all those fields ...
FROM D102

UNION ALL 
SELECT ... all those fields ...
FROM D164
)

UNION ALL 
SELECT ... all those fields ...
FROM D166;

so that it processes the sub-query with only one UNION ALL and then combines that with the third table.

I have no idea if this will work.

 

[Remou]

The specifications (

http://office.microsoft.com/en-us/access/HP051868081033.aspx)

gives 255 as the maximum number of fields. You seem to have >300.

 

[chittenden]

That actually worked

Thanks again

 

[dhookom]

Two IMHO comments:
1) normalize
2) find and use an accepted naming convention

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]