• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2951
  • Last Modified:

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

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
rx9
Asked:
rx9
  • 15
  • 9
  • 6
  • +5
1 Solution
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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
 
pique_techCommented:
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
 
InformativeCommented:
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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
InformativeCommented:
correction

QUERY5:
Select * from query4 UNION Select * from Query3
0
 
rx9Author Commented:
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
 
Steve BinkCommented:
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
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
(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
 
InformativeCommented:
Well another option for a workaround would be to simply create a temp table and append all three into one.
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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
 
rx9Author Commented:
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
 
InformativeCommented:
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
 
rx9Author Commented:
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
 
Jokra_the_BarbarianCommented:
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
 
rx9Author Commented:
18 categories
0
 
InformativeCommented:
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
 
rx9Author Commented:
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
 
rx9Author Commented:
>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
 
GRayLCommented:
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
 
InformativeCommented:
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
 
Jokra_the_BarbarianCommented:
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
 
InformativeCommented:
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
 
Jokra_the_BarbarianCommented:
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
 
GRayLCommented:
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
 
Jokra_the_BarbarianCommented:
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
 
GRayLCommented:
I think!  I did not do a lot of testing - I may be blowing smoke.
0
 
rx9Author Commented:
GRayL, I tried your two select statements, same error.  :(
0
 
rx9Author Commented:
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
 
GreymanMSCCommented:
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
 
InformativeCommented:
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
 
rx9Author Commented:
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
 
GRayLCommented:
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
 
InformativeCommented:
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
 
Steve BinkCommented:
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
 
InformativeCommented:
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
 
InformativeCommented:
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
 
InformativeCommented:
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
 
Steve BinkCommented:
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
 
InformativeCommented:
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
 
InformativeCommented:
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
 
GRayLCommented:
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
 
InformativeCommented:
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
 
GRayLCommented:
Well said, now for the next challenge ...  ;)
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 15
  • 9
  • 6
  • +5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now