Use a Query to Make a Table

[Dontremb]

Ok, here's the problem I'm running into. (Some of you may remember part of the problem from other posts.)

I've got a lot of queries that count other queries. All of these queries need to be displayed on one report. I don't want to use subreports because they're very annoying.

I originally made a query that was just a compilation of all my other counting queries, then used that for the report, which worked perfectly, until my query became too complex.

It's too complex because I'm going to have about 100 counting queries in the main query. And each of those counting queries is based off of a filtering query.

So, basically, to run my report, I am using about 200 queries.

I heard there is some way that you can use a query to put its information into a table.

My basic question is this: When I open my report, which has all of these counts, is there a way to somehow run all my queries, then put their information into a table, then generate the report based off that table?

Ultimately, I'm just trying to get about 100 counts on one query or table so I can make a report out of it. But every time I think I find a solution, it ends up not working for some reason.

Thanks for any help.

 

[lameid]

Some sort of data warehousing where you append all the values to a table would also be possible. Use code or a macro to run all the queries as a batch.

This is not ideal but would be functional.

100 count queries? Really? It sounds like there is something fundamentally wrong with either the database design or your approach to the problem because when I have to join or union aggregate queries I have maybe hit 5 sub queries (usually to accomodate bad design in a legacy system).

Can you post a couple examples of your count queries SQL? I am hoping to see that they are basically the same query with different criteria or something else easy to simplify.

 

[MajP]

I beleive the answer I gave earlier does exactly what lameid is suggesting. Any reason you did not pursue that approach?

 

[lameid]

Having dug around and looking at thread701-1529203; I would suggest you look at what lespaul was suggesting and add appropriate grouping to capture gender as well as race in your aggregate query. Then you should have just one query to give you all your desired results. If you think you need another query, post the SQL you have and state what else you want it to do with any more relevant field names.

 

[dhookom]

Dontremb,
This makes about 1/2 dozen threads and many requests to post your SQL and tell more about the values you wish to display in the report. I seem to agree with

It sounds like there is something fundamentally wrong with either the database design or your approach to the problem

If we had a better understanding of your tables, records, and report specification(s), someone might be able to provide a more functional and efficient solution.

Duane
Hook'D on Access
MS Access MVP

 

[MajP]

I do not get it either. You have been given multiple suggestions, but will not explore any of them. Instead you keep posting the same question. I am certain that there is something fundamentally wrong with your entire design. There is absolutely no way you need 200 queries to produce some totals. Lets figure out a way to work smarter not harder.