Query is Too Complex

[depawl]

Hello:
I'm trying to generate a form which lists, among other information, the number of clients that have called in over a certain time period, by their zip code. In other words, each zip code in the county would have the number of call ins listed next to it. I developed a separate query for each zip code then combined them into one query and used that large query as the data source for the report. Things were going along quite nicely, but as you can imagine there are a lot of zip codes in my county, and I got to a certain point where when I tried to run the report, I got an error message stating that the "Query is too Complex". I think I had too many individual queries in the one large query that the report pulls it's data from. Is there a way to base the report on more than one query?
Thanks.

 

[KeithG]

Yeah, can you post an example db? Are your tables normalized?

 

[depawl]

Keith:
I've attached an example dp. I have a query which pulls a lot of other data from this table, and I want to pull information on zip code like this:

Number of Clients Served by Zip Code:

# zip code
3 12345
7 12346
etc.

Assume I have hundreds of clients and dozens of zip codes. I created a query to sum each zip code, then added those queries to my main query that is already pulling other data for a report. That's when I got the error that the query is too complex.
Thanks.

 

[ajetrumpet]

Number of Clients Served by Zip Code:

# zip code
3 12345
7 12346
etc.

DePawl, you are making this a lot more complicated than it needs to be. A simple query will give you want you want...

SELECT COUNT(ProgramTotalStatisticsT.[ID_#]) AS [Total Call-Ins], ProgramTotalStatisticsT.ZIP

     FROM ProgramTotalStatisticsT

          GROUP BY ProgramTotalStatisticsT.ZIP;

With this, you will get a count of how many ID #s there are in the table (number of call-ins??), grouped by zip code. This came from this resource.

 

[depawl]

Thanks Adam:
I'll see if I can figure that out and give it a try.

 

[depawl]

Adam:
You were right, I was really making a lot more out of it than it needed to be. It seems to be ok with one exception. What I need to have is the name of the town or village listed next to the zip code:

# zip code Location
3 12345 Smallville
7 12346 Jefftown Creek
etc.

The location name field is not included in the ProgramTotalStatisticsT table, I will have to generate a new table with all the zip codes and locations, but I need to figure out how to bring the matching location on the report.
Thanks again.

 

[ajetrumpet]

# zip code Location
3 12345 Smallville
7 12346 Jefftown Creek
etc.

The location name field is not included in the ProgramTotalStatisticsT table, I will have to generate a new table with all the zip codes and locations, but I need to figure out how to bring the matching location on the report.
Thanks again.

From that FAQ link I gave you, you can see that the more fields you include in the GROUP BY, the greater the filtering mechanism will be. But in your case, it doesn't seem to matter. If the location of the zip code will be in another table, apart from the zip code itself, you will probably have to relate the two tables and join them in the query. Then you are just looking at an autoreport for the finishing step.

Did everything work out OK?

 

[depawl]

Keith;
thanks to your help I got it figured out.
Thanks again.
Dennis