Cross-Tab Running Sum/Total? 1

[woodlandstx]

I have a cross-tab

Expr1 January February March April May June July August September October November December
1999 124,799.00
2000 450,000.00 308,909.73 984,613.33 322,444.44 131,750.00 1,542,971.43 77,655.00 244,640.00 126,199.75 2,029,000.00 1,223,174.94 172,750.00
2001 519,400.00 474,833.33 334,875.00 346,665.30 1,201,803.20 1,713,521.74 229,568.88 872,288.24 573,363.64 607,828.86 3,082,857.14 498,181.45
2002 161,800.10 303,250.00 365,772.11 174,224.64 553,153.85 131,685.00 150,609.09 464,805.00 279,111.11 265,584.71 342,538.13 307,505.56
2003 193,499.18 528,936.00 1,105,647.69 228,435.29 269,452.43 775,433.75 82,514.55 305,907.93 278,371.67 55,998.65 600,636.73 164,863.64
2004 485,694.44 247,366.93 251,340.87 130,968.33
This one is based on average, I need one that will give

January February March April
2000 200 =200+400=600 and so on...

If that doesn't make sense let me know.

Thanks!~

 

[JonFer]

Are you trying to do the YTD and the crosstab in one query or are you building a crosstab query on top of the YTD query? Are you getting an error message? What is preventing you from making the crosstab?

 

[woodlandstx]

OK, after looking at the numbers, it is not adding january 1999 to february 1999 in my cumulative query, it is just restating the monthly numbers from the monthly totals query:

Monthly Totals:

SELECT Format([EnteredDate],"yyyy") AS EnterYear, Format([EnteredDate],"mmmm") AS EnterMonth, Sum([11- Proposals].[Est Value]) AS ["EST VALUE"]
FROM [11- Proposals]
GROUP BY Format([EnteredDate],"yyyy"), Format([EnteredDate],"mmmm");

Cumulative Query:

SELECT [11- Monthly Totals Query].EnterYear, [11- Monthly Totals Query].EnterMonth, Sum(YourTableCopy.["EST VALUE"]) AS [Running Total]
FROM [11- Monthly Totals Query] INNER JOIN [11- Monthly Totals Query] AS YourTableCopy ON [11- Monthly Totals Query].EnterYear = YourTableCopy.EnterYear
WHERE ((([11- Monthly Totals Query].EnterMonth)>=[YourTableCopy].[EnterMonth]))
GROUP BY [11- Monthly Totals Query].EnterYear, [11- Monthly Totals Query].EnterMonth;

Because of the format of the query results it looked like it was summing the numbers its not, its only putting them in sequential order in the results of the query.

2002 February 16261295
2002 January 17879296
2002 July 19535996
2002 June 21774641
2002 March 28724311
2002 May 35915311
2002 November 38655616
2002 October 43170556
2002 September 45682556

 

[JonFer]

You have to use the "mm" format with the month so that January ("01") is less than February ("02"). Using the "mmm" or "mmmm" format sorts the month names alphabetically. You can modify the month format on the final query or leave it as "mm".

Another option is to keep "EnteredDate" a date but make it the first of the month in question. Then it is easy to use Format() function on it to get any date component.

 

[woodlandstx]

OK...well, I need to change it to mmmm, do I do that in the cumulative query or the cross-tab and where?

Thanks!~

 

[JonFer]

Try using the beginning of the month (BOM) option then where you keep a date field in your monthly totals query which is the first of the month. You can use DateAdd to adjust the EnteredDate.

SELECT DateAdd("d",-Day([EnteredDate])+1,EnteredDate) AS EnterBOM, Sum([11- Proposals].[Est Value]) AS "EST VALUE"
FROM [11- Proposals]
GROUP BY DateAdd("d",-Day([EnteredDate])+1,EnteredDate);

Then your YTD would be like this (no guarantee for being error-free) -

SELECT Year([11- Monthly Totals Query].EnterBOM) as EnterYear,
Format([11- Monthly Totals Query].EnterBOM,"mmm") as EnterMonth,
Sum(YourTableCopy.[EST VALUE]) AS [Running Total]
FROM [11- Monthly Totals Query], [11- Monthly Totals Query] AS YourTableCopy
WHERE Year([11- Monthly Totals Query].EnterBOM)=Year(YourTableCopy.EnterBOM) AND
[11- Monthly Totals Query].EnterBOM>=[YourTableCopy].[EnterBOM]
GROUP BY Year([11- Monthly Totals Query].EnterBOM), Format([11- Monthly Totals Query].EnterBOM,"mmm") ;

 

[woodlandstx]

Is there no other way to change it to January, February as creating more fields is now what I was hoping for.

I get the Data Type Mismatch error when using the first statement.

 

[JonFer]

Do you have some rows with Nulls in the EnteredDate field? That will give you the error. You can add a criteria to exclude those (Where EnteredDate Is Not Null) in the first query or look into recreating the table if they are not supposed to be Null.

 

[woodlandstx]

Well, it was a pain but I kept the format and just changed the column headings in my form.