chain of queries - efficiency????

[stickers]

Can anyone advise on the efficiency (or otherwise) of having lots of queries dependent on one another? I know it generally isn't that good an idea, but what are the limits? Currently I have a maximum of 3 before I get to a make table or append query, but then I have other queries relying on the tables appended or created by previous ones?

My app works OK so far, but I have only got test data in. I'm using access 2k. Will the number of queries in a row make much of a difference when I get to the situation (that I have to) of having the back end on a server and the front end on users machines?

Constructive advice needed!!

 

[Spellman]

I think as long as your only using standard select queries you should be okay and things shouldn't be effected to any great extent. Problems may arise if you start using groupings and doing calculations in the queries which tends to throw access a bit and cause errors especially in linked tables but from the scenario you have things should be okay without too much problem regarding effeciency. Mark

The key to immortality is to make a big impression in this life!!

 

[NaoTriste]

In theory, like you say it is not a good idea having lots of nested sub-queries but in practice many of those no nos work perfectly well. Only volume testing will tell you but since you mentioned the Client Server set up I can tell you that from experience and using a SQL server backend with an Access front-end I have had to change some of those nested sub-queries into a simple base query which I then use as a source by using a temporary query definition (in code) to build a temp table which then becomes the source of the Report.
This is because I found that once going live the connection to the Server actually timed out in the middle of executing the complex query.
All this is dependant on your Client Server set up but having done lots of systems I know go for the more cautios and laborious setup than the flashy one.

 

[stickers]

Thanks for the advice guys.

Stickers