Solved

Union of 3 Access Tables - Error: "Too Many Fields Defined."?

Posted on 2004-10-07
42
2,499 Views
Last Modified: 2012-05-05
I have 3 Access tables with 182 fields and 150,000 records each.  All 3 tables share the same 182 fields.

When I try to Union them, I get an error: "Too Many Fields Defined".

I'm confused because they are the same 182 fields, why "Too Many Fields Defined"?  I didn't define any new fields!

It seems like there is a memory issue but isn't the error msg misleading or am I missing something?

My goal is to get the average of all 450,000 records by 18 predefined catergories.

Am I out of luck?
0
Comment
Question by:rx9
  • 15
  • 9
  • 6
  • +5
42 Comments
 
LVL 65

Expert Comment

by:Jim Horn
Comment Utility
An access table can only handle up to 255 fields, so perhaps your query SQL is not accurately set up.

Dump your query SQL here.

Also, just out of curiosity, why are you trying to UNION three tables with that many records?  Perhaps there is a workaround available that will meet your needs.

Hope this helps.
-Jim
0
 
LVL 12

Expert Comment

by:pique_tech
Comment Utility
In Access, there are two ways to do a UNION query:

TABLE tablename1
UNION
TABLE tablename2
UNION
TABLE tablename3
...

OR
SELECT * FROM tablename1
UNION
SELECT * FROM tablename2
UNION
SELECT * FROM tablename3
..

So as long as you're doing it one of those two ways, I don't get it either.

I could understand getting that error if you're trying something like
SELECT *
FROM tablenname1, tablename2, tablename3
0
 
LVL 3

Expert Comment

by:Informative
Comment Utility
Whenever I run into an error with a Union I try breaking it down.

Create Three separate queries then simply use the syntax

QUERY4:

Select * from query1 UNION Select * from Query2

If that works add the third to combine the first two queries with the third

QUERY3:
Select * from query4 UNION Select * from Query3

hope it helps.
0
 
LVL 3

Expert Comment

by:Informative
Comment Utility
correction

QUERY5:
Select * from query4 UNION Select * from Query3
0
 

Author Comment

by:rx9
Comment Utility
Here is my Union Query:

SELECT * FROM [07 SumofRates - GMWB Lapses, PWs, and Ann: Months 1-180 part 1] union all select * from  [09 SumofRates - GMWB Lapses, PWs, and Ann: Months 1-180 part 2] UNION ALL select  * from  [11 SumofRates - GMWB Lapses, PWs, and Ann: Months 1-180 part 3];

I know they are long names but they have to stay the same.

I also tried to union just two tables, same error.
0
 
LVL 50

Expert Comment

by:Steve Bink
Comment Utility
I think you need to revisit your design.  182 fields is a bit much, and points to the idea that your data is not normalized.  If you were to correctly design your tables, you could save yourself a lot of hassles exactly like this one.

Other than that, I have to agree with the other experts.  If you are using a UNION query, all three queries must have the same number of fields, and those fields should have the same data type as well.  Names are unimportant, as Access will take the names from the field list found in the first SELECT of the UNION.
0
 
LVL 65

Expert Comment

by:Jim Horn
Comment Utility
(1)  I'm not sure using *'s will guarantee that it equates the fields as the same
(2)  Table Names - Best to name them without characters or spaces, as they necessitate using square brackets [ ] in queries and vba, which is another thing to maintain/forget.  

0
 
LVL 3

Expert Comment

by:Informative
Comment Utility
Well another option for a workaround would be to simply create a temp table and append all three into one.
0
 
LVL 65

Expert Comment

by:Jim Horn
Comment Utility
Ditto Informative and routinet's comments, plus do you really need all 182 fields for the solution you're trying to build?  The more you can not include in your query, the simpler and faster this will execute.
0
 

Author Comment

by:rx9
Comment Utility
The reason I need to union all 3 is an intermediate step.  My goal is to get the average of all 450,000 records by 18 predefined catergories.  So, I thought first get the union of the 3 tables would be necessary.  Then, do the average.  But as you know, I'm stuck with the first step.
0
 
LVL 3

Expert Comment

by:Informative
Comment Utility
Combining the three into one table is what you are asking Access to do and to do it all "in memory" (a tall order in this case) when you use three select queries with UNIONs like that.  

If you provide table space it affords more effective resources to the process and then any conflicts should be more easily apparent (if errors occur their solution should be obvious like data type conflicts).
0
 

Author Comment

by:rx9
Comment Utility
Yes, I do need all 182 fields.  These 180 (15x12)  fields are monthly data for 15 yrs and the remaining 2 fields are ID and category.

My end result is a 181 x 18 tables, if this helps.
0
 
LVL 11

Expert Comment

by:Jokra_the_Barbarian
Comment Utility
Not sure if this is a one time deal or not, but what don't you just copy the one table and append the other 2 to it? You said all of the tables have the same columns.
0
 

Author Comment

by:rx9
Comment Utility
18 categories
0
 
LVL 3

Expert Comment

by:Informative
Comment Utility
Had another thought.  You might just move the data to SQL and run your UNION there.  Access is like SQL junior and runs into lots of capacity issues.
0
 

Author Comment

by:rx9
Comment Utility
This is not a one time deal, I need to do this multiple times and to different databases.

My main question is why can't my union query work (I sort of know the answer being the memory issue), but why do I get such a message saying "Too Many Fields Defined"?  I doubt that Microsoft would prompt such an unrelated error message, given that my syntax is correct.
0
 

Author Comment

by:rx9
Comment Utility
>You might just move the data to SQL and run your UNION there.  Access is like SQL junior and runs into lots of capacity issues.

How?
0
 
LVL 44

Expert Comment

by:GRayL
Comment Utility
Can you obtain the average from one table easily?   If so, collect the sum and the count instead of the average from each table and calculate the average from the three sets of numbers.
0
 
LVL 3

Expert Comment

by:Informative
Comment Utility
Easy go here and grab the free version of SQL 2005.  Its far more powerful than Access and free.

http://lab.msdn.microsoft.com/express/
0
 
LVL 11

Expert Comment

by:Jokra_the_Barbarian
Comment Utility
I think what's happening is that Access is counting the columns in each of the unions and adding them together. With that being said, the column limit is 255, so using your example of 3 tables with 182 fields each, that's 3*182=546 fields. The max you could have would be 85 from each table.
0
 
LVL 3

Expert Comment

by:Informative
Comment Utility
The size and complexity of the database sounds like it clearly has exceeded the capacity of Access.  If your overall database size will be under 4 gig - I'd strongly encourage you to move your tables over onto SQL.

You can even in all likely situations keep your Access application and simply link all the tables from SQL.  The only difference is, you can link to a SQL view which should support more complex views than might be possible in access.

Jokra is quite probably correct in the way Access is using aliased names for the 180+ fields in each union and quickly exceeding the 255 limit.  

Grays suggestion also has merit because it is possible but sounds like it would wind up being high-maintenance and overworked from a coding side.  Simpler is better.  It should work, but doesn't because of platform limits.  Try another platform.
0
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 
LVL 11

Accepted Solution

by:
Jokra_the_Barbarian earned 500 total points
Comment Utility
I just tested my theory, and I am correct. I created 3 tables with 86 columns each and attempted a union query which failed. I went into each table and deleted one column then attempted the union query again, it worked. This is why your union query failed with only 2 tables, because you still had 364 fields (182*2)
0
 
LVL 44

Expert Comment

by:GRayL
Comment Utility
I've just done a test and you have to preceed the * with the tablename

SELECT [07 SumofRates - GMWB Lapses, PWs, and Ann: Months 1-180 part 1].* FROM [07 SumofRates - GMWB Lapses, PWs, and Ann: Months 1-180 part 1] union all
select [09 SumofRates - GMWB Lapses, PWs, and Ann: Months 1-180 part 2].* FROM  [09 SumofRates - GMWB Lapses, PWs, and Ann: Months 1-180 part 2] UNION ALL select   [11 SumofRates - GMWB Lapses, PWs, and Ann: Months 1-180 part 3].* from  [11 SumofRates - GMWB Lapses, PWs, and Ann: Months 1-180 part 3];

or

SELECT
A.* FROM [07 SumofRates - GMWB Lapses, PWs, and Ann: Months 1-180 part 1] A union all select B.* from
[09 SumofRates - GMWB Lapses, PWs, and Ann: Months 1-180 part 2] B UNION ALL select  C.* from
[11 SumofRates - GMWB Lapses, PWs, and Ann: Months 1-180 part 3] C;
0
 
LVL 11

Expert Comment

by:Jokra_the_Barbarian
Comment Utility
rx9,

SQL 2000 should work with that number of columns. The max number of columns in a select statement is 4,096, a lot better than Access' 255.

Jokra
0
 
LVL 44

Expert Comment

by:GRayL
Comment Utility
I think!  I did not do a lot of testing - I may be blowing smoke.
0
 

Author Comment

by:rx9
Comment Utility
GRayL, I tried your two select statements, same error.  :(
0
 

Author Comment

by:rx9
Comment Utility
I think Jokra_the_Barbarian is correct, strangely enough, even though it's illogival, Access counts the total number of fields of the tables being merged, it returns an error "Too Many Fields Defined." when it's over 255.
0
 
LVL 16

Expert Comment

by:GreymanMSC
Comment Utility
It's not all that illogical.  The union is returning three record sets, each with 182 fields.  It's still 546 columns, they are just stacked three on top of each other.

 [ ][ ]...
 [ ][ ]...
 [ ][ ]...
rather than
 [ ][ ]...[ ][ ]...[ ][ ]...
0
 
LVL 3

Expert Comment

by:Informative
Comment Utility
In the future when you have a problem and need an answer I hope being told you cant do that will be all you are looking for as an "ANSWER" because the people who spend time giving you the correct answer to your problem (which is go to the SQL platform) didnt get any points and will not be helping you in the future.
0
 

Author Comment

by:rx9
Comment Utility
Informative, I do appreciate your help but really your answer wasn't as to the point as Jokra_the_Barbarian and I don't feel comfortable at all using something totally brand new to me (such as SQL as you suggested).  Besides, people are more familiar with Excel and I would rather wrap up the last couple steps in Excel so that everyone would know what I'm doing.

Again, I do appreciate your help but if you won't be helping me in the future, it's fine with me.
0
 
LVL 44

Expert Comment

by:GRayL
Comment Utility
Interesting - and Informative!  Besides I had a look at SQL and it says you need XP Professional to run it. I think you did the right thing rx9.  
0
 
LVL 3

Expert Comment

by:Informative
Comment Utility
Well an answer is supposed to be a solution.  Upgrading to SQL platform or creating a temp table are in fact solutions and telling him he cannot do what he wants is a marginal answer at best.  If several people put in the time to help then splitting points is really the fair thing to do.  

Both of my viable solutions were posted before Jokra said "the column limit is 255" so you cant get there from here.

I had further info and a possible Access workaround which might go those solutions one better but will not be sharing.  Thank you.

0
 
LVL 50

Expert Comment

by:Steve Bink
Comment Utility
First the facts:

You can run MSSQL on desktop platforms, but only the Manager.  The actual database engine will not install unless you are using a server platform (such as Win2k Server).  The desktop version contains all the tools for connectivity and all the utilities....it just does not run a database service.  So in this particular scenario, you would still need some type of server platform to be able to run it.  I'm not sure about SQL Express, as I've never worked with it.

rx9: Have you found a work-around to use for this issue?


Now for the opinion randomly injected with fact:

1) Telling someone "you can't do that" is hardly a marginal answer, if that really is the case.  You cannot have more than 255 fields in Access.  Presenting this as an answer is valid because that IS the answer to the question of "what is wrong with this".

2) The questioner felt MSSQL was not a valid solution due to their experience.  Solutions are only solutions if they are workable for the one who needs it, and db developers do not always have strict control over their environment, or even over the design of the db.  Most people who come here are not professional developers...they are regular employees dumped into the "crash-n-burn" track of db design by their bosses.

3) This site is about helping people (and, for me, honing my own skills), not about the points.  Refusing to help someone just because they did not pick your solution previously, which they considered unworkable, is just poor taste.  Withholding information that could help not only the questionner, but ANYONE viewing this PAQ, is spiteful and petty.  My own solution (redesign) was barely noticed enough to be ignored, but *I* would consider it to be a MANDATORY first step to solving this problem.  Obviously, the questioner's opinion differed, and they are entitled to it.

4) http://www.experts-exchange.com/help.jsp#hs4 ,  specifically http://www.experts-exchange.com/help.jsp#hi61  
0
 
LVL 3

Expert Comment

by:Informative
Comment Utility
I've been chewed out myself by helpful people on here for something as simple as giving a B grade.

Since Access would not construct the desired view in memory, using a temporary table is the correct solution to how-to construct the desired view with his current tool (Access).  

This solution was also offered and disregarded because he felt it might be too slow.  He did not say how do I create this view to run quickly on a substandard system with Access, to which the correct answer IS you can't.

There are several ways to increase the speed of temp tables CONSIDERABLY among other ideas to which I asked why should I help further when I've already been stiffed on the points and basically told my help was not useful and in effect no further help was needed?
0
 
LVL 3

Expert Comment

by:Informative
Comment Utility
I've read your links and my asking in a round about way why I was not given any points for perfectly good answers is neither derogatory nor sarcastic in any way!  You are certainly entitled to your opinion and so am I.

Your calling my questions about the decision to call "you cant do that" the complete solution petty not to mention "rude derogatory and sarcastic" is itself very rude and certainly more rude than my asking hey wheres my points.
0
 
LVL 3

Expert Comment

by:Informative
Comment Utility
Routinet before you go calling other experts petty rude derogatory and sarcastic I would suggest you look at
http://www.experts-exchange.com/Applications/MS_Office/Excel/Q_21160112.html#12262137
while you were posting your rude comments this user got his solution in part from me long after the points had been awarded, so Obviously I'm not in it for the points but to help people.  I have no issue whatsoever with helping after the points have been assigned and I think the facts support my argument.

I apologize for suggesting that I would not help rx9 in the future.  Of course I will.
0
 
LVL 50

Expert Comment

by:Steve Bink
Comment Utility
Just because your first suggestions were thrown out does not mean help is not needed.  I've seen questions go for months with idea after idea, only to end up using one minor, previously ignored suggestion that was mentioned back at the start.  I, for one, would be interested in knowing any potential work-arounds for this issue, aside from the obvious already mentioned in the thread.  The 255-field limit in Access can be annoying, and I had not heard of this facet of the problem before (meaning the limit being exceeded due to a UNION).  

Perhaps if you explained the 'better' solution, rx9 could decide if it applies enough to warrant changing their accepted answer.
0
 
LVL 3

Expert Comment

by:Informative
Comment Utility
Very well Simply because I suggested the only way to create the view was to throw the three tables together with append queries into a temp table does not instantly mean this is a non-solution.

I would suggest if this temp table is a performance obstacle that he then simply add additional gigs of ram to the machine or moving the MDB to a server with minimum 4 gigs or RAM and drop the mdb into a huge 2 gig or more ramdrive.  

This would make the temporary table solution in effect a temporary memory table and it should run just as fast as running it as an in memory query.  You would also want to drop the temp table and run the standard compact database command each time between queries but that would work.  Excel only handles 64k lines or data and he said he had 150k line in one table so I am curious how he plans to accomplish his output with Excel.

I'm also wondering why his ID was created in January and hadnt been used for so long?  Some people don't really care who gets points for being helpful because they log with a new ID every time and to that issue I would caution people as with ebay to not get too caught up helping people without a solid history of providing fair handshakes to those that put forth efforts in their interest.
0
 
LVL 3

Expert Comment

by:Informative
Comment Utility
If you have to work with a small ramdrive like 1 gig just put an empty mdb with a single table and link that one table so the regular mdb is on physical disk and just the linked temp table in in another mdb on the ramdrive.
0
 
LVL 44

Expert Comment

by:GRayL
Comment Utility
That bout was a "one fall - 30 minute time limit" and they are still on their feet. The World Wrestling Federation never had it this good!  Handshakes and on to the next thread - huh?
0
 
LVL 3

Expert Comment

by:Informative
Comment Utility
Your humor is appreciated GrayL - please dont misunderstand my minor rant as being in any way angry.  I was merely voicing my genuine disappointment that the user had given up and settled for the "you cant do that" answer.  

We coders can make a thread last all week, just like old chewing gum, long after the flavor has left the building.
Real programmers don't give up easily - I was prepared to walk him through moving it to SQL if he wanted but they have to be prepared to do some of the legwork and of course acknowledging the contribution would have been the right thing to do imo (shrug).
0
 
LVL 44

Expert Comment

by:GRayL
Comment Utility
Well said, now for the next challenge ...  ;)
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now