Cumulative cost

[Ctellez]

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?

 

[Jeff Boyce]

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]

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

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

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?

 

[Jeff Boyce]

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

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

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

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]

Data types are:
Program Cost=Currency /WBE Program=Text / Transition Program=Text


--
Ctellez

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

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

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

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?

 

[Jeff Boyce]

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

Data types are:
Program Cost=Currency /WBE Program=Text / Transition Program=Text


--
Ctellez

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

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


:

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

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]

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

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

Data types are:
Program Cost=Currency /WBE Program=Text / Transition Program=Text


--
Ctellez

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

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


:

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

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]

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 $250.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?

 

[Jeff Boyce]

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

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

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

Data types are:
Program Cost=Currency /WBE Program=Text / Transition Program=Text


--
Ctellez


:

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

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


:

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

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]

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

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

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

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



Data types are:
Program Cost=Currency /WBE Program=Text / Transition Program=Text


--
Ctellez


:

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

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


:

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

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?

 

[Jeff Boyce]

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

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

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

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


:

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



Data types are:
Program Cost=Currency /WBE Program=Text / Transition Program=Text


--
Ctellez


:

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

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


:

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

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]

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

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

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

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

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


:

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



Data types are:
Program Cost=Currency /WBE Program=Text / Transition Program=Text


--
Ctellez


:

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

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


:

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

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?

 

[Jeff Boyce]

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

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

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

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


:

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

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


:

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



Data types are:
Program Cost=Currency /WBE Program=Text / Transition Program=Text


--
Ctellez


:

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

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


:

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

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]

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

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

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

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

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


:

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

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


:

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



Data types are:
Program Cost=Currency /WBE Program=Text / Transition Program=Text


--
Ctellez


:

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

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


:

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

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?

 

[Jeff Boyce]

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

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

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

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


:

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

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


:

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

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


:

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



Data types are:
Program Cost=Currency /WBE Program=Text / Transition
Program=Text


--
Ctellez


:

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

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


:

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

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]

I'll see if maybe that will work better.

Thanks for your time and ideas.
--
Ctellez

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

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

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

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


:

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

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


:

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

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


:

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



Data types are:
Program Cost=Currency /WBE Program=Text / Transition
Program=Text


--
Ctellez


:

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

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


:

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

I am having trouble getting cumulative cost in an
education
database
I
am