Can there be too many sub queries within a single query?

[Guitarzann]

I am trying to combine the results from more than 15 queries into a single
query in an attempt to save opening 15 different queries individually.

The query runs successfully with the exception of the 15th, or last, query
returning null cells. If I pull that sub query by itself it does return the
correct data however. Has anyone experienced this in their projects?

Thanks for your help,
Rick

 

[KARL DEWEY]

I do not know as there is a limit to the number of subqueries but I read
there is a limit to the total number of characters in the complete SQL
statement. That would include subqueries, joined queries, and union queries.
I have not heard of getting null data fields but that an error message would
be returned as being too complex.
Try removing another subquery but include number 15 as see if it runs. You
just might have a problem in it.

Post what you are trying to and someone might have a suggestion as to how to
accomplish it in a simpler fashion.

 

[Guitarzann]

Thank you, Karl. Your answer regarding "being too complex" addressed the
previous problem I had before I tried this current work around. It sounds
like I may be pushing the envelope in terms of the coding of the SQL
statement. That is helpful. Being I am using the Design view and not the SQL
view it appears to be limitless in its design. However, it sounds like I am
learning there are limitations in using SQL. I am not an SQL programmer
unfortunately.

This is what I am trying to do. My database contains a report with a series
of sub-reports. However, my customers would like the data in an editable
format where they can take portions of my report and post them into their
existing Excel workbooks.

To do so and not create additional steps for myself at the same time, I
assembled a query that pulls all the queries that populate that report
together into a master query, so to speak, where they can now open a query
datasheet of all the sub query results which they can then copy/paste into an
Excel book versus the Access report which is not designed to copy/paste data

I will await any suggestions to fulfill this process being this is something
the management teams like about what Access can do (and that was a major feat
for this office where they are absolutley terrified of Access.) In the
meantime, I am going to tweak perhaps create a pyramidal style where instead
of using all 16 queries in one I will split them into two and then have those
two go into one in some fashion.

Thanks again, Karl, for your response. If what I have written here sparks
something of an idea, I would appreciate your feedback.

Rick

 

[KARL DEWEY]

my customers would like the data in an editable format where they can take
portions of my report and post them into their existing Excel workbooks.
Why not create a form with 16 buttons, each to call one of the queries in
datasheet view so they can select, copy data, and paste in Excel?

 

[Guitarzann]

Already did that. Unfortunately, I spoiled them in that they came to like the
idea of a single button pulling all their data. They just want to go in, pull
their data, and run. 16 buttons mean they have to open 16 reports, copy/paste
16 times, etc.

Because of the immensity of the data coming from multiple independent
sources, I developed the Access database to be a central reporting location.
But, they still love Excel. So, it has been a battle over what application to
use. One that will address their needs. Or, one that will address their
comfort zone.

Thanks again! I truly appreciate the responses.

Rick