Query Too Complex?

[NBAbueg]

I have a report that uses a query with a lot of variables, sub-queries, and functions. If I add one more entry to to the query, it gives me an error message "query too complex". Is there such limit as to what you can do on a query? Any way to increase this limit? All suggestions will be appreciated.

 

[Golom]

Yes there are limits ... lots of them ... and no ... mostly you can't change those limits. Your message may coming from your blowing over a limit but it may also be coming from missing or incorrect SQL syntax that is causing Access to become confused.

Post your SQL and we may be able to give you better information.

 

[willir]

Ho Golom -- long time no chat. Last year I mentioned in a posting that you were a rising star. while I took a seista, well actually worked my butt off on a new job, you certinly proved me right. There are a lot of people who really appreciate your work.

NBAbueg

Complex queries can mean several things.

Firstly, there are limits to everything, and how long or how complex a query can be is one thing to expect.

Secondly, in my experience, a very common reason for developers running into this "wall" may be because of poor database design. Perhaps the design is not adequately normalized...

If your design is normalized, there are ways around the "wall", but again, within reason.

You can
- Have a query reference a subquery
- Create a function that performs a calculation or returns the results that you have incorporated in the long SQL statement. for example, calculate the SALES total for each invoice without referencing the InvoiceDetail table. You pass the function the InvoiceID, and the function returns the totalSALES. I find this soltuion pretty slick but I understand this apporach is a point of failure if you upsize to MS*SQL or Oracle

But I say again, the most common cause when you hit a "resource wall" within Access is due to poor design. (Well, okay, if you have a huge application that should be running a more robust database is another reason).

Richard

 

[NBAbueg]

I think the problem is due to poor database design, so i'm in the process of rebuilding tables and queries. Do you know how to simplify the loop below.... what is the equivalent of the "for" loop in Access or SQL:

LblTS: IIf([CurrentMonthPPSF]=1,[JanTotal],IIf([CurrentMonthPPSF]=2,[FebTotal],
IIf([CurrentMonthPPSF]=3,[MarTotal],IIf([CurrentMonthPPSF]=4,[AprTotal],
IIf([CurrentMonthPPSF]=5,[MayTotal],IIf([CurrentMonthPPSF]=6,[JunTotal],
IIf([CurrentMonthPPSF]=7,[JulTotal],IIf([CurrentMonthPPSF]=8,[AugTotal],
IIf([CurrentMonthPPSF]=9,[SepTotal],IIf([CurrentMonthPPSF]=10,[OctTotal],
IIf([CurrentMonthPPSF]=11,[Nov02Total],[Dec02Total])))))))))))

Thanks!

 

[willir]

I would say this is one large "IF" statement. You to test each feild representing a month to find one value.

I am not sure about the requirements of your database, but I have three concerns about your database...
- Fields names JanTotal, FebTotal ... suggests that you are "hard coding" your months
- Field names Nov02Total, Dec02Total suggests that you may be hard coding your years.
- Field names Nov02Total, Dec02Total suggest that you are storing totals.


Please review the following fundementals on relational databases...
Fundamentals of Relational Database Design by Paul Litwin
Download document
Read on-line (HTML)

Micro$oft's answer...
283878 - Description of the database normalization basics
304467 - ACC2000 Defining Relationships Between Tables in a Microsoft Access Database


Now some times, it may be necessary to store totals, but normalization suggests that it is better to calculate then when requried. I wrote a post on this topic last year... Add data to a table from an equation, how?

Take advantage of date fields.
If you feel you have to storing the transaction month, treat the month as a field instead of storing numbers for each month.
Unless there are good reasons, calculate totals when your require them.

To use a non-normalized database can result in...
- difficulty in extracting information
- very long SELECT statements and fields that may tax the limits of Access
- a heck of a lot of maintenance. For example, having to add new fields for each year whihc would then require changes to the forms, reports and queries.

If you choose to continue with your current design, I can understand -- you have a lot of time invested in the current databse, but you may be heading to a lot of frustrating times where you have to figure out work-around solutions to "force" the information out of your database.

Good luck.
Richard