Hi JK,
Sorry, its still not right. If a student can take more that 1 course and
pays for them seperately you'll need to change again. Just ensure that you
backup and backup as you normalize your db so that you can refer back if it
does go like you expect and so that you can check that you have matched up
the data
Your Course 1, Course 2 fields should not exist. (If your college ever
offered 3 courses, you would have to change your entire database and all
your forms reports and queries)
Instead, you should have
StudentInfo
StuID, FirstName, LastName etc stuff only to do with the student and nothing
about the courses
TblCourse
CourseID
CourseName
CourseStartDate
Otherstuff all about the course and nothing to do with the student
So if ClassDate is something is the same for all students then it would go
here. If individual students have different dates then the StudentCourses
table would have it.
TblPaymethod
MethID
PayMethod (if you want to enter all students and courses into TblPayment
even before they have paid, then make the first entry in this None and use
that ID number as the default value for MethID in TblPayment)
for the next bit there are 2 options You can combine TblStudentCourses and
TblPayment into one Table but ONLY if students only ever make one payment
for each course. If they ever make part-payements (or are *ever* likely to
do so in the future) you will need to record the details of each
part-payment eg the date it was paid, how much was paid, how it was paid.
TblStudentCourses
SCID (Primary Key)
StuID
CourseID
Details to do with that student and that course - perhaps when he signed up
for it
TotalCost (the total cost of *that course* for *that student*- if there is a
standard cost for the course for all students, then this field would go in
TblCourse.
TblPayment
PaymentID PK
(this is connected to the student paying for one of the courses he is taking
so)
SCID (a combo box to feed this field could concatenate StudentName and
Course and would be based on a query based on TblStudentCourse which had the
text fields from StudentInfo and TblCourse )
PayDate
PayAmount
MethID (so you can choose which method of payment he is using -
ChkNo (check number if he is paying by check
Other fields concernin the student's payments for that course.
Using this latter structure, you can record if and when students made a part
payment for the course.
In forms, queries and reports you can calculate both how much the student
has paid for each course, how much he is owing for each course and how much
he owes altogether.
(I don't know whether MethodOfStudy is dictated by the Course - in which
case it goes in TblCourse, or if it is the choice of the student, in which
case it goes in StudentCourses)
CompletionInf
If this has the details of the student completing the course then it will
have
CompID
SCID
CompletionDate
any info which refers to that student completing that course.
You won't be able to (nor will you need to) enforce Referential Integrity
until all the records in the Foreign Key field (eg StuID in
TblStudentCourse) have been filled in with a StuID number that is in the
TblStudent. Referential Integrity means that you won't be allowed to finish
entering a new record into eg TblStudentCourse until you have chosen a StuID
from StudentInfo and a CourseID from TblCourse
You won't want to enforce Ref Integ until everything has been sorted out.
For all your Foreign Key fields, ensure that they are Number and Long
Integers and delete the Default Value of 0.
In some cases, you may want a Default Value to correspond to a 'dummy
record' in the 'one' table (one tables above are StudentInfo, TblCourse,
TblPaymethod)
So, for example, you may want to append all students and their courses to
TblPayment using an Append query to append all the SCIDs and then fill in
their payments when they make them, If you do this, you would want a Dummy
record for MethID (N/A or None) and you would have that MethID as the
Default Value for MethID in TblPayment
You may want to check what you have entered since you will only see a load
of ID numbers instead of real data.
To do this, if you were using the structure above, you would start of with
TblStudentCourse. You would put create a form based on TblStudentCourse
alone.
You would have a combo based on a query based only on TblStudent and bound
to StuID. You can concatenate eg FirstName and Surname in the query. Another
combo would be based on TblCourse and be bound to CourseID.
Or
You could have a main table based on TblStudent and the Subform based on
TblStudentCourse and having a combo box based on TblCourse
For the payments, you would have a main form based on TblStudent, a First
subform based on TblStudentCourse and a second subform based on tblPayment
and linked to the First Subform by SCID.
Evi
