MS Access 2003 - Performing Calculations in a table's field with Transactions Table

Question

One thing I want to do is build a personal database for myself at home to use a financial database (transaction log, checking/savings account tables, etc), and I want to do this mainly to learn more about developing databases. I am pretty familiary with MS Access, though not put to use in this context, but what I am really trying to learn is SQL Server.

SO, that being said, the first question that popped into my mind is that if I have a transactions table that I would want to use as a ledger, then is there some method to have the table automatically perform a calculation for one field (balance) based on another field(s) (expense, revenue fields)? Similiar to what someone may do with Excel......

Or is this something I would have to do with an unbound form, and an UPDATE statement kinda of approach?? if a table constraint exists for this type of idea, I would like to learn it....

I mentioned MS Access in the title, but a SQL Server is also most appreciated. Thanks for the help!

Answer 1

In MS SQL server, you can use computed columns for this: http://msdn.microsoft.com/en-us/library/ms191250.aspx

Answer 2

Derived data should not be stored except if it needs to be indexed -- you calculate the values in your SQL statements, or in the presentation layer.

In addition to computed columns in SQL Server tables, you can have them in VIEWS and you can index them. The term is "indexed view" and when you do that, the data is persisted in a hidden temp table and updated on the fly when the data the VIEW is derived from is changed. You can read about it under the TYPES OF VIEWS topic in the same link cited in @Roland Bouman's answer.

Last of all, it's not clear to me why you mention Access at all if you're using SQL Server as your back end. Are you developing your front end in Access?