Access Multiple Queries Run with Date Parameter

[timmer2686]

Hello, I am new to access queries and this board. This has been very helpful recently for me to figure out some issues that I have had while trying to create and run queries. I have a few questions that I was wondering if someone here could help me with.

A little background first: I have 5 queries that I am running. The first query has the date range parameters set in the field area that I need to run and each additional create table query is based off the results of the previous query.

1. Which is better to use to run all of the queries in one simple step? A macro or a form? I am exporting the final table to excel so that I can make some additional adjustments off of it.

2. How would I setup the date range parameters for the first query if I were to use a macro without going into the query itself and updating the date field? I tried setting up a macro to run the queries by using the OpenQuery action for each of the 5 queries, but I cannot figure out how to do the date range.

Thanks for your help in advance.

Tim

 

[plog]

each additional create table query

Action queries (MAKE, APPEND, DELETE) are generally hacks and should be avoided. So,

1. You should build your queries properly and not have a "process". You simply run one query (which might be built upon sub-queries) and it produces the ultimate data you want.

2. Anything that requires a parameter and will be used frequently, I create a form for. User selects/inputs a parameter value, I use VBA to open/export the query using that parameter.

My advice is to tighten up the process using queries, instead of making tables. That way you simply click on the appropriate query and you get the data you want. You should convert your MAKE tables to SELECT queries and use them as sub-queries.

 

[timmer2686]

Action queries (MAKE, APPEND, DELETE) are generally hacks and should be avoided. So,

1. You should build your queries properly and not have a "process". You simply run one query (which might be built upon sub-queries) and it produces the ultimate data you want.

2. Anything that requires a parameter and will be used frequently, I create a form for. User selects/inputs a parameter value, I use VBA to open/export the query using that parameter.

My advice is to tighten up the process using queries, instead of making tables. That way you simply click on the appropriate query and you get the data you want. You should convert your MAKE tables to SELECT queries and use them as sub-queries.

Plog,

Thanks for the reply. I am new to this and was told by one of my co-workers that this is what I should do. I have always thought there should be an easier way to do this.

In terms of making the table queries sub-queries how would I go about this? I am assuming that my final query should be the only make table query that I have?!

Thanks in advance.
Tim

 

[plog]

You don't even need that last MAKE table, you can simply export a query.

When you make a new query you add datasources to them, correct? Either by dragging them from the left panel or by that dialog box that initially pops up. Either method allows you to add queries as well as tables. Once brought in, they behave the same way--you can bring down fields, you can apply criteria, you can link them to the other data sources in there.

So, making tables is an unnecessary step in producing a final query. My suggestion is to make a copy of your database (presumably everything works, it just uses those MAKE tables). Then, convert your MAKE table queries to SELECT queries, and in the further queries that use those made tables, you change them to use those SELECT queries instead.

 

[timmer2686]

You don't even need that last MAKE table, you can simply export a query.

When you make a new query you add datasources to them, correct? Either by dragging them from the left panel or by that dialog box that initially pops up. Either method allows you to add queries as well as tables. Once brought in, they behave the same way--you can bring down fields, you can apply criteria, you can link them to the other data sources in there.

So, making tables is an unnecessary step in producing a final query. My suggestion is to make a copy of your database (presumably everything works, it just uses those MAKE tables). Then, convert your MAKE table queries to SELECT queries, and in the further queries that use those made tables, you change them to use those SELECT queries instead.

Plog,

Yes, I use linked tables from an ODBC database to pull the information onto the queries.

I did not know that you could add select queries to other queries. This is definately interesting and should save me some time in the future. (Thanks)

My only other question is how would I set these queries up then to run in one single step with a date range selection for the first query and then export the final query to excel? I am not too comfortable with Forms and Macros, so any help would be appreciated.

Tim

 

[plog]

It depends. Answer these to see how automated we can make this:

1. Can your date range be calculated? For example, do you really need to input parameters everytime? I have a process that exports the current months data--I don't need to input parameters manually because I can use logic to determine what month it is, then apply that as criteria. Do your parameters follow a similar logic?

2. Is there a schedule this needs to run? Do you want this run every Thursday at noon for instance? Or does it need to be run on-demand?

3. If a user truly does need to oversee it (by inputing parameters or actually deciding when to run) will it be just you or multiple possible users?

 

[timmer2686]

It depends. Answer these to see how automated we can make this:

1. Can your date range be calculated? For example, do you really need to input parameters everytime? I have a process that exports the current months data--I don't need to input parameters manually because I can use logic to determine what month it is, then apply that as criteria. Do your parameters follow a similar logic?

2. Is there a schedule this needs to run? Do you want this run every Thursday at noon for instance? Or does it need to be run on-demand?

3. If a user truly does need to oversee it (by inputing parameters or actually deciding when to run) will it be just you or multiple possible users?

Plog,

1. Since the information is based off of the first table I do not know if the date range needs to be entered everytime. I would assume not since I am pulling the previous data and then adding on to it from other tables or using IIF statements from the current table. I usually just use a fiscal calendar date range i.e. >=3/29/2015 And <=4/25/2015 on the date field in the first table and the additional queries are for specific information based on the first queries results.

2. No schedule. I will run this on-demand on a monthly basis.

3. I will be the only person running this, unless I am no longer employed .

Thank you,
Tim

 

[plog]

So its a Fiscal Year type of thing? In that instance I create a custom function in a module--I pass it a date, it returns the FY it is in. There's code on this forum for that sort of calculation (search around for "Fiscal Year" and you'll find a post with code).

With that you can set up your process to involve minimal interaction: The whole process of creating your spreadsheet can be boiled down to these steps

1. Open Database
2. Right Click on the query and select Export.
3. Choose location/ name and export it.

Again, though, the key is making your queries not involve a series of MAKE tables. Convert them to SELECT queries and then use those down the line.

 

[timmer2686]

So its a Fiscal Year type of thing? In that instance I create a custom function in a module--I pass it a date, it returns the FY it is in. There's code on this forum for that sort of calculation (search around for "Fiscal Year" and you'll find a post with code).

With that you can set up your process to involve minimal interaction: The whole process of creating your spreadsheet can be boiled down to these steps

1. Open Database
2. Right Click on the query and select Export.
3. Choose location/ name and export it.

Again, though, the key is making your queries not involve a series of MAKE tables. Convert them to SELECT queries and then use those down the line.

So I have looked around and I have found some VBA code to help run the queries in sequential order (I have not changed them to Select queries yet). What I guess I would like to add to this code is a popup window requesting the parameter (date range) or an area where I can enter it. Please let me know if this is do-able and if there is anything missing from the code that would help.

DoCmd.OpenQuery "2108 GM Adjust Step 1"
DoEvents
DoCmd.OpenQuery "2108 GM Adjust Step 2"
DoEvents
DoCmd.OpenQuery "2108 GM Adjust Step 3"
DoEvents
DoCmd.OpenQuery "2108 GM Adjust Step 4"
DoEvents
DoCmd.OpenQuery "2108 GM Adjust Step 5"

 

[plog]

Sure its doable, but its not what I have been discussing.

 

[timmer2686]

Sure its doable, but its not what I have been discussing.

I guess I do not understand what you are discussing then (my apologies).

The queries are built and run correctly for what I need them to do. I can do a manual export of the table to Excel after the last query. What I need to figure out is a faster way to sequentially run the 5 queries in a single step, instead of individually, and add the date range for the query to base the information off of.

Sorry if I made anything confusing.
Tim

 

[plog]

Again, my advice is to create one query that runs and produces the data you need instead of a process you need to babysit. I can't explain it better than I have in my previous posts.