How to simplify routinely-monthly-queries?

D

Dawn

Dear all,
I will do a routine work ervery month.For example,there are 2
tables,200802&200803,I will do some queries using the two tables,thus
generates some new tables.
When the table 200804 is ready, I have to do the same queries using tables
200804&200803.
Is there any way to simplify the routinely-monthly-queries,as not to write
the queries again?
Thanks a lot!
 
K

Ken Sheridan

Dawn:

You can almost certainly avoid the need to write a new query every time by
one of two means. In either case, however, you should abandon having
separate tables for each monthly set of data. This is generally not only
unnecessary, but is bad design practice as it is encoding data as table
names. Data should only be stored as values at column positions in rows in
tables.

1. If the criteria for the queries can be related to the current date then
this can be built into the query so that the correct rows are returned each
time. From what you say it sounds like you are returning data for the last
two months, so this would be possible. You could set it up either to return
rows for the current month and the previous one, or for the two months before
the current month for instance. For the former the WHERE clause for a query
would be along these lines:

WHERE [YourDateField] > DATESERIAL(YEAR(DATE()),MONTH(DATE())-1,1)
AND [YourDateField] < DATESERIAL(YEAR(DATE()),MONTH(DATE())+1,1)

For latter it would be like this:

WHERE [YourDateField] > DATESERIAL(YEAR(DATE()),MONTH(DATE())-2,1)
AND [YourDateField] < DATESERIAL(YEAR(DATE()),MONTH(DATE()),1)

2. The above requires the queries to be run during the correct month of
couse. To enable the queries to be run at any time you could include
parameters to input the year and month. So for the first option:

WHERE [YourDateField] > DATESERIAL([Enter year:],[Enter month as number:]-1,1)
AND [YourDateField] < DATESERIAL([Enter year:],[Enter month as number:]+1,1)

and for the second:

WHERE [YourDateField] > DATESERIAL([Enter year:],[Enter month as number:]-2,1)
AND [YourDateField] < DATESERIAL([Enter year:],[Enter month as number:],1)

You could make this much more user friendly by creating a dialogue form with
combo boxes for the year and month, with default values of the current year
and month, and reference these as parameters in the query. The user then
simply has to click a button on the form to open the query using the default
current year/month, or better still a form or report based on it, or they can
select a different year and/or month before clicking the button.

With the limited information you've given its not possible to say any more
about how to combine your multiple tables into a single one, but it should be
straightforward enough. Similarly advice on the exact design of the query
would require more information from you on what your tables represent and
what you are extracting from them with your queries.

Ken Sheridan
Stafford, England
 
G

George Nicholson

Your data structure could probably be improved, but working with what you
have:

Consider creating 2 queries (qryCurrentM, qryPriorM) that are simply copies
of February and March. Then change your more complex monthly queries so that
they are based on qryCurrentM and qryPriorM.

Then, when a new month rolls around you can change where qryCurrentM and
qryPriorM are pointing in about 30 seconds and run the related queries.

Or, is it possible to give the tables generic names that can be changed?
(tblLastMonth, tblCurrentMonth, tblNextMonth). Name AutoCorrect should
definetly be off if you go this route, if it isn't already.
 
D

Dawn

Dear ken and Geroge,
Thank u for your kindness,as I am a junior user of access,I will choose the
way Geroge suggested,but which dosen't mean Ken's suggestion is not good.As I
am improving my skills on access,I will try to use the marcos,will try to use
the means
Ken refered above.
Thanks to u both.
 
D

Dawn

Dear George,
If I choose your way,there’s still a problem I want to trun to you for help.
As I named two tables with table current and table previous.I do many
queries with them,I also saved those queries.And those queries are not all
generated from the 2 tables,some are generated from the queries.
When the next period comes,I replaced the table previous with the data of
table current,and downloading the table current.When I tried to re-use these
queries, because some queries depend on other queries.What’s the
opening-sequence for these queries to make sure the queries use the correct
data source?Will open all these queries avoid the open queries turn by turn
problem?
Best regards.


George Nicholson said:
Your data structure could probably be improved, but working with what you
have:

Consider creating 2 queries (qryCurrentM, qryPriorM) that are simply copies
of February and March. Then change your more complex monthly queries so that
they are based on qryCurrentM and qryPriorM.

Then, when a new month rolls around you can change where qryCurrentM and
qryPriorM are pointing in about 30 seconds and run the related queries.

Or, is it possible to give the tables generic names that can be changed?
(tblLastMonth, tblCurrentMonth, tblNextMonth). Name AutoCorrect should
definetly be off if you go this route, if it isn't already.

--
HTH,
George




Dear George,
Click to expand...
If I choose your way,there’s still a problem I want to trun to you for help.
As I named two tables with table current and table previous.I do many
queries with them,I also saved those queries.And those queries are not all
generated from the 2 tables,some are generated from the queries.
When the next period comes,I replaced the table previous with the data of
table current,and downloading the table current.When I tried to re-use these
queries, because some queries depend on other queries.What’s the
opening-sequence for these queries to make sure the queries use the correct
data source?Will open all these queries avoid the open queries turn by turn
problem?
Best regards.
 
G

George Nicholson

I'm not 100% sure I understand your current question, but:

** Be sure Name AutoCorrect is OFF **
In this case you definitely do NOT want Access to "help" you by keeping
track of table/query name changes and applying those changes to existing
references within queries. (The feature is buggy anyway, so you are safer
having it off in any case).

As long as tables named tblCurrentM and tblPreviousM exist (with identical
field names), any queries based on them should work fine.
 
Top