Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Reset Running Sum at year end.

Posted on 2004-08-01
19
Medium Priority
?
325 Views
Last Modified: 2012-08-13
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),"Fixed") AS NSales, (SELECT Format(Sum(A.NSales),"Fixed") FROM [WeeklyGroupQuery] AS A WHERE A.WDate <=W.WDate) AS RunningTotal
FROM WeeklyGroupQuery AS W
GROUP BY W.WDate
ORDER BY W.WDate;
0
Comment
Question by:perrypshah
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 8
19 Comments
 
LVL 32

Expert Comment

by:jadedata
ID: 11688566
run it as two separate queries,
then bring them back together with a union query
0
 

Author Comment

by:perrypshah
ID: 11688708
I have table with four years data how can ?I have sepreat query with runnig sum reset at each year end?
0
 
LVL 32

Expert Comment

by:jadedata
ID: 11688865
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
0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 

Author Comment

by:perrypshah
ID: 11689037
can you direct me to sample SQL Statments? for  yearly data query.
0
 
LVL 32

Accepted Solution

by:
jadedata earned 500 total points
ID: 11689101
determine which years are available
  select distinct year(W.WDate) as AvailableYear FROM Tablename Group BY year(W.WDate);

SELECT W.WDate AS WYDate, Format(Sum(W.NSales),"Fixed") AS NSales, (SELECT Format(Sum(A.NSales),"Fixed") FROM [WeeklyGroupQuery] AS A WHERE A.WDate <=W.WDate) AS RunningTotal
FROM WeeklyGroupQuery AS W
WHERE (year(W.WDate)=2004)
GROUP BY W.WDate

(the above presumes that you had the rest of the query working before...)

I also feel compelled to point out that this could/should be done in an Access report format that will do the running sum over specified groups like the Year the data pertains to, by including the year(w.wdate) expression as a field in the recordsource.

Access Queries require inordinate effort to get correct running sums.  Reports do them by design

0
 

Author Comment

by:perrypshah
ID: 11689122
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?
0
 
LVL 32

Expert Comment

by:jadedata
ID: 11689135
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
0
 

Author Comment

by:perrypshah
ID: 11689145
Which function on report is it DSum? or Control Source Running Sum "Yes"?
0
 
LVL 32

Expert Comment

by:jadedata
ID: 11689182
See "RunningSum" in the help system.

DSum is not the right tool for the job.
0
 

Author Comment

by:perrypshah
ID: 11689206
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?
0
 
LVL 3

Expert Comment

by:ehartfield2002
ID: 11689509
Have your report group by Year(WYDate).  Then your control will only sum over a single year.
0
 

Author Comment

by:perrypshah
ID: 11689601
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.  
0
 
LVL 32

Expert Comment

by:jadedata
ID: 11689742
How does the above bear on the original question of getting running sums to work???
0
 

Author Comment

by:perrypshah
ID: 11689762
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".
0
 
LVL 32

Expert Comment

by:jadedata
ID: 11689802
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.

0
 

Author Comment

by:perrypshah
ID: 11689823
I am more Interested in YTD NOT necessary in Running Sum I thought I can achieve YTD by Achieving Running Sum.
0
 
LVL 32

Expert Comment

by:jadedata
ID: 11690129
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....
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

610 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question