a hand people!?...

[kdm3]

I have hit a crisis of confidence - am I doing the right thing?

I am building an incredibly complex app., and have to do alot of weird calculations.
At present I have made extensive use of queries, but these are layered up to 3 or 4 deep, is it considered bad design to do this - should I try to combine these abit more - or should I continue down the route I have recently become enamoured with, using VBA and trying to accomplish the same tasks through a series of IF's, Do's and Custom Functions.
I've always thought that queries were great, but using 3/4 deep seem a bad idea - would it be better to incorporate the SQL strings in a sub, to consolidate them all?

I would be interested to hear what anyone thinks, and what their prefernecs are, both from a point of view of minimising processing time, programming time, and robustness of the app.

 

[Mile-O]

Would you consider your database as being normalised?

 

[The_Doc_Man]

It can sometimes be a good idea to layer queries. I'll give you an example scenario.

If it happens that you have a LOT of criteria to apply and you have a strong clue as to which criterion is most restrictive, make that the innermost query. You make the system do less work sometimes. That is because of an old theory on hypergeometric volumes.

Not only that, if it happens that you have two queries that are based on some underlying commonality between records in your tables (two queries or more), you can base the two visible queries on a query that does the same elimination each time. Save yourself from work and assure that two different queries start from the same place while you are at it.