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!~

 

[woodlandstx]

Could I use a unbound text box to create an expression to add January 2000 to February 2000?

I think I can I just am lost as to how to do that.

Thanks!!!

 

[JonFer]

You should do the YTD calculation before the crosstab and then crosstab the result. Assuming you have a table or query with the totals for each month, create a query with two instances of it. Add a criteria that the date from the second instance of the table/query is <= the date from the first instance. Sum the amount from the second instance and it will be cumulative.

There are a few other posts on creating queries for YTD or cumulative amounts.

 

[woodlandstx]

Jonfer -

It may be because its monday morning, and I'm not awake yet,
but can you give me a little more to go on, as I have looked through the posts and cannot really find anything that is relating to what I am individually trying to do or anything I can gleen some information on how to do this.

Any help is greatly appreciated!

Thanks!~

 

[JonFer]

This thread shows how to do a running total:

thread701-708836

Once you have this working with your tables in a new query, write a new crosstab query based on the running totals query.

 

[woodlandstx]

OK, I have to say I am sorry maybe I'm being extremely dense, but I need to have it tell me the monthly totals for each year, month to date.

The thread and the others do not show me or point me in any direction to do that.

Right now I have the base query where it has:

Est Value Expr 1 Expr 2 EnteredDate
80000 January 2004 04/01/2004

I don't understand how to tell it to total each month without doing a cross-tab. And I definately don't know how to make it add the previous months total to the next and so on.

I'm sorry if I seem a bit dense, but this has really got me stumped, I spent most of last week reading through posts to resolve this problem.

Thanks!~

 

[lespaul]

Here's another thread that may be helpful:

Thread701-763983

Leslie

 

[JonFer]

Please take a look at the thread again and try to make a YTD query. The basic idea of how to do YTD is there - just modify it for your table. If you then post your SQL, I'll provide feedback on it.

 

[woodlandstx]

Leslie -

Thank you for the thread, do I have to have a field for monthly totals in my table to use that statement?

I'd rather not have to have a field in my table for it.

Also, the whole table.copy reference I am not 100% understanding.

Thanks!~

 

[woodlandstx]

Jonfer -

I have tried to do what you asked but I get all kinds of errors and issues, ending up with basically nothing when I do what you are asking.

Above is the cross tab for the yearly totals....

here is the query that I have to do the month to date or year to date totals:

SumOfEst Value Expr1 Expr2 EnteredDate
300000 1999 December 01/12/1999
0 1999 December 02/12/1999
10000 1999 December 03/12/1999
0 1999 December 06/12/1999
22000 1999 December 15/12/1999
441593 1999 December 20/12/1999
100000 1999 December 29/12/1999
130000 2000 April 10/04/2000
140000 2000 April 11/04/2000
12000 2000 April 13/04/2000
48000 2000 April 19/04/2000
2550000 2000 April 20/04/2000
20000 2000 April 24/04/2000
2000 2000 April 26/04/2000
0 2000 April 27/04/2000

or:

SELECT Sum([11- Proposals].[Est Value]) AS [SumOfEst Value], Format$([EnteredDate],"yyyy") AS Expr1, Format$([EnteredDate],"mmmm") AS Expr2, [11- Proposals].EnteredDate
FROM [11- Proposals]
GROUP BY Format$([EnteredDate],"yyyy"), Format$([EnteredDate],"mmmm"), [11- Proposals].EnteredDate
HAVING ((([11- Proposals].EnteredDate)>#11/30/1999#));


Now can someone tell me what I need to do to modify it, start all over again or what?

Thanks!~

 

[lespaul]

No, in that example the total "column" is just in the query.

Let's try to take the example from that thread and modify it for you:

SELECT Format([11- Proposals].[EnteredDate],"d mmm yy"), 
       [11- Proposals].[Est Value], 
       SUM([YourTableCopy].[Est Value]) 
           AS [Running Total] 
FROM [11- Proposals] 
         LEFT JOIN [11- Proposals] AS [YourTableCopy]
             ON  DateAdd("yyyy",-1,[11- Proposals].[EnteredDate]) <= [YourTableCopy].[EnteredDate]
             AND [11- Proposals].[EnteredDate] >= [YourTableCopy].[EnteredDate]
GROUP BY [11- Proposals][EnteredDate], 
         [YourTable][Running Total];

What happens if you paste the SQL above and run it?



Leslie

 

[woodlandstx]

Leslie -

I added a few missing "."'s

then hit the view button and I get this error:

You tried to execute a query that does not include the specified expression 'Est Value' as part of an aggregate function.

Just a quick question, I'm not understanding the [yourtablecopy] reference, could you please explain that a little more.


?

 

[lespaul]

sorry, I didn't change something, try this instead:

SELECT Format([11- Proposals].[EnteredDate],"d mmm yy"), 
       [11- Proposals].[Est Value], 
       SUM([YourTableCopy].[Est Value]) 
           AS [Running Total] 
FROM [11- Proposals] 
         LEFT JOIN [11- Proposals] AS [YourTableCopy]
             ON  DateAdd("yyyy",-1,[11- Proposals].[EnteredDate]) <= [YourTableCopy].[EnteredDate]
             AND [11- Proposals].[EnteredDate] >= [YourTableCopy].[EnteredDate]
GROUP BY [11- Proposals][EnteredDate], 
         [11- Proposals][Est Value];

The [yourtablecopy] is an alias. What it's doing from the LEFT JOIN on is joining 11-Proposal to ITSELF.

Leslie

 

[woodlandstx]

Leslie -

That works great!

Except if I try to see the design view it brings up an error screen about missing fields and may have been renamed?

One question, to use this for a cross-tab query I need to break out the month and year, where and how would I insert that into the above statement?

Thanks!!

 

[JonFer]

What you should do first is create a query (e.g "Month Totals") that summarizes the [EST Value] for each month with a Group By query. Then use that instead of [11- Proposals] in your YTD query. You don't want to do the running total by month with multiple records for each month in the table.

Select Format(EnteredDate,"yyyy") as EnterYear,
Format(EnteredDate,"mm") as EnterMonth,
Sum([EST Value]) as "EST VALUE"
From [11- Proposals]
Group By
Format(EnteredDate,"yyyy"),
Format(EnteredDate,"mm")

This is my take on the YTD query which can also be your crosstab query:

SELECT [Month Totals].[EnterYear],
[Month Totals].[EnterMonth],
SUM([YourTableCopy].[Est Value]) AS [Running Total]
FROM [Month Totals]
INNER JOIN [Month Totals] AS YourTableCopy
ON [Month Totals].EnterYear = YourTableCopy.EnterYear
WHERE [Monthly Totals].[EnterMonth] >= [YourTableCopy].[EnterMonth]

GROUP BY [Month Totals].[EnterYear],
[Month Totals].[EnterMonth] ;


Use EnterYear for the Row Heading and EnterMonth for the Column Heading.

 

[woodlandstx]

Jonfer -

This works perfectly until I try and run the second query based on the month total query.

It is asking for parameter value:

yourtablecopy.Est Value

?

Thanks for all the help!~

 

[JonFer]

You'll have to post the SQL for both queries for me to help.

 

[woodlandstx]

Jonfer -

Some of this is actually making sense and sinking in a little I think.

The query changed the Est Value field to "Est Value" is I included that in your statement above and it works great!

Thank you for all your help!!! It is GREATLY appreciated!

 

[woodlandstx]

Forgot I had one quick question, if I only need to see records >#11/01/04# how do I make that selection, as I have tried several different ways and have had no luck.

The other queries have just used the above in the EnteredDate field, but when I add it to either query it messes it up.

Thanks!

 

[woodlandstx]

OK....it is almost 100%....the only problem is I cannot use this in the cross-tab query which is how I have to have the data.

is there any way to do this and still be able to use the cross-tab?