Adding fields

[alosbanos]

Hello,

I am creating a GED database. I have the following fields....

Pretest_TABE_Date
PreTest_Payment
Module_Payment
GED_Credential
Total_Payment_Amount

GED_Writing_Score1
GED_Writing_Score2
GED_Writing_Score3

GED_SocialStudies_Score1
GED_SocialStudies_Score2
GED_SocialStudies_Score3

GED_Science_Score1
GED_Science_Score2
GED_Science_Score3

GED_Reading_Score1
GED_Reading_Score2
GED_Reading_Score3

GED_Math_Score1
GED_Math_Score2
GED_Math_Score3

If I can get any sort of guidance to do the following that would just make my day:
1. If a date in Pretest_TABE_Date is added then $285 shows up automatically in the PreTest_Payment.

2. If a student scores a 410 and above in any of the fields for GED_Writing, GED_SocialStudies, GED_Science, GED_Reading, and GED_Math then the field Module_Payment automatically gets $275 times whichever fields the student received a 410+ in.
IE: John Doe Received the following scores
Writing: 410
Social Studies: 412
Science: 410


Reading: 420
Math: 410
Module_Payment will update to $1,375.

3. If a student scores a 410 and above in all of the GED subjects then that means he/she passes the GED is there a way to make GED_Credential update to $90?

4. I would now just like to add PreTest_Payment + Module_Payment +
GED_Credential = Total_Payment_Amount


If I can get help on any of these that would be great.

Thank you in advance for any help.

[alansidman]

I know this is not part of your question, but looking at your table set up, you do not have a normalized database. You should not have repeating fields holding the same type of information. Scores. You should create another table and it should look something like this.

tblScores
ScoreID (PrimaryKey)
StudentID (Foreign Key)
ScoreDate
TestType
TestScore

You should also have a student table with vital information about the student, ie. Unique ID, Name, Address etc. You will probably need other tables concerned with payments, etc.

You need to make sure that your schema is in place before you begin calculations. Having a proper schema will allow you to make calculations easily. This is critical.

Here is a link to database design.

http://www.deeptraining.com/litwin/d...aseDesign.aspx

Also, click on the "Relational Database Principles" below