"Query is too complex." Error

  • Thread starter Thread starter Todd
  • Start date Start date
T

Todd

Hello All,

I am trying to create a query that concatenates 3 the columns and rows
together. Unfortunately, I get an error saying "Query is too complex." The
tables in question each have 200 columns and I'm trying to make it look
like:

Table1 & "-" & Table2 & "-" & Table3

and results in

1-222222-9

I've tried to scale it down to 2 tables and I can get a few more rows, but I
still get the same error.

Is there a maximum amount of columns that you can have in a query? Is there
a maximum on a SQL statement? Would this error also appear if I tried an
Update query (update a new table to get the desired content)? Is there an
easier way?

I need to be able to do this to 3 sets of 3 tables.

Any help is appreciated

Thanks

Todd
 
There's a limit of 255 fields in a recordset, which I suspect is your
problem. (BTW, search help for "specifications" to get a list of Access'
technical limits.)

But I'm very suspicious of 200-field tables. What are you modelling?

--
Rebecca Riordan, MVP

Designing Relational Database Systems
Microsoft SQL Server 2000 Programming Step by Step
Microsoft ADO.NET Step by Step

http://www.microsoft.com/mspress

Blessed are they who can laugh at themselves,
for they shall never cease to be amused...
 
I recreated the SQL statement and went back and deleted until the error
stopped occurring. I counted the columns and had a total of 170 (the Index,
plus H0 - H168). Anything beyond the 170th column resulted in the "Query is
too complex." Error.

I did a Word Count in MS Word and found 9000+ characters (including spaces).
The Help said that there could be 65000 characters in a SQL statement.

I'm creating a game for some friends of mine to play. The total of all the
columns are 510 and represent individaul map images (think a hexagon map
that fits on half a sheet of paper). The map is also 361 of these
individual maps (19 x 19 individual maps).

My plan is to have Access concatenate each table to create an image for the
map (Tbl1.A1 + Tbl2.A1, etc) , kind of like putting transparency paper on
top of each other. After that, I want to export the information to a text
file, use some VB to get it to one continuous line for each map, and link it
to a webpage.

I'm pretty new to Access so I might be going at it in the wrong direction,
but as far as I can see, this is the only way to get the data exported into
a usable format for my needs.

Any help is appreciated

Todd
 
I think we're confusing each other, as well as Access. I have a text file
that I use for DataBinding with IE. It's set up like this

Map, A0, A1, ..., A510
0, data, data, ..., data
1, data, data, ..., data
...., data, data, ..., data
360, data, data, ..., data

When the page loads, it takes the records for the Map number it is set. I
can rotate through the Maps to get to the last. Based on the Data Binding,
I set my table up the same way. A better explanation of the 361 maps would
be like the game Battleship. A1 is one map, B9 is another. Each sector is
50 km. The reasoning behind the multiple tables (table1, table2) is to get
a 3D feel (sort of like of elevation). Each record on the table may have
the same value as another record, however it represents a different part of
the map.

Getting back to the original problem, I set up the DB with the tables that I
used for the webpage. Now I want to put them together so that it forms a
single string separated by a "-" (table1-table2-etc). I set up the SQL
statement to include all the columns in the format that I need (ex,
[tbl_Table1].[A0] & "-" & [tbl_Table2].[A0] AS H0).

When I run the SQL statement, the "Query is too complex" error popluates.
The online Help suggest to "Reduce the number of fields in the SELECT clause
or the number of subqueries or tables in the join." I have a total of 170
(340 if you count both tables) entries in the SELECT clause and my Join is
"JOIN tbl_Table2 ON [tbl_Table1].[Map]=[tbl_Table2].[Map]"

When it comes down to it, how do I get the maximum amount of columns into a
query?

Sorry for the confusion

Todd
 
Back
Top