Sum Subform

[Sherry]

Ok, I asked this question before and got a response, but
it didn't work for me. Here is what I'm trying to do:

I have a form and a subform based on two separate
tables. Each record on the subform has multiple
subjects, such as Math, Reading, Science, Etc. Each
record is a student. Each student receives a particular
number of points per subject. I have each subject and
its value set up with checkboxes. In other words, if
Jane Doe completed Math, Math gets a check mark and is
worth 100 points. If she completes Science, the Science
field gets a check mark and is worth 200 points. At the
end of the row of the record, I have a Total field which
calculates the total number of points a student earned.

Now, each record is a per week thing. In other words,
Jane Doe gets check marks and a total) for every subject
she completes in Week 1. Then, as a separate record, she
gets check marks (and a total) for every subject she
completes. This is all set up on the subform and works
great.

The formula I used to get that total was basically like
this:

=IIF(Math=True, 100,0) + IIF(Science=True, 200,0) + IIF
(Geography=True, 250,0)

And so on....

So, if the box is checked, then Math=True and is given
100 points, but if not, it =False and is given none.
Simple enough and this formula counts all checks and
their values and totals. I'm fine there.

Where I'm having the problem is this:
My first table is just of the student's names & IDs, so
my form simply shows the student's first and last name,
then the subform shows each week (with each week as a
separate record) of their points. I am now trying to get
a cummulative total onto the form itself for all the
weekly totals in the subform.

Help!!!! LOL!

Any suggestions will be greatly appreciated!!!!

Sherry
Rome, Georgia

 

[Guest]

Sherry, let me start by saying that I am very far from an expert in access
it can just do so much. This being said and with the experience that I have
with the program I would attempt to generate this in either a cross tab query
or on a report. I do not have the knowledge of how to do this in a form
itself.

Don't know if I helped

Brian

 

[Richard]

I just did something similiar but put the total in the footer section of the
subform
Would that work for you
In the footer put a unbounded text box and set the control source property
to =Sum([Amount])
Amount being your calculated field in the subform containing the point total

I think to put this total on the main student form you will need a control
source pointing to a SQL statement on the main table or perhaps a query

Richard

 

[Guest]

Thanks for your response, Brian. As you can see, I'm no
Access guru, so I just kind of limp my way by and make
things work sometimes without having a clue what I'm
doing! LOL! I have actually done this before in another
form with a subform, but for some reason, my syntax is
incorrect in this second instance, or was, anyway. I
have finally spotted the problem, and added a Sum field
to my form footer. Worked like a charm! But, again,
just wanted to say thanks!

 

[Guest]

Thanks for your response. After posting this request, I
actually did figure out what to do, and you are right, a
control using the SUM function in the footer did the
trick!! Thanks!

-----Original Message-----
I just did something similiar but put the total in the footer section of the
subform
Would that work for you
In the footer put a unbounded text box and set the control source property
to =Sum([Amount])
Amount being your calculated field in the subform containing the point total

I think to put this total on the main student form you will need a control
source pointing to a SQL statement on the main table or perhaps a query

Richard

Ok, I asked this question before and got a response, but
it didn't work for me. Here is what I'm trying to do:

I have a form and a subform based on two separate
tables. Each record on the subform has multiple
subjects, such as Math, Reading, Science, Etc. Each
record is a student. Each student receives a particular
number of points per subject. I have each subject and
its value set up with checkboxes. In other words, if
Jane Doe completed Math, Math gets a check mark and is
worth 100 points. If she completes Science, the Science
field gets a check mark and is worth 200 points. At the
end of the row of the record, I have a Total field which
calculates the total number of points a student earned.

Now, each record is a per week thing. In other words,
Jane Doe gets check marks and a total) for every subject
she completes in Week 1. Then, as a separate record, she
gets check marks (and a total) for every subject she
completes. This is all set up on the subform and works
great.

The formula I used to get that total was basically like
this:

=IIF(Math=True, 100,0) + IIF(Science=True, 200,0) + IIF
(Geography=True, 250,0)

And so on....

So, if the box is checked, then Math=True and is given
100 points, but if not, it =False and is given none.
Simple enough and this formula counts all checks and
their values and totals. I'm fine there.

Where I'm having the problem is this:
My first table is just of the student's names & IDs, so
my form simply shows the student's first and last name,
then the subform shows each week (with each week as a
separate record) of their points. I am now trying to get
a cummulative total onto the form itself for all the
weekly totals in the subform.

Help!!!! LOL!

Any suggestions will be greatly appreciated!!!!

Sherry
Rome, Georgia

.