Provide WHERE Clause for Parent Query from Within Child Query

[Rawb]

I short, is it possible to have a Saved Query filter the results of it's parent Saved Query (assuming, of course, that it's based off of another Query)?



In long: I have a UNION Query that returns (about) 4500 Records. It's not fast, as far as Queries go, but it's still under 1 sec. execution time.

I have another Query that uses the above UNION Query, Joins it to several other Tables, and then provides a WHERE clause to filter the Results down to what I want.

This "child" Query takes significantly longer to run and, once you tie it to a Report, it takes about 2 seconds to generate each page (each Record is one page on the Report). If you want to print a Report that's 9 pages, it takes 18 seconds to send it to the printer. 30 pages? You're talking a wait of a minute.

I'm hoping that paring down the parent UNION Query's results before feeding it to the child Query could speed this up. Does anyone know of a wayto do this without accessing the parent UNION Query directly?

[June7]

Why is UNION query necessary? This can be an indication data structure is not optimized. Are you unioning tables with identical field structure?

[Rawb]

Why is UNION query necessary?
I'm doing this because we have two Tables used for Scheduling here:

• The first Table it a list of everything in the Database that COULD POSSIBLY be scheduled. It is based on incoming orders, what we've previously scheduled, and if anything needs to be remade because it failed quality control tests. This is essentially a temporary Table that is rebuilt daily.
• The second table is a list of everything that HAS ALREADY BEEN SCHEDULED and that hasn't been billed complete. Once an item from Table 1 is scheduled, it is added to this Table (although it's not removed from Table 1 until the next day's schedule is generated, ugh).

Basically, I'm combining these Tables to get a list of everything - both scheduled and unscheduled items - so that I don't have to generate separate Queries and Reports for each grouping. And then have the users always asking me "Why does my Report show up in this area but not this one?".

This can be an indication data structure is not optimized.
You're absolutely correct. I'm working on updates to a Database that was put together by someone that had never even heard of the word Normalization Unfortunately, he's still working here (and in charge of IT) and refuses to let me redesign the whole shebang so I'm stuck with his setup.

Are you unioning tables with identical field structure?
Hehe, are you sure "unioning" is a real word? :P

Anyway, yes. The Tables don't correlate exactly, but I've been able to massage both Tables enough so that the resulting data matches up.

[June7]

"is it possible to have a Saved Query filter the results of it's parent Saved Query"
Not really sure what you are asking. You can apply a filter in the UNION query or in another query that uses the UNION as data source.

I don't know that filtering at any stage will speed up the report rendering. I don't use UNION query much. I have one that merges legacy dBase data with Access table and is the basis for an export to Excel. However, all the graphs I built have UNION query as data source.

Why does IT care how you design your db? My IT people are mostly network managers and know little to nothing about databases and could care less what I do. There are a few managers in our IT heirarchy who do manage org database (accounting, payroll, etc in Oracle or some other multi-user db) and if we choose to use Access for our local needs (lab data reporting) then don't expect any support from them and again they don't care how we build the db.

[Rawb]

Why does IT care how you design your db?
Because I'm not designing a db for use only at this location. I've been given limited permission to modify the db provided by IT to better suit the needs of this location.

Even though I know more about database design than just about everyone else in the company combined, I'm little more than just a local tech in ITs eyes. I've been working to change that over the past year or so, but they still balk at just about everything I say because "The system has worked fine for the past 10 years" (utter rubbish - IT just drags its feet or flat out refuses to make changes and the users eventually each found their own way of doing things without using the db) or "It's too much work to change everything now" (this one has a grain of truth, each location has years worth of data that would need to be converted in addition to the over 400 different Forms in the db).

Anyway, this is all beside the point. The point is that I have very little control over the Table structure of the db and can only design my own Queries, Forms, and Reports.

I don't know that filtering at any stage will speed up the report rendering.
I've tried Joining my child Query directly to just one of the Tables in my parent UNION Query and it's blazingly fast (compared to now). The Report pages get sent to the printer so fast you can't even count them. It's only after I plug the UNION Query back in that things slow down.

I think it's because the entire UNION Query has to be run for each Record returned to the child Query. This essentially means that I'm forcing the system to run 4500 UNION Queries every time I run the child Query (which itself returns all 4500 Records when unfiltered).

I've tried hard coding the filters into the parent UNION Query and it does increase the speed that the Report is generated. It's still not as fast as I would like, but it drops to a little under 1 second per Report page. So only half a minute for a 30 page Report :/