Access database design help
May 5, 2010 9:10 AM Subscribe
I need to create an Access 2007 database to confirm tuition payments for students and I am not sure how to set up the tables. Details within.
There are, at any given time, between 120 and 180 students total, in 5 programs. The programs start in some subset of four starting months (Sept, Oct, Feb, May), from one to three of these. Each program has its own cost, which occasionally changes for the new school year. Several of these programs have kits you need to buy, the total cost of which also changes occasionally. Several of these programs have discounts which some students are eligible for (again, this discount changes occasionally). Every student has an enrollment fee on top of tuition and kit. The enrollment fee has been static, but could conceivably change in the future. Obviously if the fee changes after you enroll, you are charged the initial fee, not the changed fee.
Students pay either by cash or by direct deposit. Some direct deposits are charged 5$ -- so the student tells us they deposited 250, but we only get 245, and we credit them 245. We know which students have these fees only after the first deposit. Each payment can go towards any or all of tuition, kit and enrollment fee.
I can easily set up a very simple database with tables for students, payments, and programs and the appropriate links between them, which I then use to confirm that what the front office said was paid has, in fact, been deposited to the correct accounts and that there are no typos, etc. But what I'd like is to be able to be able to generate reports that show how much is outstanding in tuition (by student or by program), kit payments or enrollment fees. I'd also like to be able to use this to print out receipts when payments are received.
I will eventually get inputs in some set of excel sheets (one to add in new students, one for the list of payments that have been received that month, probably). Ideally I'd like the input to be something
like:
student id, cash/transfer, amount, date, for kit?, for tuition?, for enrollment?
as this would be most convenient for other users.
For various reasons, this really would be best done in Access and not another database program.
This isn't homework, and I don't need someone to create the database for me, I'd just like some clues on how to set it up effectively, especially how to deal with the payments. I wouldn't mind clues on how to deal with total payments due for the reports, though I am pretty sure I know how to do that, once I have the rest of it set up properly.
There are, at any given time, between 120 and 180 students total, in 5 programs. The programs start in some subset of four starting months (Sept, Oct, Feb, May), from one to three of these. Each program has its own cost, which occasionally changes for the new school year. Several of these programs have kits you need to buy, the total cost of which also changes occasionally. Several of these programs have discounts which some students are eligible for (again, this discount changes occasionally). Every student has an enrollment fee on top of tuition and kit. The enrollment fee has been static, but could conceivably change in the future. Obviously if the fee changes after you enroll, you are charged the initial fee, not the changed fee.
Students pay either by cash or by direct deposit. Some direct deposits are charged 5$ -- so the student tells us they deposited 250, but we only get 245, and we credit them 245. We know which students have these fees only after the first deposit. Each payment can go towards any or all of tuition, kit and enrollment fee.
I can easily set up a very simple database with tables for students, payments, and programs and the appropriate links between them, which I then use to confirm that what the front office said was paid has, in fact, been deposited to the correct accounts and that there are no typos, etc. But what I'd like is to be able to be able to generate reports that show how much is outstanding in tuition (by student or by program), kit payments or enrollment fees. I'd also like to be able to use this to print out receipts when payments are received.
I will eventually get inputs in some set of excel sheets (one to add in new students, one for the list of payments that have been received that month, probably). Ideally I'd like the input to be something
like:
student id, cash/transfer, amount, date, for kit?, for tuition?, for enrollment?
as this would be most convenient for other users.
For various reasons, this really would be best done in Access and not another database program.
This isn't homework, and I don't need someone to create the database for me, I'd just like some clues on how to set it up effectively, especially how to deal with the payments. I wouldn't mind clues on how to deal with total payments due for the reports, though I am pretty sure I know how to do that, once I have the rest of it set up properly.
Response by poster: Yes, a student is only ever enrolled for a single course. Each course is a fulltime, 12 month program. No student ever enrolls in a second course.
Would it not make more sense to have the student records have the fee amounts copied in at the time of enrollment? Students pay tuition in anywhere from 4 to 12 steps, so I can't see how total amount due can be easily copied in from the course table which should have the fee structures. (Interest isn't charged.)
I guess I was thinking that courses could have a related table of cost histories so that we could just copy the newest, or the last one before the enrollment date, or something like, so we'd have more historical information.
posted by jeather at 1:51 PM on May 5, 2010
Would it not make more sense to have the student records have the fee amounts copied in at the time of enrollment? Students pay tuition in anywhere from 4 to 12 steps, so I can't see how total amount due can be easily copied in from the course table which should have the fee structures. (Interest isn't charged.)
I guess I was thinking that courses could have a related table of cost histories so that we could just copy the newest, or the last one before the enrollment date, or something like, so we'd have more historical information.
posted by jeather at 1:51 PM on May 5, 2010
« Older Is this place in Philadelphia safe? | Break ups before marriage - does it ever happen? Newer »
This thread is closed to new comments.
I don't see how you can report on which courses have not been paid for, unless students always make it clear which course a payment is for (or only enroll in a single course).
Random tips:
Don't delete records, use a boolean deleted field to hide them.
Don't use floating point types for recording currency amounts! (Either use a built in money type, or stick with recording it in cents).
If you are worried you may not get the design just right (or just for troubleshooting), keep a separate table that records every action people take along with the date and time.
Good luck!
posted by samj at 12:24 PM on May 5, 2010