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.
posted by jeather to computers & internet (2 answers total)
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
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.