Re: Cumulative cost

If this were mine, I'd probably not try to do it all in one query, but
create queries that can grab each piece I needed, then figure out how to
hook them together.

Regards

Jeff Boyce
Microsoft Office/Access MVP

"Ctellez" <dogbit@xxxxxxxxx> wrote in message
news:845F1D31-05ED-42A8-B5C3-D8203C510340@xxxxxxxxxxxxxxxx
Yes, but when it ran all that appeared was the heading's; no student data.
Any other ideas?
I have tried the following as well using the last name as a count. I
changed
the Table for CountOfLast Name to a number (so it would not be a text
field)
still does not work.

Student No(Group By) / CountOfLast Name (Count) / LName (Group By) /
FName
(Group By) / Program (Group By) / WBE Program (Group By) / Program Cost
(Group By) /
Expr1[Last Name]*[Program Cost] (SUM)



[Type Student No]



--
Ctellez


"Jeff Boyce" wrote:

It may be that we're talking about the same thing using different terms.

When you say "running total per student", I believe this means the same
thing as "total per student, through <some date>".

You can use a Totals query to get a "per student" Sum (i.e., Total). I
believe I described doing this in an earlier response. Did you try that?

Regards

Jeff Boyce
Microsoft Office/Access MVP

"Ctellez" <dogbit@xxxxxxxxx> wrote in message
news:ED32B01B-A892-4475-95FF-53B577AF989B@xxxxxxxxxxxxxxxx
To calculate total amount per student for a given date range would be
perfect. In doing the calculation my only goal would be to see the cost
as
a
running total per student, therefore expanding my date range correct?
Sorry if I confused anything, I have stressed out on this thing for
day's
now

Thanks for your time

--
Ctellez


"Jeff Boyce" wrote:

Again, unless we have specific information about the data structure,
we
can
only offer general suggestions.

I can't be sure from your description, but it sounds like you want to
do
two
different things, calculate total amount per student for a given date
range,
AND calculate something you're calling "cumulative cost".

More info, please!

Regards

Jeff Boyce
Microsoft Office/Access MVP

"Ctellez" <dogbit@xxxxxxxxx> wrote in message
news:57544228-93CB-42B6-A6B1-281B32302296@xxxxxxxxxxxxxxxx
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
















.