Union – too many fields!

A

alex

Hello everyone,

Using Access ’03…

I’ve searched the archives of this group but have not found a specific
answer to the question below:

I have a dozen or so tables, populated by as many people. The tables
are not that big (only a few rows), albeit a bit wide (30 or so
columns).

The tables are used in an ODBC connection with ORACLE. I need to
combine them into one table in Access. I’ve tried using a union
query, which I’ve used before in other applications. I get the error
too many fields defined! I know what that means, but I can’t
understand why I cannot combine 15 tables into one, especially when
the combined table will only possess 30 columns and about a hundred
rows. Where does Access derive 255 from this? Is it multiplying 15 *
30?

Any thoughts?

Thanks,
alex
 
J

John Spencer

I think (a guess in other words) that the field count in a union query
includes one for every field in every query in the union query. So, yes - the
count is 15*30 which definitely exceeds the 255 limit.

You *M*I*G*H*T* be able to create several saved union queries and then union them.

If that doesn't work you could define a table with the proper structure and
populate it by using a series of append queries. And you can clear the table
by deleting all records in it before doing an Update/Append. This process
will bloat your database, so you will need to compact on a regular basis.

An option is to use a temp database to hold the data.
See Tony Toews website
http://www.granite.ab.ca/access/temptables.htm
for an example

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
 
A

alex

I think (a guess in other words) that the field count in a union query
includes one for every field in every query in the union query.  So, yes - the
count is 15*30 which definitely exceeds the 255 limit.

You *M*I*G*H*T* be able to create several saved union queries and then union them.

If that doesn't work you could define a table with the proper structure and
populate it by using a series of append queries.   And you can clear the table
by deleting all records in it before doing an Update/Append.  This process
will bloat your database, so you will need to compact on a regular basis.

An option is to use a temp database to hold the data.
   See Tony Toews websitehttp://www.granite.ab.ca/access/temptables.htm
for an example

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County










- Show quoted text -

Thanks John. I appreciate the feedback.

I thought about making several small union querys and unioning those
queries...seems like a lot of work for a rather simple problem. I'm
sure I'll get it eventually...maybe I'll try the append query
suggestion.

alex
 
L

Lynn Trapp

Hello everyone,

Using Access ’03…

I’ve searched the archives of this group but have not found a specific
answer to the question below:

I have a dozen or so tables, populated by as many people. The tables
are not that big (only a few rows), albeit a bit wide (30 or so
columns).

The tables are used in an ODBC connection with ORACLE. I need to
combine them into one table in Access. I’ve tried using a union
query, which I’ve used before in other applications. I get the error
too many fields defined! I know what that means, but I can’t
understand why I cannot combine 15 tables into one, especially when
the combined table will only possess 30 columns and about a hundred
rows. Where does Access derive 255 from this? Is it multiplying 15 *
30?

Any thoughts?

Thanks,
alex

In addition to John Spencer's good suggestions, have you considered
creating a view in Oracle and connecting to that view? Oracle will
allow you to create a Union query with a significantly higher number
of columns.
Lynn Trapp
www.ltcomputerdesigns.com
 
A

alex

In addition to John Spencer's good suggestions, have you considered
creating a view in Oracle and connecting to that view? Oracle will
allow you to create a Union query with a significantly higher number
of columns.
Lynn Trappwww.ltcomputerdesigns.com- Hide quoted text -

- Show quoted text -

Thanks Lynn for your help...I think I've got the union queries to
work. One issue, however, while I've got yours and maybe John's ear
(eyes). My individual tables have a hyperlink field. When I perform
the union query and subsequent make table query (to combine the tables
into one) the hyperlinks are gone. I then need to go into the new
table (master) and change the data type. Any idea on how to
automatically create the table with a hyperlink datatype?

alex
 
A

a a r o n _ k e m p f

if you're getting the message 'too many fields' it means that you need
to move to SQL Server

Oracle hasn't taken Oracle Forms very seriously for about a decade..
and JET Is too flaky for real world usage..
So move to SQL Server and ADP.

THanks
 
A

a a r o n _ k e m p f

why would you EVER use the hyperlink field?

I don't get it

store it as plain text.. deal with the clickability in the presenation
tier
 
L

Lynn Trapp

if you're getting the message 'too many fields' it means that you need
to move to SQL Server

Oracle hasn't taken Oracle Forms very seriously for about a decade..
and JET Is too flaky for real world usage..
So move to SQL Server and ADP.

THanks

Aaron,

I'm sure that Alex doesn't have any choice about whether to move to
SQL Server or not. That decision is probably made way above his head.
Lynn Trapp
www.ltcomputerdesigns.com
 
J

John Spencer

Where do the hyperlinks change? In the union query - you are out of luck.

If you are using a make table query based on the union queries, don't. Design
the table first and then use the union queries as a source for append queries.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
 
A

alex

Where do the hyperlinks change?  In the union query - you are out of luck.

If you are using a make table query based on the union queries, don't.  Design
the table first and then use the union queries as a source for append queries.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County






- Show quoted text -

Thanks John, I'll give it a try. Thanks everyone for your help!
 
P

Please Learn to Read

Thank you for your interest, but the original question was not "In your
opinion, what server database should I use?" The original question dealt with
union queries is a specified environment.

And, as another respondent noted, few people at the worker level have the
option of changing the server database that their company uses.
 
J

John W. Vinson/MVP

One issue, however, while I've got yours and maybe John's ear
(eyes). My individual tables have a hyperlink field. When I perform
the union query and subsequent make table query (to combine the tables
into one) the hyperlinks are gone.

Try using UNION ALL rather than UNION. UNION will attempt to delete
duplicates from the recordset; since a hyperlink field is a type of
Memo field, it's harder to deduplicate. UNION ALL will run faster and
also avoid this problem.
 
A

a a r o n _ k e m p f

SQL Server is merely an optional component on the office CD.

it's been included the past 3 editions.

-Aaron
 
P

Pete D.

The answer is in your question, although you never see more than the 30
columns in the query process access needs to see all the fields in the
tables to create the final table/results. Within this process access is
creating to many columns. To get around this you should build your
table/query up in many small ones appending each to the previous results
until you have the final product. Now this begs the question on wether your
database is correctly designed as a relational database should be. What you
describe is something I only normally run into when taking old flat
datafiles and moving them to relational tables. Pete
 
A

a a r o n _ k e m p f

sounds to me-- like a good old fashioned ACCESS BUG.

Stop blaming the messenger-- move to SQL Server and ADP.
Things just work there.

-Aaron
 
P

Pete D.

Yep, and when my 100gb hard drive quits allowing writes to it because is too
full and I never house keep it. This must be a bug in the hard drive.
Couldn't be that it is just too full. So when you hit the limits in SQL it
just keeps on working fine? Sounds like to much vacuum around your ears
again. Eat something!

sounds to me-- like a good old fashioned ACCESS BUG.

Stop blaming the messenger-- move to SQL Server and ADP.
Things just work there.

-Aaron
 
A

a a r o n _ k e m p f

What _EXACTLY_ are you trying to say?
Yes, if your hard drive is buggy-- time to get a new hard drive.

When your database is too buggy-- it is time to move to SQL Server

that's all I'm trying to say.

I don't hit limits in SQL Server.
SQL Server 2005 Express works great for some of my needs..

SQL Standard, SQL Enterprise for others.

If your harddrive randomly started filling up when you tried to use it
more than once at the same time-- yes I would throw it away.

-Aaron
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top