Advice Needed for Newbie

[dee]

Hi,

I need to create a database for a small school. Objectives are basically:

Family information

For each student:
Course(s)
Registration date
Registration fee
One time tuition fee
Discontinuation date
Vacation date

Courses:
there are only 4 possibilities
Rate - usually $100 per month

Payment information:
Payment mode (cash, cheque)
Payment date
Is it split between dates or courses or children (could be all three)
Deposit Number

I've so far created these tables:
FamilyInfo:
FamilyID
FamilyName
ParentName
Address, phone, etc. details

Students:
Student ID, Last, First

CourseInfo:
CourseID
CourseName
CourseCode (abbreviation of name)
MonthlyRate
SpecialRate

DepositInformation:
DepositID
DepositNumber
DepositDate

PaymentInformation:
PaymentID
Mode
Date
Amount

I've created a form to enter and view data:
FamilyInfo
Students

Now I need to start creating other forms to view and enter data, but am not
sure the best way of going about this.

Any advice or does anyone know of a site that has examples of this type of
Access application. I've looked at the Northwinds example, but it doesn't
really apply to what I'm doing.

THANKS in advance for any help given.

 

[John Vinson]

Hi,

I need to create a database for a small school. Objectives are basically:

I've so far created these tables:
FamilyInfo:
FamilyID
FamilyName
ParentName
Address, phone, etc. details

Students:
Student ID, Last, First

You also need a FamilyID as a link to the family of which this student
is part. This is called a "Foreign Key", and is how Access or any
relational database links information in different tables together.

CourseInfo:
CourseID
CourseName
CourseCode (abbreviation of name)
MonthlyRate
SpecialRate

So this table would have only four records, if there are only four
courses; that's fine.

DepositInformation:
DepositID
DepositNumber
DepositDate

This table should also have a StudentID field as a foreign key to
Students, to indicate which student is credited with this particular
deposit.

PaymentInformation:
PaymentID
Mode
Date
Amount

Similarly here. But might not a Deposit be considered a special case
of a Payment? Isn't it just an initial payment? Maybe you can combine
these two tables.

You also need one more table: Enrollment, which describes the many to
many relationship between Students and Courses:

Enrollment:
StudentID <<< who enrolled
CourseID <<< into which course
<any other info about this student in this class, e.g. enrollment
date, completion date, grade, etc.>

I've created a form to enter and view data:
FamilyInfo
Students

Now I need to start creating other forms to view and enter data, but am not
sure the best way of going about this.

Forms and Subforms; for instance you could have a Subform for
Enrollment and another Subform for Payments on the Student form.

Any advice or does anyone know of a site that has examples of this type of
Access application. I've looked at the Northwinds example, but it doesn't
really apply to what I'm doing.

It actually does, at a more abstract level. Think of Students as
Customers, Courses as Products, and Enrollment as Orders and you'll
see the parallels! Just as one Customer can buy multiple Products, and
each Product can be bought by many Customers, so each Student can
enrol in more than one Course, and each Course will have more than one
Student.

Good luck - post back if this isn't making sense!

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[dee]

Thank you so much for your response and help.

The payment information is actually very key. A student does not make an
initial payment. They actually make many payments. The charge is per month
per student per course.

One family may have two children; one child may be taking two courses; the
other one. They may pre-pay by cheque, for example, to be applied to Oct.
Nov and Dec.

Or, they may pay cash or cheque for only one child, for example. It is
sometimes also a late payment to be applied partially to, say, November, but
also for a payment missed in August. Kind of complicated.

The deposit number should somehow link to the student, the course and the
month, plus of course the amount.

Does this make sense?

Thanks again!
Dee

 

[John Vinson]

Thank you so much for your response and help.

The payment information is actually very key. A student does not make an
initial payment. They actually make many payments. The charge is per month
per student per course.

OK - I misunderstood the meaning of your suggested field "Depostit
Number". Would the Deposit Number simply identify each payment?

One family may have two children; one child may be taking two courses; the
other one. They may pre-pay by cheque, for example, to be applied to Oct.
Nov and Dec.

Or, they may pay cash or cheque for only one child, for example. It is
sometimes also a late payment to be applied partially to, say, November, but
also for a payment missed in August. Kind of complicated.

The deposit number should somehow link to the student, the course and the
month, plus of course the amount.

I guess I don't understand what you mean by "a Deposit". Is this a
bank deposit statement which might cover many different students? How
does a Deposit relate to a Payment?

If it's a many to many relationship from charges to payments, you may
need another table:

PaymentAllocation
PaymentID ' which payment
CourseID ' which course is this payment for
StudentID ' on whose behalf; 3-field joint Primary Key
Amount

Thus if there were a single cheque covering two students for three
courses, this table would have a record for each portion, allocating
it to the appropriate charge.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[dee]

Hi again,

The deposit is indeed a bank deposit, with a bank deposit number that
identifies it, in addition to the same bank deposit number being "linked" to
the student, the course, the date (month) it applies to.

A family may have three children and pay for 1 course for one child and two
courses for another and it may be for different months (the courses are on a
payment per-month basis. What about if the payment is in cash towards the
coming months?

I originally thought Excel for this, but realized that even with advanced
filtering, it would be very difficult to manipulate this information, in
addition to making sure the integrity of the data already entered, such as
payment information, student information, was maintained without all kinds of
field protection and locking/unlocking each time payments were made. Plus,
I'd like to run reports to see how many children are entrolled in a course,
etc. Do you agree that Access is the way to go?

I hope this clarifies things - sorry if I wasn't clear enough - you are so
kind to help.

Looking forward to your response.
Dee