Dance Classes
in [Tables]
|
Prev: doctors office database
Next: ORGANIZATIONAL CHART
From: Nomy on 20 Feb 2010 13:25 Hi, I posted a couple of weeks ago regarding a simple DB for dance classes and received excellent advice. I�ve made good progress but have another question. I�ve been reading a lot and understand the tables are the most important part and would really appreciate help again. I want to record class payments and also bank deposits. All payments are deposits but not all deposits are class payments. Some deposits are for other various items and I can�t figure out how to relate the deposits to the class payments. I normally hand write deposit slips then I have to post checks for class payments again. Some payments are for more than one student so I�ll have to allocate to the correct accounts. The class payments are currently done in Excel and I'd really like to streamline this process. Tables I have so far: (I�ve not built any forms yet) FamilyData: Or the person responsible for class payment. FamilyID as PK. with additional family mailing info etc. Students: StudentID as PK FamilyID as FK related to the family table JoinDate - (the original date they became a customer) Other student info. Other info for only this student Dance class lookup table with DanceClassID as PK. Class Enrollment: Info for the class sign-up and class expiration. EnrollID - PK StudentID - PK - related to the student table DanceClassID - PK - related to a class lookup table StartDate ExpDate - some students enroll for 3 months, some for a year Tuition NumberOfPayments - (Pmt Term - some pay for the year and some pay each month) AllocClassPmts: Allocating the payments AllocClassPmtID EnrollID - related to the Enrollment table StudentID - related to the StudentID in the class enrollment table Amount MethodOfPmt - (Ck, cash or charge) PmtDate Deposits: DepositID DepDate DepAmt DepType � (Cash or check) Sorry for the long post � but to recap, do my tables look correct and if so, how do I relate the deposit table to payments? Thank you in advanch so much. Nancy
From: Steve on 21 Feb 2010 11:36 Hi Nancy, I think you need some revisions to your tables. Observations: 1. TblDanceClass should define the start and end dates, tuition per class, instructor if different for any classes and the type of dance if different for any classes. Tuition per class is needed because some students enroll for 3 months and some enroll for a year. 2. TblClassEnrollment should record the students in a specific class in TblDanceClass. StartDate is not needed because it is defined in TblDanceClass. ExpDate would be better as NumClassesEnrolled because in the end that is what you need to calculate for payments. Tuition is not needed because it is defined in TblDanceClass. NumberOfPayments is not needed because it is defined by NumClassesEnrolled . 3, Regarding payments and deposits ..... You receive a check, cash or credit card from a FamilyID so the payment is a single record in a payment table, The payment needs to be allocated so you also need a payment details table. The same thing about deposits ... you make a deposit by check or cash or both and then then you need a deposit details table to show where the deposit came from. I suggest you make a deposit of class payments a separate transaction of a deposit for other things to simplify your accounting. That all sais, I suggest the following tables: TblClassPayment ClassPaymentID FamilyID PaymentDate PaymentMethodID From TblPaymentMethof CheckNum CCNum TblClassPaymentDetail ClassPaymentDetaiID ClassPaymentID EnrollmentID ClassPaymentAmount TblDeposit DepositID DepositDate DepositType Check or Cash DepositCheckNum TblDepositDetail DepositDetailID DepositID ClassPaymentDetaiID DepositAmount Nancy, study the above and mull it over. If you have any comments or questions, post back and I will try to help. Steve santus(a)penn.com "Nomy" <noname(a)nonsense.invalid> wrote in message news:eUrJdolsKHA.5936(a)TK2MSFTNGP04.phx.gbl... > Hi, > I posted a couple of weeks ago regarding a simple DB for dance classes and > received excellent advice. I've made good progress but have another > question. I've been reading a lot and understand the tables are the most > important part and would really appreciate help again. > > I want to record class payments and also bank deposits. All payments are > deposits but not all deposits are class payments. Some deposits are for > other various items and I can't figure out how to relate the deposits to > the > class payments. > > I normally hand write deposit slips then I have to post checks for class > payments again. > Some payments are for more than one student so I'll have to allocate to > the > correct accounts. The class payments are currently done in Excel and I'd > really like to streamline this process. > > Tables I have so far: (I've not built any forms yet) > > FamilyData: Or the person responsible for class payment. > FamilyID as PK. > with additional family mailing info etc. > > Students: > StudentID as PK > FamilyID as FK related to the family table > JoinDate - (the original date they became a customer) > Other student info. Other info for only this student > > Dance class lookup table with DanceClassID as PK. > > Class Enrollment: Info for the class sign-up and class expiration. > EnrollID - PK > StudentID - PK - related to the student table > DanceClassID - PK - related to a class lookup table > StartDate > ExpDate - some students enroll for 3 months, some for a year > Tuition > NumberOfPayments - (Pmt Term - some pay for the year and some pay each > month) > > AllocClassPmts: Allocating the payments > AllocClassPmtID > EnrollID - related to the Enrollment table > StudentID - related to the StudentID in the class enrollment table > Amount > MethodOfPmt - (Ck, cash or charge) > PmtDate > > Deposits: > DepositID > DepDate > DepAmt > DepType - (Cash or check) > > Sorry for the long post - but to recap, do my tables look correct and if > so, how do I relate the deposit table to payments? > Thank you in advanch so much. > Nancy > > >
From: Stop$teve on 21 Feb 2010 15:14 "Nomy" <noname(a)nonsense.invalid> schreef in bericht news:eUrJdolsKHA.5936(a)TK2MSFTNGP04.phx.gbl... > Hi, > I posted a couple of weeks ago regarding a simple DB for dance classes and > received excellent advice. I've made good progress but have another > question. I've been reading a lot and understand the tables are the most > important part and would really appreciate help again. > <snipped long post> > Sorry for the long post - but to recap, do my tables look correct and if so, how do I relate the deposit table to payments? > Thank you in advanch so much. > Nancy > $teve answered your question... Maybe $teve can help, but mind you... he is after your money... http://home.tiscali.nl/arracom/whoissteve.html Regards, Arno R
From: Nomy on 22 Feb 2010 11:55 Steve, thank you for you detailed response. Could you help with this. I understand that tblClassPayment is related to tblClassPaymentDetail by ClassPaymentID and tblDeposit is related to tblDepositDetail by DepositID using a form and subform for the payments and deposits. I know you said to put a field called ClassPaymentDetailID in tblDepositDetail but how will the data from the payment detail table be posted to the deposit detail table. Nancy "Steve" <notmyemail(a)address.com> wrote in message news:enm%232PxsKHA.3408(a)TK2MSFTNGP06.phx.gbl... > Hi Nancy, > > I think you need some revisions to your tables. Observations: > 1. TblDanceClass should define the start and end dates, tuition per > class, instructor if different for any classes and the type of dance if > different for any classes. Tuition per class is needed because some > students enroll for 3 months and some enroll for a year. > 2. TblClassEnrollment should record the students in a specific class in > TblDanceClass. StartDate is not needed because it is defined in > TblDanceClass. ExpDate would be better as NumClassesEnrolled because in > the end that is what you need to calculate for payments. Tuition is not > needed because it is defined in TblDanceClass. NumberOfPayments is not > needed because it is defined by NumClassesEnrolled . > 3, Regarding payments and deposits ..... You receive a check, cash or > credit card from a FamilyID so the payment is a single record in a payment > table, The payment needs to be allocated so you also need a payment > details table. The same thing about deposits ... you make a deposit by > check or cash or both and then then you need a deposit details table to > show where the deposit came from. I suggest you make a deposit of class > payments a separate transaction of a deposit for other things to simplify > your accounting. That all sais, I suggest the following tables: > TblClassPayment > ClassPaymentID > FamilyID > PaymentDate > PaymentMethodID From TblPaymentMethof > CheckNum > CCNum > > TblClassPaymentDetail > ClassPaymentDetaiID > ClassPaymentID > EnrollmentID > ClassPaymentAmount > > TblDeposit > DepositID > DepositDate > DepositType Check or Cash > DepositCheckNum > > TblDepositDetail > DepositDetailID > DepositID > ClassPaymentDetaiID > DepositAmount > > Nancy, study the above and mull it over. If you have any comments or > questions, post back and I will try to help. > > Steve > santus(a)penn.com > > > > > > > > "Nomy" <noname(a)nonsense.invalid> wrote in message > news:eUrJdolsKHA.5936(a)TK2MSFTNGP04.phx.gbl... >> Hi, >> I posted a couple of weeks ago regarding a simple DB for dance classes >> and >> received excellent advice. I've made good progress but have another >> question. I've been reading a lot and understand the tables are the most >> important part and would really appreciate help again. >> >> I want to record class payments and also bank deposits. All payments are >> deposits but not all deposits are class payments. Some deposits are for >> other various items and I can't figure out how to relate the deposits to >> the >> class payments. >> >> I normally hand write deposit slips then I have to post checks for class >> payments again. >> Some payments are for more than one student so I'll have to allocate to >> the >> correct accounts. The class payments are currently done in Excel and I'd >> really like to streamline this process. >> >> Tables I have so far: (I've not built any forms yet) >> >> FamilyData: Or the person responsible for class payment. >> FamilyID as PK. >> with additional family mailing info etc. >> >> Students: >> StudentID as PK >> FamilyID as FK related to the family table >> JoinDate - (the original date they became a customer) >> Other student info. Other info for only this student >> >> Dance class lookup table with DanceClassID as PK. >> >> Class Enrollment: Info for the class sign-up and class expiration. >> EnrollID - PK >> StudentID - PK - related to the student table >> DanceClassID - PK - related to a class lookup table >> StartDate >> ExpDate - some students enroll for 3 months, some for a year >> Tuition >> NumberOfPayments - (Pmt Term - some pay for the year and some pay each >> month) >> >> AllocClassPmts: Allocating the payments >> AllocClassPmtID >> EnrollID - related to the Enrollment table >> StudentID - related to the StudentID in the class enrollment table >> Amount >> MethodOfPmt - (Ck, cash or charge) >> PmtDate >> >> Deposits: >> DepositID >> DepDate >> DepAmt >> DepType - (Cash or check) >> >> Sorry for the long post - but to recap, do my tables look correct and if >> so, how do I relate the deposit table to payments? >> Thank you in advanch so much. >> Nancy >> >> >> > >
From: BruceM via AccessMonster.com on 22 Feb 2010 15:12
A payment is one thing, and a deposit another. You could have a cash payment and immediately put the cash to use for something. Deposits and payments may be related, but it may be that not all payments are deposited. Even if they invariably are, there are two distinct transactions: you being paid, and you making a deposit. You may need to allow for a deposit being something other than a class payment. I don't know if there are grants, donations, studio rental, or other sources of income, but if so the suggested model does not allow for that from what I can tell. The Deposit table may not have a FamilyID in all cases. You may want a field in the Deposits table to describe the deposit generally, and subform records only for deposits that are specifically applied to classes. However, there is another wrinkle if a deposit can be several payments from several people. Typically a deposit will describe cash as one line item without breaking it down into the source for each dollar, then each check is listed separately. You can link all of the information as needed, but you have to decide how far you want to go down that road. Are classes defined as a certain number of sessions with a fixed enrollment on set dates? Or maybe Jazz 1 is offered three times a week, and people who have enrolled for Jazz 1 may attend any of the three sessions. Or maybe somebody can purchase ten classes to be used as wanted, or unlimited classes for a month at a time. The details will have a bearing on how you structure the database. Nomy wrote: >Steve, thank you for you detailed response. > >Could you help with this. I understand that tblClassPayment is related to >tblClassPaymentDetail by ClassPaymentID and tblDeposit is related to >tblDepositDetail by DepositID using a form and subform for the payments and >deposits. I know you said to put a field called ClassPaymentDetailID in >tblDepositDetail but how will the data from the payment detail table be >posted to the deposit detail table. > >Nancy > >> Hi Nancy, >> >[quoted text clipped - 111 lines] >>> Thank you in advanch so much. >>> Nancy -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-tablesdbdesign/201002/1 |