Access, SQL - Update Joined Crosstab queries by Code for monthly trend

billb1057
billb1057 used Ask the Experts™
on
I have two cross tab queries joined on a field with monthly data added to columns each month.
Is there a way for a formula to automatically update the query each month?  This will show a trend with a calculation.

There are two scores "s" and "n".  Each month, new records come in the table (so June will be 6/1/2009).  The crosstab puts them as column headings.  Then the calculation adds the values in the columns (for example, year-to-date adds all the months).
To change this manually I would pull in a new column (6/1/2009), then title the column headers nJune and sJune.  Then change the ytd calculation to add the June values.
The purpose of this is to show all of the months in a trend with the calculations by Operation (operations are row value).
The source files could be crosstabs (presently) or exported to tables and then joined for the calculations - that part doesn't matter.


'two queries: qry_N and qry_S are joined and new columns added each month.
 
SELECT qry_N.OperName, qry_N.[4/1/2009] AS sApril, qry_S.[4/1/2009] AS nApril, qry_N.[5/1/2009] AS sMay, qry_S.[5/1/2009] AS nMay, [sApril]+[sMay] AS ytdS, [nApril]+[nMay] AS ytdN
FROM qry_N INNER JOIN qry_S ON qry_N.OperName = qry_S.OperName;

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Hamed NasrRetired IT Professional

Commented:
A model database may help!
List input values
And what output to expect.

Author

Commented:
Ok - good idea.  I will post that a bit later.
Here is a model database.  The FINAL query joins the two crosstabs and calculates a score.  But then when a new month comes in, it all has to be changed manually.
Update-crosstabs.mdb

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial