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;