Trying To Sum MonthlyExpenses To YtdExpenses

Oct 16, 2006

I have a Monthlyexpense column. How do I Sum up this column and put the Total in my ytdexpenses column. Do I use a stored procedure, because I want the monthlyExpenses to SUm up every time I submit a monthly expense to the database and siplay in the ytdExpenses Column.
When I Write a Query all of the rows in the ytdExpenses shows the same amount and do not total up every time I submit to the database. Help please.

monthlyExpenses ytdExpenses
$1,000 $1,000
$2,000 $3,000
$3,000 $6,000
$2,000 $8,000
$5,000 $13,000

khtan
Flowing Fount of Yak Knowledge

Singapore
3773 Posts

View 8 Replies

Update YtdExpenses With A Stored Procedure

Oct 16, 2006

I'm trying to update the ytdExpenses from the monthly Expenses. I'm submitting to the SQL database a currency amount everymonth and need for the ytdExpenses to be calculated from the sum of the monthly expenses so that I can display it on a form. I've tried everything and still can't figure this out. Will I need a stored procedure or a trigger? I want to place the update ytdExpense total at the top of my database so when I open a new for the form picks up the top most value and displays it in the form. Also, as soon as someone submit to the database the ytdExpense should calculate and show up on the form right then. My database looks like this

ReportID � � ReportDate � � monthlyExpense � � ytdExpenses

4 � � � � � � � � � � � 10/12/06 � � � � � � � � � � � $1000 � � � � � � � � � � � � $10,000
3              11/12/06             $2000             $9,000
2              12/12/06             $3000              $7,000
1              1/12/06              $4000              $4,000

I just want to add the monthly expenses up and display them in the ytdExpenses Column at the top everytime someone submit tho the database. That way the form picks up the ytdExpenses at the top of the database everytime. Here is what I got so far...Thanks ahead of time....

create trigger tu_trigger_name on yourtable for update
as
begin
update t
set ytdExpenses = t.ytdExpenses + i.monthlyExpenses - d.monthlyExpenses
from inserted i inner join deleted d
on i.pk = d.pk
inner join yourtable t
on i.pk = t.pk
end

View 20 Replies

View Related