Calculated Text Box

[MB]

I have a program to track courses taken by our employees. The main form
includes employee name, position, employee number, etc. A subform (called
tblCoursesSubform2) consists of the name of the course and associate costs
(cost, travel, motel, meals, misc).

There is a text box (TotalAllCosts) with the following control source:
=Nz([Cost],0)+Nz([TravelCost],0)+Nz([MotelCost],0)+Nz([MealCost],0)+Nz([MiscCost],0)

I need to have another text box that will add the TotalAllCosts amount to
the last record added.

In other words, the last record has $1,200 in the TotalAllCosts box. When I
add a new record where the TotalAllCosts for that record is, say, $1,000, I
need a box (called TotalToDate) that will show $2,200 (total spent for that
employee to date).

I tried to work with the expression builder, but I am lost. Please help.

Thank you for your time and effort.

 

[Al Campagna]

MB,
Your Cost subform table design needs some rework. When you use
discrete/separate fields for each cost entry, you complicate any
calculations against those values. A better table design would be...
EmpID TypeOfCost Cost
123 Travel 100.00
123 Meal 20.00
123 Motel 84.00
123 Tolls/Park 6.50
etc....
TypeOfCost would be a combo or list box with all possible types of cost
listed.

Now, the cost of a particular trip (in the subform footer) would be
= Sum(Cost) ---- $204.00

Regarding "overall" costs for an employee, one solution (and there are
several ways to do it).
Use an unbound calculated field on either the main form or subform to do a
DSum of all costs against that particular EmpID.

 

[MB]

Wonderful! Makes so much sense. I'll do it. Thank you!
--
MB

MB,
Your Cost subform table design needs some rework. When you use
discrete/separate fields for each cost entry, you complicate any
calculations against those values. A better table design would be...
EmpID TypeOfCost Cost
123 Travel 100.00
123 Meal 20.00
123 Motel 84.00
123 Tolls/Park 6.50
etc....
TypeOfCost would be a combo or list box with all possible types of cost
listed.

Now, the cost of a particular trip (in the subform footer) would be
= Sum(Cost) ---- $204.00

Regarding "overall" costs for an employee, one solution (and there are
several ways to do it).
Use an unbound calculated field on either the main form or subform to do a
DSum of all costs against that particular EmpID.

I have a program to track courses taken by our employees. The main form
includes employee name, position, employee number, etc. A subform (called
tblCoursesSubform2) consists of the name of the course and associate costs
(cost, travel, motel, meals, misc).

There is a text box (TotalAllCosts) with the following control source:
=Nz([Cost],0)+Nz([TravelCost],0)+Nz([MotelCost],0)+Nz([MealCost],0)+Nz([MiscCost],0)

I need to have another text box that will add the TotalAllCosts amount to
the last record added.

In other words, the last record has $1,200 in the TotalAllCosts box. When
I
add a new record where the TotalAllCosts for that record is, say, $1,000,
I
need a box (called TotalToDate) that will show $2,200 (total spent for
that
employee to date).

I tried to work with the expression builder, but I am lost. Please help.

Thank you for your time and effort.