Re: Cumulative cost

I have a master table that contains detailed information in each student;
including class start date (MM/YY). How would I query classes cost for this
student over a period of time (each class cost a different amount) for
instance I can insert the start date into the grid and type between 01/01/08
and 03/18/08 and that would give me the number of classes that this student
has attended. How would I tie the cumulative cost into that?
--
Ctellez


"Jeff Boyce" wrote:

It is absolutely possible to calculate the total education cost for each
student, PROVIDED that you are capturing enough raw data. Moreover, it is
relatively easy to do in Access IF you are storing the data in a
well-normalized database structure. It all starts with the data.

Without a better idea of which pieces of data you are storing and in what
structure (tables, fields), it's hard to be very specific.

You started describing John Doe, Welding, 304.30 ... but nothing in that
gives you a way to determine by month or by year.

More info, please.

Regards

Jeff Boyce
Microsoft Office/Access MVP

"Ctellez" <dogbit@xxxxxxxxx> wrote in message
news:252D3DAE-F4FA-4AB1-A5DD-684A5C61DEB7@xxxxxxxxxxxxxxxx
That is how I feel rite now too. Is it possible to count the number of
classes taken by one student and the do the calculation by the cost of
each
class for a running total? I am trying this and it's not working either

RunSum: Format(DSum("[Program Cost]","Program","WBE Program","[Id]<=" &
[Student Id]),"Currency")

say this student takes three classes all costing $50.00 each over a three
month period, would it be easier to do the calculation by the number of
classes taken?
I guess I was trying to get the query to count the classes (text) and then
calculate the fee into a running total. Should I add another table as a
class
count and try that?

The goal I am seeking is to be able to identify each student's education
cost as a running total.

Is it even possible to do that?

Thank you

--
Ctellez


"Jeff Boyce" wrote:

I guess I don't understand what you are trying to do.

I don't know how you would "multiply" and "add" text values and currency
values.

Regards

Jeff Boyce
Microsoft Office/Access MVP



"Ctellez" <dogbit@xxxxxxxxx> wrote in message
news:35FF2EAC-BC64-4DE3-B79B-BFECF25D65FD@xxxxxxxxxxxxxxxx
Data types are:
Program Cost=Currency /WBE Program=Text / Transition Program=Text


--
Ctellez


"Jeff Boyce" wrote:

The "Type Mismatch..." error message usually means one or more of the
fields
you are using is of a different datatype. What are the data types of
those
three fields?

Regards

Jeff Boyce
Microsoft Office/Access MVP

"Ctellez" <dogbit@xxxxxxxxx> wrote in message
news:147210E3-F974-4FD6-B5A8-C1B8D14C5177@xxxxxxxxxxxxxxxx
I did that and it works for one class. I need to calculate John does
classes
by month and annually for the education cost basis i.e. cumulative
value.

So if John Doe has one class each month and the cost varies for each
class
each month, He will appear as a new entry each month (therefore; new
ID
number) I need to capture the total cost of his education year to
date.
I
have tried the following but it is not working:
Expr1: Sum ([Program Cost])*([WBE Program])+([Transition Program])

Error is "Type mismatch expression"

I have designed the query to require the student number be entered
for
his
YTD total.

Not sure what I am missing or doing wrong.

--
Ctellez


"Jeff Boyce" wrote:

Open a new query in design view.

Add the table that holds the data you described.

Add the field that holds "John Doe" and the field that holds the
Amount.

Click on the button that looks like a backwards 3 (the "sigma" or
"Totals"
button).

The [FullName] field will show "GroupBy" beneath in in the grid.
So
will
the [Amount] field. Change it to "Sum" for the [Amount] field.

Run it.

Regards

Jeff Boyce
Microsoft Office/Access MVP

"Ctellez" <dogbit@xxxxxxxxx> wrote in message
news:ED596E62-A9DA-475F-A775-EBF56458E844@xxxxxxxxxxxxxxxx
I am having trouble getting cumulative cost in an education
database
I
am
working on. There are two columns for the programs one column for
the
cost
and the fourth column is the year to date total. HELP!! I need
my
query
to
calculate the year to date by student and the program(s) +
Program
Cost
they
are taking for annual reporting purposes.
I have been doing the calculations in excel and the importing the
data,
(not
what I really want to do) How do I Make the Programs (data) and
the
cost
(data) to calculate the students YTD cost basis in a query?

Example:

John Doe Life Skills 258.00
John Doe Welding 304.30

The query needs to take the cost for both programs and combine
them
to
the
cumulative total.

Any Ideas?

--
Ctellez












.

Loading