Grade keeping for report card.

[MillieWarwick]

I am a new user to Access. I am building a data base for an adult Bible
School. Classes are twice a week for 5 weeks with the 6th week being test.
They can earn 10 bonus points to be added to final grade. If there is a book
to read, and they do not read it, then 10 points is deducted from their final
grade. All five worksheets for each class are to be totaled . The test is to
be 75% of the final grade and the worksheet total is the other 25% of the
final grade. Then final grade plus bonus points, plus (minus 10 points) if
book not read equals class grade.
I have had no success so far in makeing this work on the tabel or form. I
have a table for students with all of their information;for teachers with all
of their information; for classes with all 26 classes listed; for assignments
with all five WorkSheets(numbered 1-5), WorkSheetTotal, Exam, FinalGrade,
BonusPoints, ReadBook (yes/no), ClassGrade.
The table relationships are all linked 1-many with enforced referrential
integrity. I have tried making the fields Numbers, Percents, and Currency.
The formula I have been trying to use so far is just to get the 5 worksheets
to add up. =[WorkSheet1]+[WorkSheet2]+[WorkSheet3]+[WorkSheet4]+[WorkSheet5]
It keeps coming back and saying it does not recognize WorkSheet1 ...
I have completely deleted the table and rebuilt it several times. I have
started over from scratch with a new database with a new name and rebuilt all
of the tables several times. I almost know all of the information by heart,
which is good, since I am the director of the school. So I can see that some
good has come out of all of this confusion.
Is there a VBA for dummies? I would really like to have this working by the
second week of September when school starts if possible.
Thanks for your help
MillieWarwick

 

[jmonty]

First Try this:
Create your table and fields Worksheet1,....5. Make the fields
Numbers/Numeric and then hit F6 and change them from Long Integer to Double.
A double can have multiple decimal places - an Integer cannot. This will make
things easier later when you go to do percentages.
Good database design tip: Never save a calculated number in a table field.
Always calculate it when needed. Use unbound textboxes on forms and reports
to caculate the fields at run-time. That way any changes to the scores will
be automatically updated in the calculated field.
OK back to what we were doing:
Next create a form. Use the form wizard if you want. Go into design view.
Right click on each textbox and select properties. Make sure that the
ControlSource property on the Data tab is linked to the corresponding field.
Next select the ALL tab. change the Name property from Worksheet1 to
txtWorksheet1. Do this for each of the text boxes. This will help distinguish
the form's field (textbox) name from the table's field (or ControlSource)
name. Next add an unbound textbox to the form by using the toolbox. Change
the Name property to txtSum. Add the formula to the ControlSource property
on the Data tab of this unbound textbox:

=Nz([txtWorkSheet1] ,0) + Nz([txtWorkSheet2],0) + Nz([txtWorkSheet3],0) +
Nz([txtWorkSheet4],0) + Nz([txtWorkSheet5],0)

The use of the brackets helps Access identify them as form fields.
I think what the problem may have been is that there is probably no data in
your table yet. Which means all the values were null. When the field is
numeric and it appears blank to you in datasheet view, it is actually equal
to a NULL value. Access cannot add a null value to a numeric value - the
result is NULL. We get around this problem by using the Nz function.
The Nz function basically says: If the value is null, replace it with this
value. (0).
It may help to change the default value in the table design for these fields
to 0 if it is not already.

Use Access help to research the following functions: Nz, IsNull, IIF

In addition: I would go out to the MS Office Home page and then to
Templates. I believe there are a couple of very good Access databases and
Excel Spreadsheets already created for recording grades that you can download
and use.

Good Luck!

jmonty

I am a new user to Access. I am building a data base for an adult Bible
School. Classes are twice a week for 5 weeks with the 6th week being test.
They can earn 10 bonus points to be added to final grade. If there is a book
to read, and they do not read it, then 10 points is deducted from their final
grade. All five worksheets for each class are to be totaled . The test is to
be 75% of the final grade and the worksheet total is the other 25% of the
final grade. Then final grade plus bonus points, plus (minus 10 points) if
book not read equals class grade.
I have had no success so far in makeing this work on the tabel or form. I
have a table for students with all of their information;for teachers with all
of their information; for classes with all 26 classes listed; for assignments
with all five WorkSheets(numbered 1-5), WorkSheetTotal, Exam, FinalGrade,
BonusPoints, ReadBook (yes/no), ClassGrade.
The table relationships are all linked 1-many with enforced referrential
integrity. I have tried making the fields Numbers, Percents, and Currency.
The formula I have been trying to use so far is just to get the 5 worksheets
to add up. =[WorkSheet1]+[WorkSheet2]+[WorkSheet3]+[WorkSheet4]+[WorkSheet5]
It keeps coming back and saying it does not recognize WorkSheet1 ...
I have completely deleted the table and rebuilt it several times. I have
started over from scratch with a new database with a new name and rebuilt all
of the tables several times. I almost know all of the information by heart,
which is good, since I am the director of the school. So I can see that some
good has come out of all of this confusion.
Is there a VBA for dummies? I would really like to have this working by the
second week of September when school starts if possible.
Thanks for your help
MillieWarwick

 

[MillieWarwick via AccessMonster.com]

Thank you so much for your quick response. I will try this procedure. But, I
have had an urgent situation come up and may not be able to work on the data
base for several days. I will let you know what the outcome is when I do get
to try it.
Thank you again for your help.
MillieWarwick
----------------------------------------------------------

First Try this:
Create your table and fields Worksheet1,....5. Make the fields
Numbers/Numeric and then hit F6 and change them from Long Integer to Double.
A double can have multiple decimal places - an Integer cannot. This will make
things easier later when you go to do percentages.
Good database design tip: Never save a calculated number in a table field.
Always calculate it when needed. Use unbound textboxes on forms and reports
to caculate the fields at run-time. That way any changes to the scores will
be automatically updated in the calculated field.
OK back to what we were doing:
Next create a form. Use the form wizard if you want. Go into design view.
Right click on each textbox and select properties. Make sure that the
ControlSource property on the Data tab is linked to the corresponding field.
Next select the ALL tab. change the Name property from Worksheet1 to
txtWorksheet1. Do this for each of the text boxes. This will help distinguish
the form's field (textbox) name from the table's field (or ControlSource)
name. Next add an unbound textbox to the form by using the toolbox. Change
the Name property to txtSum. Add the formula to the ControlSource property
on the Data tab of this unbound textbox:

=Nz([txtWorkSheet1] ,0) + Nz([txtWorkSheet2],0) + Nz([txtWorkSheet3],0) +
Nz([txtWorkSheet4],0) + Nz([txtWorkSheet5],0)

The use of the brackets helps Access identify them as form fields.
I think what the problem may have been is that there is probably no data in
your table yet. Which means all the values were null. When the field is
numeric and it appears blank to you in datasheet view, it is actually equal
to a NULL value. Access cannot add a null value to a numeric value - the
result is NULL. We get around this problem by using the Nz function.
The Nz function basically says: If the value is null, replace it with this
value. (0).
It may help to change the default value in the table design for these fields
to 0 if it is not already.

Use Access help to research the following functions: Nz, IsNull, IIF

In addition: I would go out to the MS Office Home page and then to
Templates. I believe there are a couple of very good Access databases and
Excel Spreadsheets already created for recording grades that you can download
and use.

Good Luck!

jmonty

I am a new user to Access. I am building a data base for an adult Bible
School. Classes are twice a week for 5 weeks with the 6th week being test.

[quoted text clipped - 23 lines]

Thanks for your help
MillieWarwick