Solved

Running sum- plain SQL

Posted on 2000-02-14
6
585 Views
Last Modified: 2008-03-03
Hi Having the table , with fields:

Account
Year
Period
Amount

I want to create a new table (create table SQL)

Account
Year
Period
Amount
RnYear
RnTot


Where the two last fields are running totals (over year and over all)

This is to be done in a query in MSDE or SQL server so i cannot use DSum(), or any VBA  etc.. plain SQL

If I have to do it in two SQL (first create and then update so be..)

An examp:

Account   Period   Year   Amount
2020        1      1998     500
2020        2      1998     200
2089        1      1998     400
2089        3      1998     300
2020        1      1999     500
2020        2      1999     200
2089        1      1999     400
2089        3      1999     300
   
Sould give me in the new table

Account Period Year Amount rnTot rnYear
2020      1    1998   500   500   500
2020      2    1998   200   700   700
2020      1    1999   500   1200  500
2020      2    1999   200   1400  700
2089      1    1998   400   400   400
2089      3    1998   300   700   700
2089      1    1999   400   1100  400 2089      3    1999   300   1400  700  

I'm going to create a accumulative table for speeding up accounting reports, so I'm gonna delete and re-create the table every night.

anyone??

0
Comment
Question by:perove
[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
  • 4
  • 2
6 Comments
 
LVL 7

Expert Comment

by:tchalkov
ID: 2521833
The TSQL(the language of SQL Server and msde) is not plain SQL - it is very powerful.

So my suggestion is to use a cursor, which contains the records in the first table ordered by account, year and period and a loop over the records in the cursor.
So in this loop it is very easy to calculate what you need and put it in another table.

If you need help with the TSQL which will do the work - say and I'll help you.
0
 
LVL 9

Author Comment

by:perove
ID: 2521909
Yes, Help me.
I'm a rather experienced Access/jet & visual basic developer, but when it comes to SQl server 7.0 im more of a novise.

Using the SQLDMO I can create job that I schedule to run wher needed, so if the TSQL can be run as a job on SQL server I'm happy-very happy.

Give me som ex. on where to put the Tsql on the server and how to add these into a job, and I'm there (i think)

thanks  for a quick reply,

Keep it up

perove
0
 
LVL 7

Accepted Solution

by:
tchalkov earned 200 total points
ID: 2522012
Here is the TSQL statement which goes through table1 and writes the resluts in table2 then prints the resluts.
Create a job on the server with one step of TSQL type. Then place the code there. Or alternatively you can create a stored procedure and call it instead of putting the entire code.

use pubs
declare mycursor cursor for select * from table1 order by account,year,period
open mycursor
declare @id int,@account integer, @period integer , @year integer, @amount float
declare @curaccount integer, @curyear integer
declare @rntot float, @rnyear float
fetch next from mycursor into @id, @account, @period, @year , @amount
select @rntot=0
select @rnyear=0
select @curyear=@year
select @curaccount=@account
delete from table2
while (@@FETCH_STATUS=0)
begin
      if (@curaccount=@account)
      begin
            select @rntot=@rntot+@amount
            if (@curyear=@year)
                  select @rnyear=@rnyear+@amount
            else
            begin
                  select @rnyear=@amount
                  select @curyear=@year
            end
      end
      else
      begin
            select @rntot=@amount
            select @rnyear=@amount
            select @curyear=@year
            select @curaccount=@account
      end
      insert table2(account,period,year,amount,rntot,rnyear) values(@account, @period, @year, @amount, @rntot, @rnyear)
      fetch next from mycursor into @id, @account, @period, @year , @amount
end
select * from table2
deallocate mycursor
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 9

Author Comment

by:perove
ID: 2522110
Will test it immidiatly, it's looking good and straight-froward, as ususl the problem when starting working with new tool it is to know the POSSIBILITY on what you can.

Anyway thanks a mill, I will test this and let you know!

regards from perove in Norway
0
 
LVL 9

Author Comment

by:perove
ID: 2522844
Yes, got it, had to modefy som of the code, (it's int not integer and so on....) but I got the whole picture.

Excellent answer tchalkov! , and again I have to think about how great this site is (you hear EE)!

Anyway, thanks a mill for the input (you have any links for T-SQL debugging and advaced scriting)

perove

0
 
LVL 9

Author Comment

by:perove
ID: 2522845
Yes, got it, had to modefy som of the code, (it's int not integer and so on....) but I got the whole picture.

Excellent answer tchalkov! , and again I have to think about how great this site is (you hear EE)!

Anyway, thanks a mill for the input (you have any links for T-SQL debugging and advaced scripting)

perove

0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Can not open the Access Help ? 3 42
Access 2007 Compact and Repair Back-End Database 9 53
MS Access 2016 resize forms 3 12
Unidentified Function 2 32
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

752 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