Creating a balance sheet form

[Nicholas Scarpinato]

I have a form that displays the records from a table which stores checking
account and savings account debits and credits. I want to use this form to
calculate the balance of each account to show the effect of each transaction
on the account balance. For example, if I have the following transactions:

Checking
-10.00
20.00
-10.00
50.00

I want to see the running balance of the account for each item, like this:

Checking // Balance
-10.00 // -10.00
20.00 // 10.00
-10.00 // 0.00
50.00 // 50.00

How do I calculate that in a continuous form?

 

[kingston via AccessMonster.com]

You can do this in a report fairly easily by using the property RunningSum.
Otherwise, you'll want to create a query to feed the form. This can be done
in a form, but it would be messy. The way to do this in a query is to add a
field (I'm going to assume that there is a transaction date field that is
available):

CurrentBalance = DSum("Amount","MyTable","TransactionDate<=#" &
[TransactionDate] & "#")

 

[Nicholas Scarpinato]

This only works if the transaction dates are different, I'm assuming... I
have multiple entries on the same date though. Also, where would I put this,
it looks like vBA code... how would I use that in my query?

You can do this in a report fairly easily by using the property RunningSum.
Otherwise, you'll want to create a query to feed the form. This can be done
in a form, but it would be messy. The way to do this in a query is to add a
field (I'm going to assume that there is a transaction date field that is
available):

CurrentBalance = DSum("Amount","MyTable","TransactionDate<=#" &
[TransactionDate] & "#")

I have a form that displays the records from a table which stores checking
account and savings account debits and credits. I want to use this form to
calculate the balance of each account to show the effect of each transaction
on the account balance. For example, if I have the following transactions:

Checking
-10.00
20.00
-10.00
50.00

I want to see the running balance of the account for each item, like this:

Checking // Balance
-10.00 // -10.00
20.00 // 10.00
-10.00 // 0.00
50.00 // 50.00

How do I calculate that in a continuous form?

 

[kingston via AccessMonster.com]

No, it works if the date is equal to or less than the indicated date.
However, I just assumed that there is a date field that can be used to
sequence the transactions; you might have a transaction id that would work
better (e.g. transaction id <= specific transaction id). You did not provide
enough information about your data structure for me to suggest what to use.

Anyway, this can be used in a query, form control, or VBA. Again, I don't
know enough about how your form is constructed, what fields are available,
what they mean, etc. Basically, the DSum() function allows you to sum a
field in a dataset based on some criteria. It can be used to calculate a
running sum if there is a field available that can provide a sequence. HTH

This only works if the transaction dates are different, I'm assuming... I
have multiple entries on the same date though. Also, where would I put this,
it looks like vBA code... how would I use that in my query?

You can do this in a report fairly easily by using the property RunningSum.
Otherwise, you'll want to create a query to feed the form. This can be done

[quoted text clipped - 25 lines]

 

[Nicholas Scarpinato]

I don't really have a transaction ID per se, I'm building this database based
off a simple spreadsheet I created so I didn't need one when it was in
spreadsheet form. The only reason I wanted to put it in Access is so I don't
have to copy all the formulas again every time I add new lines to the sheet
(I'm inserting lines and deleting and moving them all the time as things are
added and billing dates change, etc.). I don't know if this will even work
through Access, because at this point it seems like Access itself can't do
the process I'm trying to create. I guess what I'll have to do is write a vBA
script that runs whenever you calculate the data (either by an autoupdate on
the main form when you change something or a calculate button) If I did this
with DSum I would need some sort of a transaction ID, and the easiest way to
do that is to add an ID Number autonumber field, but according to what I'm
reading, that only works if you're adding data to the end of the sheet; I
would be inserting data at the current date and so on, so the autonumber
wouldn't be in the correct sequence. I guess the problem now is getting the
transactions that fall on the same date to sequence out correctly, although
that shouldn't really matter I guess.

No, it works if the date is equal to or less than the indicated date.
However, I just assumed that there is a date field that can be used to
sequence the transactions; you might have a transaction id that would work
better (e.g. transaction id <= specific transaction id). You did not provide
enough information about your data structure for me to suggest what to use.

Anyway, this can be used in a query, form control, or VBA. Again, I don't
know enough about how your form is constructed, what fields are available,
what they mean, etc. Basically, the DSum() function allows you to sum a
field in a dataset based on some criteria. It can be used to calculate a
running sum if there is a field available that can provide a sequence. HTH

This only works if the transaction dates are different, I'm assuming... I
have multiple entries on the same date though. Also, where would I put this,
it looks like vBA code... how would I use that in my query?

You can do this in a report fairly easily by using the property RunningSum.
Otherwise, you'll want to create a query to feed the form. This can be done

[quoted text clipped - 25 lines]

How do I calculate that in a continuous form?

 

[kingston via AccessMonster.com]

I think a lot of people go through the same issues when they transition from
Excel to Access. Both are very powerful tools. There is much overlap in
their capabilities and it's best to not try to get one to work like the other
(although it is possible). Besides inherent limitations in Excel, such as
65K+ maximum rows, Excel doesn't deal with separate sets of data very well,
especially when there are data relationships. On the other hand, Access
simply does not work well as a spreadsheet (some functionality can be
recreated but only through much effort). To boil things down, if you are
dealing with small datasets, a low number of dataset relations, and need
complicated analysis of singular data points, stick with Excel. If you want
to store large amounts of data (relative to Excel), have complex
relationships in data structure, and want to quickly produce applications
that deal with this type of data model, use Access. There's a lot more to
this than what I've stated but I think you're getting the wrong idea about
Access (and unfortunately it may be my fault).

So, for example, you're inserting and deleting rows in Excel to reorder your
entries. In Access, you would not have to do this since an automatically
populated date and time field could keep things in order for you. (You
really don't need to do this in Excel either, but I can't say that this is
true in all situations.) In Access, all you'd have to do is make sure the
date and time are correct for each entry; you would not need to manipulate
records, copy calculations, redo a form, etc. HTH

I don't really have a transaction ID per se, I'm building this database based
off a simple spreadsheet I created so I didn't need one when it was in
spreadsheet form. The only reason I wanted to put it in Access is so I don't
have to copy all the formulas again every time I add new lines to the sheet
(I'm inserting lines and deleting and moving them all the time as things are
added and billing dates change, etc.). I don't know if this will even work
through Access, because at this point it seems like Access itself can't do
the process I'm trying to create. I guess what I'll have to do is write a vBA
script that runs whenever you calculate the data (either by an autoupdate on
the main form when you change something or a calculate button) If I did this
with DSum I would need some sort of a transaction ID, and the easiest way to
do that is to add an ID Number autonumber field, but according to what I'm
reading, that only works if you're adding data to the end of the sheet; I
would be inserting data at the current date and so on, so the autonumber
wouldn't be in the correct sequence. I guess the problem now is getting the
transactions that fall on the same date to sequence out correctly, although
that shouldn't really matter I guess.

No, it works if the date is equal to or less than the indicated date.
However, I just assumed that there is a date field that can be used to

[quoted text clipped - 17 lines]