vb and access/multiple queries one report

[tcgaines]

do any of you have any examples of using multiple queries for a single report? basically i want to execute a query on a query. then excecute another query on that result set. any examples? oh yes, im using access and vb6.

thx

 

[tcgaines]

thank you for the suggestion.

However, I am uncertain that this is the best way to achieve my goal. I have 300+ queries which each select specific non-dynamic data from a single table depending on a zipcode. In addition to the criteria in these queries, i also need to execute a query on the result set from the first query. creating an outerjoin on each one of these 300 queries sounds inefficient and non-scalable. I would like to create ONE query that can be executed "on top" of these other queries, one at a time, without having to rewrite the 300+ queries.

EX:

I want to populate a report with all customers from x zipcode, there are hundreds of these queries so I select the query with the appropriate zipcode:
Select tblCustomer.*
From tblCustomer
WHERE tblCustomer.ZipCode="xxxxx"

now i want to execute an additional query on this result set that will select records based on the criteria of the second query, in addition to the first.

So to sum it up, i want to create ONE query that I can execute against eeach on of the hundreds of queries I already use, rather than re-writing the 300 queries, and then rewriting them again when a different query is required, and again, and again. I just want to re-write ONE, that is available to ALL.

am i on the right thought process?

 

[RuralGuy]

Why wouldn't you refer to your form control instead of having so many queries? ie:

WHERE tblCustomer.ZipCode=[Forms].[MyFormName]![txtZipCodeControl]

 

[Budd]

Hi tcgaines,

Just wondering, are the fields requested in your queries always the same? In other words are you pulling from the same fields each time but only requesting a different Zipcode?

BUD

 

[tcgaines]

Hi tcgaines,

Just wondering, are the fields requested in your queries always the same? In other words are you pulling from the same fields each time but only requesting a different Zipcode?

BUD

Yes, I am pulling the same fields each time, but requesting different zipcodes. however, i am selecting multiple zipcodes. for example, chicago is subdivided into 8 parts. for north chicago, i:

Select tblCustomer.*
From tblCustomer
Where ZipCode = 89089 OR 23432 OR 95435 OR 45425 OR 45243 OR 54322 OR 54325 OR 54325 OR 54354 OR 54325 OR 45254;

All of these zipcodes are considered to be in NORTH CHICAGO. I have hundreds of queries identical to this (with the exception of zipcodes, ranging from one zipcode to 30 zipcodes), each selecting customers from a particular area.

For examples sake, and simplicity, suppose i now wanted to select every customer who's name is john, from N.Chicago (the above query), as well as from every other query, if i chose to, one at a time, as needed.

As far as scalibilty is concerned, it seems to me (mind you, im an amateur) that it will be much more efficient to write one query that would be availible to execute "on top" of the others, rather than modifying each of the others.

The reason I am hesitant to modify each and every query is because the criteria for the "One Query," that i have in mind, will change regularly.

I feel strange laying this all out for you, as if its being done for me. that is not at all my intention. thank you all for any assistance.

 

[tcgaines]

As RuralGuy implied but didn't say, get rid of the 300+ queries and replace them with one that gets the requested zipcode from a form field.

Thank you for the suggestion, however, i never want to look at zipcodes again. as my above reply suggests, each area is subdivided into zipcodes. all i want to do is select the location, no zipcodes, theyre driving me crazy.

EX: So Each location may consist of MANY zipcodes. Consider each of these as a different query, hense the hundreds i have.

N. Chicago (WHERE ZIPCODE=53654 OR 34523 OR 42543 OR 34578 OR etc.)
S.Chicago
E. Chicago
W. Chicago
Seattle
Austin
N. Sacrimento
S. Sacrimento
Houston #1
Houston #2
Houston #3
Houston #4
Houston #5
Houston #6
Houston #7
Houston #8

Each of these has identical queries with the exception of the zipcodes, and there may be many zipcodes as criteria.

In addition to these queries, suppose i have one that will:
SELECT tblCustomer.Name
FROM tblCustomer
WHERE tblCustomer.Name=JOHN;

Rather than modifiying hundreds of queries, i just want to execute it "in addition to."

mind you, this is for simplicity sake. In actuality, the above query will include many more criteria in the WHERE.

Please excuse the repitition, sometimes i have a hard time with words.

Thanks again.

 

[RuralGuy]

I would have thought you would have put those zip code group references in a table rather than hard coding them into a query. If Uncle Sam splits out an additional zip code then...?