Cumulative Query

[woodlandstx]

I have the following SQL, it is supposed to make Januarys total add to februarys and show in february, then add februarys field to marchs total and put that in marchs field.

If it is putting them all in order but I am not getting a running total I don't know what I am getting. The numbers are WAY bigger than they are in reality.

EnterYear EnterMonth Running Total
2000 01 4050000
2000 02 33236049
2000 03 214689277
2000 04 179353656
2000 05 139258242
2000 06 448113070
2000 07 365324096
2000 08 446217768
2000 09 303536100
2000 10 539436150
2000 11 636065192
2000 12 485340894



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
HAVING ((([11- Monthly Totals Query].EnterYear)>"1999"))
ORDER BY [11- Monthly Totals Query].EnterYear, [11- Monthly Totals Query].EnterMonth;

Any help is greatly appreciated~~~


Thanks~!

 

[rac2]

This may be difficult to do in a query, certainly it is a novel problem for me. Let me try to solve it.

First the HAVING and ORDER BY probably are not needed. HAVING is used to eliminate rows from the result of a GROUP BY. In this case that could be done by eliminating any detail rows with the WHERE clause; and GROUP BY sometimes orders results anyway. In any case we can put them in later once we crack the tough nut, a running total by month.

Next I am going to assume that [11- Monthly Totals Query] is a query that has monthly totals for each month since the beginning and that they are identified by year and month in the columns named EnterYear and EnterMonth with the monthly total in the column named [EST VALUE]. (I am a little confused here as to why you have quote marks in a column name.)

And I am going to assume that you are looking for the year-to-date total as of the end of each month for a specific year.

I think a new table may help. This table is just for this report, it defines the buckets that you want to use for the totals. Each bucket is a month, so the table has twelve rows. The table name is Months with a column named thru and values 01 to 12. We JOIN this table to [11- Monthly Totals Query], which I will refer to as MonthlyTotals for brevity.

SELECT Months.thru, SUM([EST VALUE])
FROM Months
LEFT JOIN MonthlyTotals ON
    MonthlyTotals.EnterMonth BETWEEN '01' AND Months.thru
GROUP BY Months.thru
WHERE MonthlyTotals.EnterYear = '2000'

You might check whether this is producing the correct results by looking at this query.

SELECT Months.thru,
       MonthlyTotals.EnterYear,
       MonthlyTotals.EnterMonth,
       MonthlyTotals.[EST VALUE]
FROM Months
LEFT JOIN MonthlyTotals ON
    MonthlyTotals.EnterMonth BETWEEN '01' AND Months.thru
WHERE MonthlyTotals.EnterYear = '2000'

That query should yield 1+2+3+4+5+6+7+8+9+10+11+12=78 rows.

So maybe I have not understood the structure of your data; or maybe this idea is all wet; or maybe there is some feature in Access that automatically provides running totals. Let me know.

 

[woodlandstx]

SELECT Months.thru, SUM([EST VALUE])
FROM Months
LEFT JOIN MonthlyTotals ON
Monthly Totals Query.EnterMonth[COLOR=red yellow]
AND[/color] BETWEEN '01' AND Months.thru
GROUP BY Months.thru
WHERE Monthly Totals Query.EnterYear = '2000'

It made me put the AND above in the expression, then it gives me the:

Syntax Error (missing operator) in query expression 'Monthly Totals Query.EnterMonth'

???

Thanks !~

 

[amorous]

May be this is what you need:

SELECT Months.thru, SUM([EST VALUE])
FROM [b](Months
LEFT JOIN MonthlyTotals ON
    Monthly Totals Query.EnterMonth)[/b]
BETWEEN '01' AND Months.thru
GROUP BY Months.thru
WHERE Monthly Totals Query.EnterYear = '2000'

observe the parentheses
-VJ

 

[rac2]

Hmmm. I hate to say it but it worked for me. Although I use SQL Server, not Access, so that could be relevant. Here are some links posted by Terry L. Broadbent, a true master, regarding running totals.


<

http://support.microsoft.com/support/kb/articles/Q208/7/14.ASP>

<

http://support.microsoft.com/support/kb/articles/Q205/1/83.ASP>

<

http://www.fabalou.com/Access/Queries/querycreator.htm>

 

[JonFer]

Hey woodlandstx - once more into the breach ...

Assuming [11- Monthly Totals Query] has a single record for each year and month, you can do this:

SELECT A.EnterYear, a.EnterMonth, SUM(b.[EST VALUE]) as ESTVALUE
FROM [11- Monthly Totals Query] as a
INNER JOIN [11- Monthly Totals Query] as b ON
a.Year=b.Year
WHERE b.EnterMonth <= a.EnterMonth
And [11- Monthly Totals Query].EnterYear > '1999'
GROUP BY A.EnterYear, a.EnterMonth

Aliasing the tables makes it easier to work with. Use an Inner Join because you'll always have a matching record using "<=".

If there are multiple records for each year and month, you need to summarize [11- Monthly Totals Query] first and then use that summary query in the above SQL.

 

[woodlandstx]

Here is what I have now:

SELECT Months.thru, SUM([EST VALUE])
FROM (Months
LEFT JOIN MonthlyTotals ON
[COLOR=red yellow](text[/color] Monthly Totals Query.EnterMonth)
BETWEEN '01' AND Months.thru
GROUP BY Months.thru
WHERE Monthly Totals Query.EnterYear = '2000'

It gave me the Syntax error again until I added a comma above.

Now it is having a problem with GROUP saying:

Syntax Error on Join Operation

I have looked at all of the links that you provided, and the would be great if I was just looking to add two numbers.

I need a running monthly ytd total for each month of each year:

January 2000 = 3
February 200 = 3+5=8
March 2000 = 8+4=12

All on one row of a cross-tab query....?

Thanks!~

 

[woodlandstx]

Again Jonfer I have tried your methods, and they put things neatly in order by year and by month but how do I get a cumulative total:

This is what I end up with in the sql, as there were some errors:

SELECT a.EnterYear, a.EnterMonth, Sum(b.["EST VALUE"]) AS ESTVALUE
FROM [11- Monthly Totals Query_Crosstab], [11- Monthly Totals Query] AS a INNER JOIN [11- Monthly Totals Query] AS b ON a.EnterYear = b.EnterYear
WHERE (((b.EnterMonth)<=[a].[EnterMonth]) AND (([11- Monthly Totals Query_Crosstab].EnterYear)>'1999'))
GROUP BY a.EnterYear, a.EnterMonth;


This is what the output is:

EnterYear EnterMonth ESTVALUE
2000 01 20250000
2000 02 166180245
2000 03 1073446385
2000 04 896768280
2000 05 696291210
2000 06 2240565350
2000 07 1826620480
2000 08 2231088840
2000 09 1517680500
2000 10 2697180750
2000 11 3180325960
2000 12 2426704470

Here are the actual numbers we begin with:

EnterYear "Total Of ""EST VALUE""" 01 02 03 04 05 06 07 08 09 10 11 12
2000 80890149 1350000 3398007 14769200 2902000 790500 21601600 854205 3914240 1009598 9348000 19570799 1382000
2001 124059471 6752200 4273500 4018500 3466653 6009016 39161000 3673102 14828900 6307000 8509604 21580000 5479996
2002 45682556 1618001 3639000 6949670 2439145 7191000 2238645 1656700 4648050 2512000 4514940 2740305 5535100
2003 63909652 2128491 7934040 14373420 3883400 1897406 15508675 907660 4588619 3340460 926977 6607004 1813500
2004 14429987 4371250 3710504 3770113 2578120
As you can see it is not doing a cumulative total....honestly I can't figure out what it is doing?

Thanks !~

 

[JonFer]

Your posted SQL has three occurrences of the table so you're adding a lot more rows than you want. Look in my SQL - you should just have 2 occurrences aliased as "a" and "b":

FROM [11- Monthly Totals Query] AS a INNER JOIN [11- Monthly Totals Query] AS b ON a.EnterYear = b.EnterYear

 

[woodlandstx]

Can you clarify what you mean by a summary query?

Thanks!~

 

[woodlandstx]

Nevermind....I have come to my own conclusion here

Oddly enough after much thought....all I did
was add a column inbetween every month and add
them....in my crosstab.

So, I have resolved the problem...thanks