Expression Too Complex

Here's one for you !!

I have a large data set of figures 200 000 deep and with 59 columns. The
columns represent each product and the rows represent individual records.

I am attempting to write a query in design view that returns the smallest
figures per row. (I.e. Top 3 products per record by price) I have done this
successfully for the first 29 columns and then i receive an error message
"Expression Too Complex" when i go further.

Tell me how i can get round this showstopper without changing the structure
of the table because to be honest the table is too large to manipulate and
the data is presented in this format anyway.

Thanks

You can't. Sorry to say that if you have a column per product, the problem is
that your table is not properly normalized. That leads to overly complex SQL
statements as you've found out. What happens when you need to add another
product? You have to change all queries, forms, and reports don't you?

If you normalize your data properly, your queries would be much simpler and
the ability to add products will be greatly simplified.

I highly suggest getting some relational database training or reading
"Database Design for Mere Mortals" by Hernandez before proceeding any further
on this database.

As far as presenting the information, once properly normalized you can run a
crosstab query to display the data in reports if needed that way.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

"ACWOKING" wrote:

Here's one for you !!

I have a large data set of figures 200 000 deep and with 59 columns. The
columns represent each product and the rows represent individual records.

I am attempting to write a query in design view that returns the smallest
figures per row. (I.e. Top 3 products per record by price) I have done this
successfully for the first 29 columns and then i receive an error message
"Expression Too Complex" when i go further.

Tell me how i can get round this showstopper without changing the structure
of the table because to be honest the table is too large to manipulate and
the data is presented in this format anyway.

Thanks

The simplest way to solve this might be to build a new table that normalizes
your data. How to do that would depend on the structure of the data as it
now exists.

You could write a Vba function to get the three lowest values for each row.
Without knowing more about your row structure it is hard to say how to solve
the problem even with VBA. Also, what do you want returned - the three
values or the names of the columns or something else?

Does each row have a primary key?

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

"ACWOKING" wrote in message
...
Here's one for you !!

I have a large data set of figures 200 000 deep and with 59 columns. The
columns represent each product and the rows represent individual records.

I am attempting to write a query in design view that returns the smallest
figures per row. (I.e. Top 3 products per record by price) I have done
this
successfully for the first 29 columns and then i receive an error message
"Expression Too Complex" when i go further.

Tell me how i can get round this showstopper without changing the
structure
of the table because to be honest the table is too large to manipulate and
the data is presented in this format anyway.

Thanks