summing values in each record (summing across rows)

[Madison]

I am creating a form to help train tellers in cash counting. It will be a
mock-up of a system they use while helping customers. Each bill and coin
denomination is listed. I've labeled the fields loose_100, loose_50, etc.
and strapped_100, strapped_50, etc. for each of the bills. The coins are
similarly labeled for loose and rolled.

I need my field "total" to give a running total as the teller enters each
amount and tabs to the next field. This total will be saves with each
record. Eventually, I will use the sum of the total column for an exercise
in balancing their cash drawer.

So far I have had no luck setting default values. I know I can sum values
down columns but essentially I want to sum across each row and have that row
sum value saved as a field in each record.

 

[Clifford Bass]

Hi,

Create a function that does the computation and assign that function as
the After Update property of each field: =CalculateBalance()

Public Function CalculateBalance() As Currency

CalculateBalance = Nz(loose_100, 0) * 100 + Nz(loose_50, 0) * 50 ... + _
Nz(loose_nickles, 0) * 0.05 + Nz(loose_pennies, 0) * 0.01

End Function

Where you will substitute place the other denominations that I did not
list in where the ... is. The Nz() function is needed if you allow nulls in
those fields or if you do not have a default value. If defaults are provided
and nulls are not allowed, you can eliminate the Nz() functions and just use
the fields: loose_100 * 100 + loose_50 * 50 + ...

Hope that helps,

Clifford Bass

 

[Allen Browne]

You need to build your tables relationally.

One transaction consists of multiple demoninations. So, in a relational
database like Access, you model this with these tables:
a) Transaction header table, with fields:
- TransactionID autonumber primary key
- TransactionDate date/time
- TellerID who handled this
- ClientID who the transaction was for

b) TransactionDetail table, with fields like this:
- TransactionDetailID autonumber primary key
- TransactionID number tells what transaciton this row
belongs to
- DenominationID currency what type of currency this row is
- Quantity number how many of this
denomination
- IsLoose yes/no loose coin/note, or
rolled/folded

c) Denomination table (one record for each note/coin type):
- DenominationID currency primary key

In (c), enter a record for each type of currency, e.g. $1, $5, ... and the
coins, e.g. .05, .10, .25, ...

You will also have a table of tellers, and a table of clients.

Create a query using the TransactionDetail table. Drag the * into the output
grid in query design, so the query shows all fields. In the next column,
type this expression into the Field row:
Amount: [Quantity] * [DenominationID]
Save the query as (say) qryTransactionDetail.

To interface this, create a main form bound to the Transaction table, with a
subform bound to qryTransactionDetail. Design the subform so it's in
Continuous Form view: labels in the Form Header section above each column,
and text boxes in the Detail section side-by-side. Use a combo for the
Denomination. In the Form Footer, add a text box with Control Source:
=Sum([Amount])

The text box in the subform footer now shows the total as soon as each row
is saved.