Dance Classes
in [Tables]
|
Prev: doctors office database
Next: ORGANIZATIONAL CHART
From: Nomy on 22 Feb 2010 16:52 Bruce, Thank You. You have given me a lot think about. My main objective is to track students, the person responsible for payment and the class payments but since my bank will accept an electronic deposit slip with the correct info on it (account and routing #), I thought to separate cash and check deposit transactions as cash is a combined on one line. If I'm going to enter the check info and amount line by line for a bank deposit slip, it seems redundant to then repeat this step and enter it again in a payment and allocation table. To answer your question as to how classes are defined, it's a small studio. Enrollment and tuition is fixed and there is a start date and exp date. It's not just dance. There are yoga classes etc. Most students sign up for three months. I don't want to track schedules. In your answer, you mention that I can link all of the information as needed (deposits and payments). Could you point me in the right direction and thanks again for your help. Nancy "BruceM via AccessMonster.com" <u54429(a)uwe> wrote in message news:a40759646ab95(a)uwe... >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 >
From: Steve on 22 Feb 2010 19:38 Nancy, Sorry for the delay in getting back to you! <<how ...can.... the data from the payment detail table be posted to the deposit detail table.>> First, you need to create a new record in TblDeposit so you have a DepositID to assign to each of your deposit detail records. Simply open your deposit form/subform and enter the data in the main form. Next create a query that returns the payment details that you want to deposit. You'll have to set some criteria to do this. The query only needs ClassPaymentDetailID and ClassPaymentAmount. Next create code that uses Rst.AddNew and Rst.Update to add records to the deposit subform. The code needs to cycle through the query records and add the query records one-by-one to the deposit detail subform. You want to add ClassPaymentDetailID to ClassPaymentDetailID in the subform and add ClassPaymentAmount to DepositAmount in the subform. Access will automatically add DepositID to each record. Steve santus(a)penn.com "Nomy" <noname(a)nonsense.invalid> wrote in message news:OVWAg$9sKHA.5868(a)TK2MSFTNGP05.phx.gbl... > 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 23 Feb 2010 07:49 Steve has suggested in broad outline a way to add the payment information to a deposit record. However, there are some potential difficulties. How will you determine which payments are to be included as deposits? You will want to prevent checks already deposited from being included again, and to allow for the cash deposit being less than the cash payments in case some cash goes to petty cash or some such thing. One possible approach is to use the payment date as a criteria for the query. You could make a query based on the Payment table, with the criteria for PaymentDate something like: Between [Enter start date] And [Enter end date] For PaymentMethod (I would probably use a one-field lookup table consisting of a few records: Cash, Check, CreditCard, plus whatever else (barter?). Steve apparently would use a two-field table, with a numeric ID field, then a description; then would store the ID. You could do it that way too, but for a simple lookup I would simply store just the actual text. In any case, set the criteria for PaymentMethod to "Check" (or the corresponding ID value with a two-field table). This will return (produce a listing of) all payments by check in the specified date range. You could then use some code to add to a recordset, as Steve suggested. It's not as daunting as it may sound, but I suggest starting with the query to be sure you can get the desired results. After producing the records listing each check you could compare the list to the actual checks, editing as needed. Count the cash and add that as a single Deposit Detail record, and add any checks received for reasons other than classes. The point is to use the query to do most of the work, and modify/add records as needed. My understanding now is that students sign up for three months, during which time they may attend classes as they wish. If not that exactly, it seems students are not bound to a specific schedule, so you will have to rethink the enrollment table, which as things stand is based on students taking specific classes at specific times, as if they were in an academic school with a fixed class schedule. Perhaps a "Class" would be a three-month block of time. You could have a class table so you could make and print out the schedule, but it sounds as it would not be related to enrollment. If you have not yet built any forms, I think it's time to make a Family form with a Student subform, so you can become familiar with building an interface and managing simple related records. Thsi is not a trivial project, but I think it can be done if you are willing to stick with it, and you build it in increments. Nomy wrote: >Bruce, > >Thank You. You have given me a lot think about. My main objective is to >track students, the person responsible for payment and the class payments >but since my bank will accept an electronic deposit slip with the correct >info on it (account and routing #), I thought to separate cash and check >deposit transactions as cash is a combined on one line. > >If I'm going to enter the check info and amount line by line for a bank >deposit slip, it seems redundant to then repeat this step and enter it again >in a payment and allocation table. > >To answer your question as to how classes are defined, it's a small studio. >Enrollment and tuition is fixed and there is a start date and exp date. >It's not just dance. There are yoga classes etc. Most students sign up for >three months. I don't want to track schedules. > >In your answer, you mention that I can link all of the information as needed >(deposits and payments). Could you point me in the right direction and >thanks again for your help. > >Nancy > >>A payment is one thing, and a deposit another. You could have a cash >>payment >[quoted text clipped - 49 lines] >>>>> Thank you in advanch so much. >>>>> Nancy -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-tablesdbdesign/201002/1
From: Steve on 23 Feb 2010 14:33 Nancy, Bruce keeps mentioning that deposits other than class payments are not handled by my proposed tables. He is correct but that was an intentional ommission. TblDeposit and TblDepositDetail are meant to only record deposits of class payments so you will always be able to just track class payments. If you want to include deposits from other sources, I suggest you need to revise TblDeposit and add more tables: TblBankAccount BankAccountID BankName BankAccountNumber <Bank address fields> TblDeposit DepositID BankAccountID DepositDate DepositType Check or Cash DepositCheckNum TblNonClassDeposit NonClassDepositID BankAccountID NonClassDepositDate NonClassDepositType Check or Cash NonClassDepositCheckNum TblNonClassDepositDetail NonClassDepositDetailID NonClassDepositID NonClassDepositDescription NonClassDepositAmount If you make these changes, you will be able to determine the total of all deposits as well as track deposit of class payments and deposit of non-class income separately. "Nomy" <noname(a)nonsense.invalid> wrote in message news:OVWAg$9sKHA.5868(a)TK2MSFTNGP05.phx.gbl... > 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 24 Feb 2010 07:51
Steve, I was thinking that a Deposit record is a single deposit event, or the filling out of a single deposit ticket, or however you want to look at it. That being the case, deposit type (cash or check) would be more appropriate to the deposit detail. I assume you would still have that table as described earlier, although you did not mention it in your latest post. The way I see it, payment information is recorded in the ClassPayment table, which also includes the payment type (check or cash). I think your idea of opening a recordset and appending check payments from ClassPayment to DepositDetail table is a good idea. It would be possible also to append other information such as FamilyID if necessary, but I wonder if there is a reason for recording that level of detail. In a paper-based system a person would make a payment, which would be recorded in the appropriate ledger. The payments would be entered onto a deposit slip, but I expect in most small business cases the deposit amount only is noted, not the source of the specific deposit line item. I don't know what business need would be served by going beyond that in an electronic system. My thinking was that to do most of the work of filling out the deposit ticket you could append to the DepositDetail table records of check payments as you suggested, then add all cash as a single detail line item, and any other checks would be input manually. A non-class deposit table may be more slicing and dicing than is necessary, but the OP may weigh in on that. Please don't think of this as a challenge to your entire idea. Rather, I am suggesting further considerations, including these points: A payment is made by the Family person, but may be for more than one student. Therefore, the payment record would be associated with the FamilyID, but each individual Student record would show that the payment was made. Payment for two students at once would presumably be a single check, and would be deposited as such, so any link with the Deposit record needs to be to the Family record. The Bank Account table may be a valid consideration, but if there is a single bank account it may add an unnecessary layer. A single deposit ticket for a single bank account should be able to accomodate deposits from all sources, unless there is a specific business need to separate class and non-class payments. Again, appending records to do most of the work, then inputting additional records manually, could be a good solution. Steve wrote: >Nancy, > >Bruce keeps mentioning that deposits other than class payments are not >handled by my proposed tables. He is correct but that was an intentional >ommission. TblDeposit and TblDepositDetail are meant to only record deposits >of class payments so you will always be able to just track class payments. >If you want to include deposits from other sources, I suggest you need to >revise TblDeposit and add more tables: > >TblBankAccount >BankAccountID >BankName >BankAccountNumber ><Bank address fields> > >TblDeposit >DepositID >BankAccountID >DepositDate >DepositType Check or Cash >DepositCheckNum > >TblNonClassDeposit >NonClassDepositID >BankAccountID >NonClassDepositDate >NonClassDepositType Check or Cash >NonClassDepositCheckNum > >TblNonClassDepositDetail >NonClassDepositDetailID >NonClassDepositID >NonClassDepositDescription >NonClassDepositAmount > >If you make these changes, you will be able to determine the total of all >deposits as well as track deposit of class payments and deposit of non-class >income separately. > >> Steve, thank you for you detailed response. >> >[quoted text clipped - 124 lines] >>>> Thank you in advanch so much. >>>> Nancy -- Message posted via http://www.accessmonster.com |