Query with multiple queries included

[taber06]

Hello.

OK - I'll give you a bit of background. I've created a database for tracking calls for a small call center. In the main table, I have items such as type of call, CSR, contact information, receipt date / close date, etc.

I created queries to pull records based on certain time periods, e.g. this week, this month, this quarter, Q1, Q2, etc.

I then used those queries to do sub queries, e.g. I used the "this month" query to pull all closed cases for a specific CSR as a "count" so I could get an accurate number of records closed per time-period per CSR. In these sub-queries, I only showed the count of the records -- no other fields are shown.

Now -- I have about 90 queries and I wanted to use these in a report. I then pulled all of my "count" queries into one query and used that one for the basis of a report. This worked until I added too many subqueries into the report query.

I am having 2 problems.

Problem #1) I got a message that my "query was too complicated". I assume because I had too many queries pulled into one. Is there a way around this?

Due to the above error, I decided I'd break up the subqueries into separate files, one for a report on Receipts and one for a report on Production. This brought on a new problem, see below.

Problem #2) I found that one of my queries, Q42003 (which included all of the CSR's "count" of records closed in Q42003) only showed results if "all" of the CSR's had a count >0? I'm not sure why this is occuring.

I'm open to suggestions?

 

[BNPMike]

Now I'm going to bet my entire February salary that you haven't got 90 queries. You've got 1, 2 or 3 that you are running 90, 45 or 30 times for different slices.

The solution to your problem is to run the query(ies) for all the relevant conditions and let the report group them for presentation.

Give us some more detail.

 

[Golom]

I'm inferring a lot ... and some of it probably wrong ... but it sounds like you have created sub queries and sub queries of sub queries on your primary table and you are now attempting to recombine the data back into a query that recombines all the data

... but all the data is in the main table already combined.

Have you attempted to write a query based on the main source table that directly generates the report result that you want rather than pulling the data out into queries and then attempting to recombine with a UNION?

Access is limited to 32 tables in a query and, in the case of a UNION query, that equates to 32 SELECTS.

Without knowing your basic table structure or the exact nature of the reports that you want to generate, it is my guess that any secondary query or report can be generated directly from the base table without the need to create intermediate queries upon which other queries are based. In fact, that must be the case because the information contained in a secondary query is just a selection and/or condensation of the data from the main table.

Just as an exercise, here's a fictitious version of your table
[tt]
CallID
ReceiptDate
CloseDate
CSRID
[/tt]
Now, some SQL to show the count of all closed calls by Quarter by CSR for 2004 is
[tt]
Select DatePart ("q", CloseDate) As [Quarter],
CSRID As [CSR],
Count (CloseDate) As [Closed Calls]

From tbl

Where Year (CloseDate) = 2004

Group By DatePart ("q", CloseDate), CSRID

Order By DatePart ("q", CloseDate), CSRID
[/tt]
All without resorting to intermediate queries to extract quarters or CSR's.

 

[BNPMike]

sub queries of sub queries"

"It's a subquery, Jim, but not as we know it"

Golom, what are you on? Send me some in the post

 

[Golom]

OK, OK, so its probably not kosher terminology. Just trying to get the point across that s/he has queries based on queries which, in their turn are based on other queries etc.

What am I on? SQL (The pain, the pain ...)