If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Advice Needed for Newbie
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. -- Thanks! Dee |
#2
|
|||
|
|||
On Fri, 10 Dec 2004 16:01:01 -0800, dee
wrote: Hi, I need to create a database for a small school. Objectives are basically: snip 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 |
#3
|
|||
|
|||
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" wrote: On Fri, 10 Dec 2004 16:01:01 -0800, dee wrote: Hi, I need to create a database for a small school. Objectives are basically: snip 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 |
#4
|
|||
|
|||
On Mon, 13 Dec 2004 08:21:06 -0800, dee
wrote: 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 |
#5
|
|||
|
|||
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 "John Vinson" wrote: On Mon, 13 Dec 2004 08:21:06 -0800, dee wrote: 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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Microsoft Office 2003, Addons, other 16,000 more CDs, [ no dongles, no activations, etc ... needed ! ] | athens.gr. | General Discussions | 1 | September 3rd, 2004 02:43 AM |
Advice needed on computerizing childcare records | Mike Halmarack | New Users | 4 | August 31st, 2004 02:41 PM |
Expanding Data As Needed | MT | General Discussion | 2 | July 1st, 2004 12:52 AM |
Table design question - advice needed | David | Database Design | 3 | June 8th, 2004 02:21 AM |
Advice needed | Andre Ronda | Worksheet Functions | 1 | October 13th, 2003 06:43 PM |