Need help passing variables to a query

[Keith Heffner via AccessMonster.com]

In an effort to automate the creation of several reports, each based on a group number, I'm curious to know if there is a way to pass a variable to a query to select only the records in that group. I need to do this without resorting to prompting the user to enter a variable each time the query is run.

Currently, I've got each report and query set up separately (query1, report1, query2, report2, etc) and in order to modify any information on one report or query I have to update them all. Not to mention that my report and query windows are packed because of the duplicates. I'd like to make one query that I can pass an argument to and thereby eliminate the duplicate queries and reports.

Any assistance or ideas on how I can do this would be greatly appreciated.

 

[Jeff Boyce]

Keith

I'm having a bit of trouble visualizing your situation. Are you saying that
you are using multiple queries to select different "sets" of data from the
same underlying table/table set?

You mention "group number", but not how it is related to the underlying
data.

If you don't prompt the user in any way, how does Access know what/which
group(s) to select?

How is your data structured?

What do a few of your queries look like? (post the SQL statements)

--
More info, please ...

Jeff Boyce
<Access MVP>

In an effort to automate the creation of several reports, each based on a

group number, I'm curious to know if there is a way to pass a variable to a
query to select only the records in that group. I need to do this without
resorting to prompting the user to enter a variable each time the query is
run.

Currently, I've got each report and query set up separately (query1,

report1, query2, report2, etc) and in order to modify any information on one
report or query I have to update them all. Not to mention that my report and
query windows are packed because of the duplicates. I'd like to make one
query that I can pass an argument to and thereby eliminate the duplicate
queries and reports.

 

[Keith Heffner via AccessMonster.com]

My data table contains employee information for the company and each employee record has a region number attached to it. Each region needs to have a separate report exported to a .rtf file every week and list this information. The people who use the database to generate these reports prefer having them run without user input (via a macro or VBA code). I know that I could write code to prompt the user to enter a region number directly inside of the query and thereby eliminate all of the extraneous copies, but as I mentioned, they would prefer to have this process transparent and automated.

The region numbers are set and should not change, so that is not really an issue. I was hoping there was a way to write >one< query and >one< report that I could pass the region number as an argument to via a macro or VB code. Something similar to passing a variable to a function call is what I'm looking for.

Currently the report and query tabs have something similar to the following:

query - region10
query - region11
query - region12
query - region13
report - region10
report - region11
report - region12
report - region13

Considering there are currently 20 regions that require these reports, this leads to a lot of wasted overhead (not to mention anything that needs to be added to one report has to be duplicated in all of the others).

Does it make a little more sense now?