Access - How do I create a monthly trend with crosstab queries?

billb1057 used Ask the Experts™
Here's the original question and sample database:

I'll try to word this differently and hope to get a better result.
When creating a crosstab for monthly data, a new column is added to the cross tab (months as columns) each month.  Is there a way to build formulas off of a crosstab query, anticipating that new columns will be added each month?
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
The following is not tested, and is just to give you an idea.  You are welcome to try if you have a date column like a transaction date.

The easiest way is to fix your column headers by puting  an "in" clause as part of your pivot statement.
Pivot MonthName(DatePart("m",[YourDatefield])) In(January, February..................................December)
....but you will probably need a group by clause before your pivot statement
try using the following function as your column header in the cross-tab:
      DateAdd("m", 1, [YourDatefield])


Your first solution worked great for fixing the headers - thanks.  Now the months read across.  I uploaded a sample (could you give that a quick look, please?).  There's another query (qry_Scores_FINAL)  that reads the combination of the two crosstabs and it makes a simple calculation (for year-to-date).  Would the best approach be to code a sum of all the columns (Jan+Feb+Mar ... Dec) and then it will just add each new month to the total?  To do that I think there would have to be some code to deal with the null values in the blank months.  Or is there a better way that can just calculate on the months which are there?
Thanks again for your help.

Try this query.  It uses just the original table.  Essentially it gives a Satisfaction quotient for each month, and also calculates a year-to-date satisfaction quotient.  I noticed you included just Apr and May in your Satisfaction calc in qryScoresFinal, but I suspect you want it on-going as the year develops - this does it.

TRANSFORM Round(-Sum(Q44>=7)/Count(Q44),4) AS CountOfQ44
SELECT Data_tbl.OperName, Round(-Sum(Q44>=7)/Count(Q44),4) AS ytdSatisfaction
FROM Data_tbl
GROUP BY Data_tbl.OperName
ORDER BY Data_tbl.OperName
PIVOT MonthName(DatePart("m",[SDate])) In (January,February, March, April, May, June, July, August, September, October, November, December);
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

I don't download as long as progress is being made. Anyway, unless the file is compatible with Access version 2000, I wouldn't be able to read it.

<<<<Would the best approach be to code a sum of all the columns (Jan+Feb+Mar ... Dec) and then it will just add each new month to the total?>>>> Yes, provided you use the null to zero function in the Transform statement as illustrated below.  That way, months with activity will fill in automatically, and months in the "future" will display 0, and it will be clear thru what month your year to date total relates to.  

TRANSFORM nz(Sum(AmountField),0) AS XtabMonthAmount
SELECT Sum(AmountField) AS XtabMonthTotal
FROM YourTable/Query
WHERE YourDateField between #1/1/2009# and #12/31/2009#
Group By __________________________________________
Order By __________________________________________
PIVOT Pivot MonthName(DatePart("m",[YourDatefield])) In(January, February..................................December)


Two great solutions here -- thanks!

I'm not sure quite how they work yet, but the results are right.


GRayL - One extra question -- what does the "-Sum" do in your formula?

Inside the -Sum() is a condition which if true evaluates to -1, false to 0,  thus effectively counting all the true conditions by taking their sum, which would be a minus number, hence -Sum().  Now you know how to count by summing ;-)


That is a very nice trick -- thanks again!

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