Running Totals in a Database

  • Thread starter Thread starter SueW
  • Start date Start date
S

SueW

I have a department that is keeping track of running totals for various funds
for our customers just like a checkbook. Staff enters a deposit or
withdrawal and the balance is automatically updated. This file is in Excel
of course. The rest of the data is in Access. Staff would like to put the
Excel worksheet into the Access database; therefore, having to only enter
data in one place. I don't know how to keep a running total. Is this
possible in Access?
 
Yes it is certainly possible.

However, in my opinion, you do NOT want to actually store the running
total in the database. Instead, you want to just store all of the
transactions (and possibly a starting balance) in the database, and
whenever you need the total, you would execute a query to show the
current total.

So, without knowing your details, you could setup a table with these
fields:

transactionID
transactionDate
transactionType (i.e. Debit or Credit)
transactionAmount
etc

Then you could create a form that allows you to enter transactions,
and then a query/report to show the total or whatever other
information.
 
Sue

.... depends ...

What is the requirement?

Does your user need to see the "running total" in conjunction with each and
every transaction, or the running total as of the most recent transaction?

.... or possibly the running total as of a specific, designated transaction?

"How" depends on "what"...

--

Regards

Jeff Boyce
Microsoft Access MVP

Disclaimer: This author may have received products and services mentioned in
this post. Mention and/or description of a product or service herein does
not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.
 
It is not a total after each transaction. The formula in Excel is written to
include a range of cells, i.e., g9:g502 so as they enter a transaction in the
spreadsheet the balance is automatically calculated. G4 contains the amounts
allocated (g9:g502), G5 is the total program obligated $ for four categories
(H5:K5), the program balance is in G6 (g4-g5).
 
If Excel can already do this, is there a "pressing" reason why you need to
try to duplicate the functionality in Access?

What about using Access to select the records, then export the information
(export a query) to Excel, which people already use and understand?

--

Regards

Jeff Boyce
Microsoft Access MVP

Disclaimer: This author may have received products and services mentioned in
this post. Mention and/or description of a product or service herein does
not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.
 
SueW said:
I have a department that is keeping track of running totals for various
funds
for our customers just like a checkbook. Staff enters a deposit or
withdrawal and the balance is automatically updated. This file is in
Excel
of course. The rest of the data is in Access. Staff would like to put
the
Excel worksheet into the Access database; therefore, having to only enter
data in one place. I don't know how to keep a running total. Is this
possible in Access?
Click to expand...
 
jkjkjk

Jeff Boyce said:
If Excel can already do this, is there a "pressing" reason why you need to
try to duplicate the functionality in Access?

What about using Access to select the records, then export the information
(export a query) to Excel, which people already use and understand?

--

Regards

Jeff Boyce
Microsoft Access MVP

Disclaimer: This author may have received products and services mentioned
in
this post. Mention and/or description of a product or service herein does
not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.
Click to expand...
 
Back
Top