If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Can there be too many sub queries within a single query?
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 |
#2
|
|||
|
|||
Can there be too many sub queries within a single query?
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. -- Build a little, test a little. "Guitarzann" wrote: 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 |
#3
|
|||
|
|||
Can there be too many sub queries within a single query?
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" wrote: 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. -- Build a little, test a little. "Guitarzann" wrote: 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 |
#4
|
|||
|
|||
Can there be too many sub queries within a single query?
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? -- Build a little, test a little. "Guitarzann" wrote: 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" wrote: 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. -- Build a little, test a little. "Guitarzann" wrote: 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 |
#5
|
|||
|
|||
Can there be too many sub queries within a single query?
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 "KARL DEWEY" wrote: 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? -- Build a little, test a little. "Guitarzann" wrote: 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" wrote: 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. -- Build a little, test a little. "Guitarzann" wrote: 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 |
Thread Tools | |
Display Modes | |
|
|