stored procdure maybe?

[hog]

This is an odd question to ask here, but I dont know where else to go to try to find out?

 

Problem:

 

I have an access query that returns the transaction history of items but without a rolling balance.

 

Solution so far:

 

I created a function with a static variable which the query calls and returns the rolling balance figure for each line retuned;

 

date qty balance

29/10/04 -1 9

01/11/04 2 11

05/11/04 4 15

 

etc.

 

Trouble is the static variable holds its value as long as the code is running so it only works first time round.

 

Big issue!

 

I use access first to get the sql right etc then use sql analsyer to create the right view for our system blah blah so dont even know if this approach would work.

 

Question....at last!

 

I know nothiing of stored procedures but would they resolve this problem? are they intended for such use?

[Joe Mamma]

date qty balance

29/10/04 -1 9

01/11/04 2 11

05/11/04 4 15

any chance you have unique datetime on this table? or a sequential transID? is there an account number????

 

select x1.acct, x1.dot, x1.balance, sum(x2.balance) from 
xact x1 left join xact x2 on x1.acct = x2.acct
where x2.dot <= x1.dot
group by x1.acct, x1.dot, x1.balance
order by x1.acct, x1.dot

 

returned

acct		dot													balance			   RunBal				
----------- ------------------------------------------------------ --------------------- --------------------- 
1		   2004-11-29 18:26:32.737								100.0000			  100.0000
1		   2004-11-29 18:26:34.800								-9.0000			   91.0000
1		   2004-11-29 18:26:36.503								5.0000				96.0000
2		   2004-11-29 18:26:38.067								100.0000			  100.0000
3		   2004-11-29 18:26:39.720								100.0000			  100.0000

Edited December 1, 2004 by Derek Stone

[Joe Mamma]

Oh yeah. . . that was with a table like:

 

 

create table xact (xactNum int identity(1,1) 
not null primary key, 
dot datetime default getDate(), 
acct int, balance money)

and inserting

insert into xact (acct , balance ) values (1, 100) 
insert into xact (acct , balance ) values (1, -9) 
insert into xact (acct , balance ) values (1, 5) 
insert into xact (acct , balance ) values (2, 100) 
insert into xact (acct , balance ) values (3, 100) 

Edited December 1, 2004 by Derek Stone