Max number of stacked queries?

Hi,

I have about six queries stacked in line. i.e. queries within queries...

Is there a maximum number of stacked queries allowed?

I just tried to add another query to the stack, and a simple answer seems un-obtainable. It is a three column query where on of the columns is a tickbox. When I set the criteria on the tickbox column to true, no records are returned. If I clear the criteria, there are records with both true and false returned values from the tickbox column.
This makes me beleive MS Access gives up when the queries are stacked so high.

Any help apprec...

LoveToSpod
LoveToSpodAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

jadedataMS Access Systems CreatorCommented:
Hey LoveToSpod!

 as Long as the number of total fields (Tables * Field) does not exceed 255 you will be within the limits.

regards
Jack
0
jadedataMS Access Systems CreatorCommented:
There is also the point when Access can no longer track the lowest level query in the stack space, which is a less calculable value.

You need to look at combining some of the lower level queries or switch to some temporary tables in order to make the data more static at the time the upper level queries try to evaluate the data.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PoppekopCommented:
if it is a yes/no field, try putting condition to -1 instead of true (just a wild guess)
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

jpolin1Commented:
I would stay away from the stacked queries anyway. It is hard for someone else to figure
out what you were trying to return from the DB.
0
jadedataMS Access Systems CreatorCommented:
I agree with jpolin1 here.  Stacked queries, UNLESS written and used by VBA code can be really ugly to iron out later if there are additional issues or change requests that have an impact on these queries.  Even for you as the developer, 3 months later.... (when the memory starts to go...)
0
LoveToSpodAuthor Commented:
Making Temporary tables is a good way of speeding things up, but I find inflates the size of the application requiring it to be compacted regularly. Jack, when you say temp taboes, are you talking recordsets? If so, I will need to brush up a little. Do recordsets inflate the size of the application too?

Thanks for your advices.

LoveToSpod
0
jadedataMS Access Systems CreatorCommented:
If you push those temp tables into a temp mdb you can throw them away with the bath water when the operation is completed

   dbengine.createdatabase("c:\folder\tempjunk.mdb")
   set dbTemp = opendatabase("c:\folder\tempjunk.mdb")
   ssql = "SELECT * INTO ["c:\folder\tempjunk.mdb"].[TempTable] FROM TableName WHERE ([This] = 'That');"
   currentdb.execute ssql, dbfailonerror

     'Your temp data is now in a "throw-away" temp container.
     'Perform ops on this data here
     'When done,....

   kill "c:\folder\tempjunk.mdb"

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.