Summing the value of two fields

[Ashes09]

I want to place the sum (difference) of two currency fields on my report (not
in the footer). It seems to me I should be able to entero
=Sum([Fee])-[Payment]) to come up with a value. This is not working, or it is
giving me a value completely incorrect.

Any suggestions?

 

[Clifford Bass]

Hi,

How about just:

=[Fee]-[Payment]

Or is there something else going on there that you have not specified?

Clifford Bass

 

[Ashes09]

I think I figured out what is happening. It is giving me a value, but it is
the total value of all fees - payments in my database, rather than for just
that specific individual.

How do I get a total just for one person?

Hi,

How about just:

=[Fee]-[Payment]

Or is there something else going on there that you have not specified?

Clifford Bass

I want to place the sum (difference) of two currency fields on my report (not
in the footer). It seems to me I should be able to entero
=Sum([Fee])-[Payment]) to come up with a value. This is not working, or it is
giving me a value completely incorrect.

Any suggestions?

 

[KARL DEWEY]

Try using a calculated field in your query like this --
Some Name: [Fee]-[Payment]

--
KARL DEWEY
Build a little - Test a little

I think I figured out what is happening. It is giving me a value, but it is
the total value of all fees - payments in my database, rather than for just
that specific individual.

How do I get a total just for one person?

Hi,

How about just:

=[Fee]-[Payment]

Or is there something else going on there that you have not specified?

Clifford Bass

I want to place the sum (difference) of two currency fields on my report (not
in the footer). It seems to me I should be able to entero
=Sum([Fee])-[Payment]) to come up with a value. This is not working, or it is
giving me a value completely incorrect.

Any suggestions?

 

[Clifford Bass]

Hi,

Well, that depends. What does you report look like? Does it contain
all paymens and fees for everyone, so there are multiple lines for those with
multiple fees/payments? If so, create a grouping on each individual and have
it calculate the sum of the difference in the group header or footer:

=Sum([Fee]-[Payment])

Is it just a summary of each person so each person only has one line?
In that case are you using a summary query? In it the query you can have it
calculate the sum of the difference. It would be an expression field that
might look like this in the top row on the query designer:

FeesLessPayments: Sum([Fee]-[Payment])

If that does not help, please be specific about your report and it's
data source. Maybe describe the data that is coming into it and how it looks
in the report. And maybe the SQL of the query if a query is used. Are there
subreports?

Clifford Bass

 

[Ashes09]

Thanks for your response. I am fairly new to Access, so bear with me...

I have a database of several names. The database contains a field for fees
charged (per person) and a field for amount of payments received (per person).

Basically what I want to do is send each personal a statement showing what
they owe. I set the report up so that it would be one page per person, and
now I want to show the individual totals per page.

Hope that makes sense...

Hi,

Well, that depends. What does you report look like? Does it contain
all paymens and fees for everyone, so there are multiple lines for those with
multiple fees/payments? If so, create a grouping on each individual and have
it calculate the sum of the difference in the group header or footer:

=Sum([Fee]-[Payment])

Is it just a summary of each person so each person only has one line?
In that case are you using a summary query? In it the query you can have it
calculate the sum of the difference. It would be an expression field that
might look like this in the top row on the query designer:

FeesLessPayments: Sum([Fee]-[Payment])

If that does not help, please be specific about your report and it's
data source. Maybe describe the data that is coming into it and how it looks
in the report. And maybe the SQL of the query if a query is used. Are there
subreports?

Clifford Bass

I think I figured out what is happening. It is giving me a value, but it is
the total value of all fees - payments in my database, rather than for just
that specific individual.

How do I get a total just for one person?

 

[Clifford Bass]

Hi,

It makes sense, but there are still some questions; more specifics are
needed. What do you use for the record source of the report? If a query,
please post the SQL of the query. To get the SQL of a query, while it is
open, click on the down-arrow on button to the far left. On it you will see
SQL View. Choose that. Then you can copy and paste the text of the SQL. If
a table, please post the table name and the pertinant fields from the table.

On each person's statement, do you show each payment and each fee that
was assessed? Or just the summary? Or do you have a previous balance, new
fees and new payments for the month and then the totals and the ending
balance?

Clifford Bass

 

[Ashes09]

I am using a table as the source, not a Query.

Basically, we run youth camps, and several campers have outstanding balances
from the last three camps. I want to do a statement per individual showing
their balance from the last three camps.

The following are fields I have in the table.

Last Name
First Name

WTR 08 Fee
WTR 08 Payments

SMR 08 Fee
SMR 08 Payments

WTR 09 Fee
WTR 09 Payments

 

[Ashes09]

I should also mention that this type of functionality worked perfectly under
Forms.

 

[Clifford Bass]

Hi,

Okay, this helps. So the basic process is to create unbound text
fields and set their Control Sources to the appropriate formulae.

For a control named [txtWTR 08 Balance]:

=[WTR 08 Fee]-[WTR 08 Payments]

For a control named [txtSMR 08 Balance]:

=[SMR 08 Fee]-[SMR 08 Payments]

For a control named [txtWTR 09 Balance]:

=[WTR 09 Fee]-[WTR 09 Payments]

And finally for a control named [txtFinal Balance]:

=[txtWTR 08 Balance]+[txtSMR 08 Balance]+[txtWTR 09 Balance]

Which is what I originally suggested. Are you saying that that is not
working?

On related issue, you really need to redesign your database so that you
do not have to make changes for every new camp session. This involves
separating out the data into several tables so that each table holds only one
type of information such as people, camping sessions and ledger (fee/payment)
information. Something like this:

tblCampers
Camper_ID (autonumber)
Last_Name
First_Name
Street_Address
City
State_Code
Postal_Code
Country
Telephone
etc.

tblCamp_Sessions
Camp_Session_ID (maybe autonumber)
Camp_Session_Description
Start_Date
End_Date
Camp_Fee
Maximum_Campers
etc.

tblLedger
Ledger_ID (autonumber)
Camper_ID (related to the Camper_ID in tblCampers)
Camp_Session_ID (related to Camp_Session_ID in tblCamp_Sessions)
Transaction_Date
Transaction_Type_Code (maybe F for Fee or P for Payment, N for (NSF)
Non-sufficient funds [bounced] checks; could have multiple fee codes such as
CF for Camping Fee and NF for NSF checks; may want a separate table that
holds possible codes)
Transaction_Amount (positive for fees, negative for payments)
other information such as check number, notes, etc.

Then you can set up your forms and reports in such a way that they will
not need modification each camp session. For forms you would have a main
form for entering campers. On the form you would have a ledger subform for
entering the fees and payments. Similarily on your report, the main report
would have the person's main information and a subreport to display the
ledger transactions and balances.

If you are using Access 2003 or earlier search for "designing a
database", including the quotes, in Access's online help. If Access 2007, go
to help an choose the "Database Design" item. Also do a search for "subform"
and "subreport, quotes not needed. <http://www.mvps.org/access/> is a good
site for resources to help learn about Access. Also,
<http://www.accessmvp.com/JConrad/accessjunkie/resources.html> looks to be a
good Access database design resource.

Hope that helps,

Clifford Bass

 

[Ashes09]

Thank you, Clifford.

This worked! My only issue now, is that I cannot get the Final Balance to
calculate unless there is an amount showing in all three Balance fields (WTR
08, SMR 08, WTR 09).

How do I get around this?

S.

Hi,

Okay, this helps. So the basic process is to create unbound text
fields and set their Control Sources to the appropriate formulae.

For a control named [txtWTR 08 Balance]:

=[WTR 08 Fee]-[WTR 08 Payments]

For a control named [txtSMR 08 Balance]:

=[SMR 08 Fee]-[SMR 08 Payments]

For a control named [txtWTR 09 Balance]:

=[WTR 09 Fee]-[WTR 09 Payments]

And finally for a control named [txtFinal Balance]:

=[txtWTR 08 Balance]+[txtSMR 08 Balance]+[txtWTR 09 Balance]

Which is what I originally suggested. Are you saying that that is not
working?

On related issue, you really need to redesign your database so that you
do not have to make changes for every new camp session. This involves
separating out the data into several tables so that each table holds only one
type of information such as people, camping sessions and ledger (fee/payment)
information. Something like this:

tblCampers
Camper_ID (autonumber)
Last_Name
First_Name
Street_Address
City
State_Code
Postal_Code
Country
Telephone
etc.

tblCamp_Sessions
Camp_Session_ID (maybe autonumber)
Camp_Session_Description
Start_Date
End_Date
Camp_Fee
Maximum_Campers
etc.

tblLedger
Ledger_ID (autonumber)
Camper_ID (related to the Camper_ID in tblCampers)
Camp_Session_ID (related to Camp_Session_ID in tblCamp_Sessions)
Transaction_Date
Transaction_Type_Code (maybe F for Fee or P for Payment, N for (NSF)
Non-sufficient funds [bounced] checks; could have multiple fee codes such as
CF for Camping Fee and NF for NSF checks; may want a separate table that
holds possible codes)
Transaction_Amount (positive for fees, negative for payments)
other information such as check number, notes, etc.

Then you can set up your forms and reports in such a way that they will
not need modification each camp session. For forms you would have a main
form for entering campers. On the form you would have a ledger subform for
entering the fees and payments. Similarily on your report, the main report
would have the person's main information and a subreport to display the
ledger transactions and balances.

If you are using Access 2003 or earlier search for "designing a
database", including the quotes, in Access's online help. If Access 2007, go
to help an choose the "Database Design" item. Also do a search for "subform"
and "subreport, quotes not needed. <http://www.mvps.org/access/> is a good
site for resources to help learn about Access. Also,
<http://www.accessmvp.com/JConrad/accessjunkie/resources.html> looks to be a
good Access database design resource.

Hope that helps,

Clifford Bass

I am using a table as the source, not a Query.

Basically, we run youth camps, and several campers have outstanding balances
from the last three camps. I want to do a statement per individual showing
their balance from the last three camps.

The following are fields I have in the table.

Last Name
First Name

WTR 08 Fee
WTR 08 Payments

SMR 08 Fee
SMR 08 Payments

WTR 09 Fee
WTR 09 Payments

On the statement I want to add a calc field that shows the balance owing for
each of the three camps, then a TOTAL that adds the three balances.

 

[Clifford Bass]

Hi,

Great to hear it is working! You are not getting a final result due to
the nulls. To get a result regardless, use the Nz() function:

=Nz([txtWTR 08 Balance], 0)+Nz([txtSMR 08 Balance], 0)+Nz([txtWTR 09
Balance], 0)

Clifford Bass