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
|
|||
|
|||
Student class payments
Hi,
I building a database for karate classes and payments. I have these tables: tblStudents - for basic info (name, address etc) tblKarateCls - a lookup table for the classes tblEnrollment - a join table with these fields: EnrollmentID - PK, StudentID - Foriegn key - to relate to the student table, KarateClsID - foriegn key to relate to the karate class table, and additional fields that only pertain to that student for that class for that year like StartDate, EndDate, TuitionAmt etc. tblPayment with DtPd, AmtPd, LtFee (if appropriate). I can't figure out how to record the payments. Should I build a form based on tblEnrollment as the parent form and a subform on tblPayments? This would give me a list of payments for each student but then every payment must be entered in a separate form (based on the tblEnrollment). Is there a better way? Thanks. |
#2
|
|||
|
|||
Student class payments
It depends on how and what they pay for. Do they pay per class or do they pay
one bill for all classes? Are partial payments possible? If a student owes $10 for Class A and $10 for Class B and they pay $15, how do you allocate the payments? You need to figure out the 'business rules' before you design your database. Your Payments table will probably need to link to your Enrollment table so you know what payment applies to what class.. -- Dorian "Give someone a fish and they eat for a day; teach someone to fish and they eat for a lifetime". "Nomy" wrote: Hi, I building a database for karate classes and payments. I have these tables: tblStudents - for basic info (name, address etc) tblKarateCls - a lookup table for the classes tblEnrollment - a join table with these fields: EnrollmentID - PK, StudentID - Foriegn key - to relate to the student table, KarateClsID - foriegn key to relate to the karate class table, and additional fields that only pertain to that student for that class for that year like StartDate, EndDate, TuitionAmt etc. tblPayment with DtPd, AmtPd, LtFee (if appropriate). I can't figure out how to record the payments. Should I build a form based on tblEnrollment as the parent form and a subform on tblPayments? This would give me a list of payments for each student but then every payment must be entered in a separate form (based on the tblEnrollment). Is there a better way? Thanks. . |
#3
|
|||
|
|||
Student class payments
I expect there should be a cost field in tblKarateCls. I would create a
payment table that is independent of the enrollments. This would allow for partial payments or payments that would cover the end of one class and the beginning of another. Don't forget the studentId in the payment table. You should only need to compare the total class cost vs the total payments made. -- Duane Hookom Microsoft Access MVP "Nomy" wrote: Hi, I building a database for karate classes and payments. I have these tables: tblStudents - for basic info (name, address etc) tblKarateCls - a lookup table for the classes tblEnrollment - a join table with these fields: EnrollmentID - PK, StudentID - Foriegn key - to relate to the student table, KarateClsID - foriegn key to relate to the karate class table, and additional fields that only pertain to that student for that class for that year like StartDate, EndDate, TuitionAmt etc. tblPayment with DtPd, AmtPd, LtFee (if appropriate). I can't figure out how to record the payments. Should I build a form based on tblEnrollment as the parent form and a subform on tblPayments? This would give me a list of payments for each student but then every payment must be entered in a separate form (based on the tblEnrollment). Is there a better way? Thanks. . |
#4
|
|||
|
|||
Student class payments
Dorian,
The students pay either monthly or quarterly on an annual basis. The tuition cost is in the tblKarateCls table. Sometimes two children in a family are students and the parents will pay one check for both. A payment amount is posted for each child. Yes, partial payments could be made. If $10 is paid for class A and only $5 is paid for class B, then I have to show that the student owes $5 for class B plus a late fee. Thanks. "Dorian" wrote in message ... It depends on how and what they pay for. Do they pay per class or do they pay one bill for all classes? Are partial payments possible? If a student owes $10 for Class A and $10 for Class B and they pay $15, how do you allocate the payments? You need to figure out the 'business rules' before you design your database. Your Payments table will probably need to link to your Enrollment table so you know what payment applies to what class.. -- Dorian "Give someone a fish and they eat for a day; teach someone to fish and they eat for a lifetime". "Nomy" wrote: Hi, I building a database for karate classes and payments. I have these tables: tblStudents - for basic info (name, address etc) tblKarateCls - a lookup table for the classes tblEnrollment - a join table with these fields: EnrollmentID - PK, StudentID - Foriegn key - to relate to the student table, KarateClsID - foriegn key to relate to the karate class table, and additional fields that only pertain to that student for that class for that year like StartDate, EndDate, TuitionAmt etc. tblPayment with DtPd, AmtPd, LtFee (if appropriate). I can't figure out how to record the payments. Should I build a form based on tblEnrollment as the parent form and a subform on tblPayments? This would give me a list of payments for each student but then every payment must be entered in a separate form (based on the tblEnrollment). Is there a better way? Thanks. . |
#5
|
|||
|
|||
Student class payments
Keep in mind in your example that the $5 still owed by the student for Class
B could either be stored (BAD IDEA!) or calculated (GREAT IDEA!). The total of all amounts received on behalf of that student, less the total of all amounts credited on behalf of that student = "amount owed". Regards Jeff Boyce Microsoft Access MVP -- Disclaimer: This author may have received products and services mentioned in this post. Mention and/or description of a product or service herein does not constitute endorsement thereof. Any code or psuedocode included in this post is offered "as is", with no guarantee as to suitability. You can thank the FTC of the USA for making this disclaimer possible/necessary. "Nomy" wrote in message ... Dorian, The students pay either monthly or quarterly on an annual basis. The tuition cost is in the tblKarateCls table. Sometimes two children in a family are students and the parents will pay one check for both. A payment amount is posted for each child. Yes, partial payments could be made. If $10 is paid for class A and only $5 is paid for class B, then I have to show that the student owes $5 for class B plus a late fee. Thanks. "Dorian" wrote in message ... It depends on how and what they pay for. Do they pay per class or do they pay one bill for all classes? Are partial payments possible? If a student owes $10 for Class A and $10 for Class B and they pay $15, how do you allocate the payments? You need to figure out the 'business rules' before you design your database. Your Payments table will probably need to link to your Enrollment table so you know what payment applies to what class.. -- Dorian "Give someone a fish and they eat for a day; teach someone to fish and they eat for a lifetime". "Nomy" wrote: Hi, I building a database for karate classes and payments. I have these tables: tblStudents - for basic info (name, address etc) tblKarateCls - a lookup table for the classes tblEnrollment - a join table with these fields: EnrollmentID - PK, StudentID - Foriegn key - to relate to the student table, KarateClsID - foriegn key to relate to the karate class table, and additional fields that only pertain to that student for that class for that year like StartDate, EndDate, TuitionAmt etc. tblPayment with DtPd, AmtPd, LtFee (if appropriate). I can't figure out how to record the payments. Should I build a form based on tblEnrollment as the parent form and a subform on tblPayments? This would give me a list of payments for each student but then every payment must be entered in a separate form (based on the tblEnrollment). Is there a better way? Thanks. . |
#6
|
|||
|
|||
Student class payments
Jeff,
Would you store the DtDue and AmtDue (the monthly class fee) in the payment table? I added these fields to my payment table: DtDue and AmtDue. Fields now are DtDue, AmtDue, DtPd, AmtPd and LtFee. My objective when I build the forms is to see the term for the class (BeginDt and EndDt), the full tuition, the monthly fee in the Parent form. The first installment is due one month after the BeginDt. Each payment is due on the same day of the month depending on the BeginDt. In the subform I want to show the DtDue and AmtDue (per month) the DtPd and AmtPd. I could then calculate the amounts due and paid with a balance. Am I on the right track. Thank you all so much for the help. "Jeff Boyce" wrote in message ... Keep in mind in your example that the $5 still owed by the student for Class B could either be stored (BAD IDEA!) or calculated (GREAT IDEA!). The total of all amounts received on behalf of that student, less the total of all amounts credited on behalf of that student = "amount owed". Regards Jeff Boyce Microsoft Access MVP -- Disclaimer: This author may have received products and services mentioned in this post. Mention and/or description of a product or service herein does not constitute endorsement thereof. Any code or psuedocode included in this post is offered "as is", with no guarantee as to suitability. You can thank the FTC of the USA for making this disclaimer possible/necessary. "Nomy" wrote in message ... Dorian, The students pay either monthly or quarterly on an annual basis. The tuition cost is in the tblKarateCls table. Sometimes two children in a family are students and the parents will pay one check for both. A payment amount is posted for each child. Yes, partial payments could be made. If $10 is paid for class A and only $5 is paid for class B, then I have to show that the student owes $5 for class B plus a late fee. Thanks. "Dorian" wrote in message ... It depends on how and what they pay for. Do they pay per class or do they pay one bill for all classes? Are partial payments possible? If a student owes $10 for Class A and $10 for Class B and they pay $15, how do you allocate the payments? You need to figure out the 'business rules' before you design your database. Your Payments table will probably need to link to your Enrollment table so you know what payment applies to what class.. -- Dorian "Give someone a fish and they eat for a day; teach someone to fish and they eat for a lifetime". "Nomy" wrote: Hi, I building a database for karate classes and payments. I have these tables: tblStudents - for basic info (name, address etc) tblKarateCls - a lookup table for the classes tblEnrollment - a join table with these fields: EnrollmentID - PK, StudentID - Foriegn key - to relate to the student table, KarateClsID - foriegn key to relate to the karate class table, and additional fields that only pertain to that student for that class for that year like StartDate, EndDate, TuitionAmt etc. tblPayment with DtPd, AmtPd, LtFee (if appropriate). I can't figure out how to record the payments. Should I build a form based on tblEnrollment as the parent form and a subform on tblPayments? This would give me a list of payments for each student but then every payment must be entered in a separate form (based on the tblEnrollment). Is there a better way? Thanks. . |
#7
|
|||
|
|||
Student class payments
By adding in fields specific to one type of transaction (i.e., dues/fees),
you're starting down a slippery slope. What happens when you come up with another type of fee? And the one after that? Adding another column or set of columns is what you'd do in a spreadsheet, but Access is a relational database. If "relational" and "normalization" are unfamiliar, plan on brushing up! If your transaction table has a field for TransactionAmount and a field for TransactionDate, what about the idea of adding one more field for [TransactionType]. In that field, you'd store the foreign key that points back to the row in your (new) [tlkpTransactionType] lookup table that lists each type of transaction. With a design like this, when you add a new fee (i.e., type of transaction), you just add a row to that lookup table. That's it! Make sense? Regards Jeff Boyce Microsoft Access MVP -- Disclaimer: This author may have received products and services mentioned in this post. Mention and/or description of a product or service herein does not constitute endorsement thereof. Any code or psuedocode included in this post is offered "as is", with no guarantee as to suitability. You can thank the FTC of the USA for making this disclaimer possible/necessary. "Nomy" wrote in message ... Jeff, Would you store the DtDue and AmtDue (the monthly class fee) in the payment table? I added these fields to my payment table: DtDue and AmtDue. Fields now are DtDue, AmtDue, DtPd, AmtPd and LtFee. My objective when I build the forms is to see the term for the class (BeginDt and EndDt), the full tuition, the monthly fee in the Parent form. The first installment is due one month after the BeginDt. Each payment is due on the same day of the month depending on the BeginDt. In the subform I want to show the DtDue and AmtDue (per month) the DtPd and AmtPd. I could then calculate the amounts due and paid with a balance. Am I on the right track. Thank you all so much for the help. "Jeff Boyce" wrote in message ... Keep in mind in your example that the $5 still owed by the student for Class B could either be stored (BAD IDEA!) or calculated (GREAT IDEA!). The total of all amounts received on behalf of that student, less the total of all amounts credited on behalf of that student = "amount owed". Regards Jeff Boyce Microsoft Access MVP -- Disclaimer: This author may have received products and services mentioned in this post. Mention and/or description of a product or service herein does not constitute endorsement thereof. Any code or psuedocode included in this post is offered "as is", with no guarantee as to suitability. You can thank the FTC of the USA for making this disclaimer possible/necessary. "Nomy" wrote in message ... Dorian, The students pay either monthly or quarterly on an annual basis. The tuition cost is in the tblKarateCls table. Sometimes two children in a family are students and the parents will pay one check for both. A payment amount is posted for each child. Yes, partial payments could be made. If $10 is paid for class A and only $5 is paid for class B, then I have to show that the student owes $5 for class B plus a late fee. Thanks. "Dorian" wrote in message ... It depends on how and what they pay for. Do they pay per class or do they pay one bill for all classes? Are partial payments possible? If a student owes $10 for Class A and $10 for Class B and they pay $15, how do you allocate the payments? You need to figure out the 'business rules' before you design your database. Your Payments table will probably need to link to your Enrollment table so you know what payment applies to what class.. -- Dorian "Give someone a fish and they eat for a day; teach someone to fish and they eat for a lifetime". "Nomy" wrote: Hi, I building a database for karate classes and payments. I have these tables: tblStudents - for basic info (name, address etc) tblKarateCls - a lookup table for the classes tblEnrollment - a join table with these fields: EnrollmentID - PK, StudentID - Foriegn key - to relate to the student table, KarateClsID - foriegn key to relate to the karate class table, and additional fields that only pertain to that student for that class for that year like StartDate, EndDate, TuitionAmt etc. tblPayment with DtPd, AmtPd, LtFee (if appropriate). I can't figure out how to record the payments. Should I build a form based on tblEnrollment as the parent form and a subform on tblPayments? This would give me a list of payments for each student but then every payment must be entered in a separate form (based on the tblEnrollment). Is there a better way? Thanks. . |
Thread Tools | |
Display Modes | |
|
|