perrypshah
asked on
Reset Running Sum at year end.
How can I reset total at year end and start over at 1st January from next year? Follwing is my SQL statment from Query "Weeklyrunsum".
SELECT W.WDate AS WYDate, Format(Sum(W.NSales),"Fixe
FROM WeeklyGroupQuery AS W
GROUP BY W.WDate
ORDER BY W.WDate;
SELECT W.WDate AS WYDate, Format(Sum(W.NSales),"Fixe
FROM WeeklyGroupQuery AS W
GROUP BY W.WDate
ORDER BY W.WDate;
ASKER
I have table with four years data how can ?I have sepreat query with runnig sum reset at each year end?
build the query to be specific to each year, four times.
The patterns in this exercise can be noted and used to build a vba routine that could
determine what years are available in the data
run the query once for each year
store/report the results of each year
The patterns in this exercise can be noted and used to build a vba routine that could
determine what years are available in the data
run the query once for each year
store/report the results of each year
ASKER
can you direct me to sample SQL Statments? for yearly data query.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Above Sql statment brings data like following.
WYDate NSales RunningTotal
1/3/04 5551.95 722796.35
1/10/04 12659.26 735455.61
I need "RunningTotal" As following
WYDate NSales RunningTotal
1/3/04 5551.95 5551.95
1/10/04 12659.26 18211.21
How can I achive above result?
WYDate NSales RunningTotal
1/3/04 5551.95 722796.35
1/10/04 12659.26 735455.61
I need "RunningTotal" As following
WYDate NSales RunningTotal
1/3/04 5551.95 5551.95
1/10/04 12659.26 18211.21
How can I achive above result?
queries to not perform running sums, you will need to write a function that can do this based on the year and the "cut-off" date.
or as I said earlier, run the output to a report format that does running sums easily
or as I said earlier, run the output to a report format that does running sums easily
ASKER
Which function on report is it DSum? or Control Source Running Sum "Yes"?
See "RunningSum" in the help system.
DSum is not the right tool for the job.
DSum is not the right tool for the job.
ASKER
It dose not tell me how to get "YEAR TO DATE" sum from table or query. It can do RunningSum of texbox located on Report It self. Is there any way to get YTD data summed up on Report?
Have your report group by Year(WYDate). Then your control will only sum over a single year.
ASKER
I have Table name "Dunkin Sales" has following fields
No (PK)
Date
Shift1
Customer Count1
Shift2
Customer Count2
Gross Sales
Tax
Customer Count3
Cash Expanse
I would like to get Data for comperision of [Gross Sales]-[Tax] = [Net Sales] Last year Vs. This year on Date 06/19/2003 Vs. 06/19/2004 (Year To Date) how do I get this in my report? So I can track sales tren up or down.
No (PK)
Date
Shift1
Customer Count1
Shift2
Customer Count2
Gross Sales
Tax
Customer Count3
Cash Expanse
I would like to get Data for comperision of [Gross Sales]-[Tax] = [Net Sales] Last year Vs. This year on Date 06/19/2003 Vs. 06/19/2004 (Year To Date) how do I get this in my report? So I can track sales tren up or down.
How does the above bear on the original question of getting running sums to work???
ASKER
Jadedata,
Your suggestion is directing me to change my question and I have also incress points. End result of the question is to get YTD running sum of net sales in report "Fee Card".
Your suggestion is directing me to change my question and I have also incress points. End result of the question is to get YTD running sum of net sales in report "Fee Card".
Running sum is not necessarily the same as a YTD total, and the two may not be able to exist in the same query depending on other conditions of the query.
ASKER
I am more Interested in YTD NOT necessary in Running Sum I thought I can achieve YTD by Achieving Running Sum.
YTD is basically
SELECT Sum([Field1]) as sField1 FROM Tablename Where ([DateField] between #1/1/2004# and #8/1/2004#)
running sum is the continuous updating of that total as the query rolls forward from days to day or week to week, constantly updating the total as it goes....
SELECT Sum([Field1]) as sField1 FROM Tablename Where ([DateField] between #1/1/2004# and #8/1/2004#)
running sum is the continuous updating of that total as the query rolls forward from days to day or week to week, constantly updating the total as it goes....
then bring them back together with a union query