Sum Subform

  • Thread starter Thread starter Sherry
  • Start date Start date
S

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
 
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
 
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
 
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!
 
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
Sherry said:
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
Click to expand...


.
Click to expand...
 
Back
Top