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 |
#11
|
|||
|
|||
Dance Classes
Having separate deposit tables makes determining the total of all deposits
as well as tracking deposits of class payments and deposits of non-class income separately very easy. TblBankAccount is necessary to link the two deposit systems. Steve "BruceM via AccessMonster.com" u54429@uwe wrote in message news:a41ca48e96636@uwe... 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 |
#12
|
|||
|
|||
Dance Classes
I'll wait to see what the OP has to say. If there is a business need to
separate class payments and non-class income, that is one thing, but I am not going to tell the OP it is necessary to proceed in that way, and it may not be possible to convince me that parallel tables (rather than a single field to distinguish the deposit type as needed) are the way to proceed. I wondered about the business need to associate a specific deposit detail with a specific payment. Details in my previous posting. Also, I listed some other considerations. No need to repeat them here. Again, I will wait for the OP's response. Absent that I see no reason to continue hashing this out. Steve wrote: Having separate deposit tables makes determining the total of all deposits as well as tracking deposits of class payments and deposits of non-class income separately very easy. TblBankAccount is necessary to link the two deposit systems. Steve Steve, I was thinking that a Deposit record is a single deposit event, or the [quoted text clipped - 100 lines] Thank you in advanch so much. Nancy -- Message posted via http://www.accessmonster.com |
#13
|
|||
|
|||
Dance Classes
Bruce and Steve,
Thank you both so much for your valued assistance. Sorry for the delay in answering but had to leave town for a family matter unexpectedly, and I didn't have access to a computer and have just returned. I've read the posts and there are several interesting possibilities. All the suggestions were excellent and I think it would be good to be able to track deposits of class payments and non class deposits but I don't think I need two sets of deposit tables. There is only one bank account and I think it would be easier to track back if all deposits were together and be able to identify what kind of deposit it was in addition to cash or check. If I had a field in the deposit detail table called ClassPaymentDetailID as Steve suggested in his first answer, then that field would link back to a class payment after appending the data. Correct? I would then enter the non class deposits manually as Bruce suggested. The other side of this coin would be the ClassPaymentDetailID field would be empty for deposit details that were not class payments. Do either of you see this as a problem? I'm going to read through everything again and think it all through very carefully. I'm not exactly new to access but have been away from it for a while and have to brush up on a lot. I have never used the VBA code to add new recordsets so I have my work cut out for me. I'm excited about doing this project and eager to continue. This newsgroup is an awesome tool. Thank you both again for all the help. Nancy "BruceM via AccessMonster.com" u54429@uwe wrote in message news:a41e9005d275e@uwe... I'll wait to see what the OP has to say. If there is a business need to separate class payments and non-class income, that is one thing, but I am not going to tell the OP it is necessary to proceed in that way, and it may not be possible to convince me that parallel tables (rather than a single field to distinguish the deposit type as needed) are the way to proceed. I wondered about the business need to associate a specific deposit detail with a specific payment. Details in my previous posting. Also, I listed some other considerations. No need to repeat them here. Again, I will wait for the OP's response. Absent that I see no reason to continue hashing this out. Steve wrote: Having separate deposit tables makes determining the total of all deposits as well as tracking deposits of class payments and deposits of non-class income separately very easy. TblBankAccount is necessary to link the two deposit systems. Steve Steve, I was thinking that a Deposit record is a single deposit event, or the [quoted text clipped - 100 lines] Thank you in advanch so much. Nancy -- Message posted via http://www.accessmonster.com |
#14
|
|||
|
|||
Dance Classes
As I understand, a payment is made by the person identified in the Family
table. It may be for more than one student, in which case it is for a larger amount than is needed for either of the students individually. tblClassPayment should have a CheckAmount field in addition to CheckNum, etc., and any link to DepositDetail should be by way of tblClassPayment, as you will be depositing the entire check amount, not the amount attributable to each student. How much detail do you need in the DepositDetail records? Do you want Family information, Student information, bank information, check number, or what exactly? If you need that type of information you would link to the ID field only, and use a query to show the rest of the data. At the same time you may need fields to store details about non-class payments. A separate table to list such income (which is different than a separate Deposit table) may be the best way to proceed, as you would be able to store a single ID field in the DepositDetail record. That said, I don't see a structural problem with leaving the ID field blank. Remember that your record of payment received is already in the tblClassPayment, so by associating a DepositDetail record with a ClassPayment record you are storing only the information of when a specific check was deposited, which may be more Deposit information than you need. It can be done, but it may be quite a bit of work for minimal gain. However, I think in any case the DepositDetail record should have a Comments field or some such. Nomy wrote: Bruce and Steve, Thank you both so much for your valued assistance. Sorry for the delay in answering but had to leave town for a family matter unexpectedly, and I didn't have access to a computer and have just returned. I've read the posts and there are several interesting possibilities. All the suggestions were excellent and I think it would be good to be able to track deposits of class payments and non class deposits but I don't think I need two sets of deposit tables. There is only one bank account and I think it would be easier to track back if all deposits were together and be able to identify what kind of deposit it was in addition to cash or check. If I had a field in the deposit detail table called ClassPaymentDetailID as Steve suggested in his first answer, then that field would link back to a class payment after appending the data. Correct? I would then enter the non class deposits manually as Bruce suggested. The other side of this coin would be the ClassPaymentDetailID field would be empty for deposit details that were not class payments. Do either of you see this as a problem? I'm going to read through everything again and think it all through very carefully. I'm not exactly new to access but have been away from it for a while and have to brush up on a lot. I have never used the VBA code to add new recordsets so I have my work cut out for me. I'm excited about doing this project and eager to continue. This newsgroup is an awesome tool. Thank you both again for all the help. Nancy I'll wait to see what the OP has to say. If there is a business need to separate class payments and non-class income, that is one thing, but I am [quoted text clipped - 25 lines] Thank you in advanch so much. Nancy -- Message posted via http://www.accessmonster.com |
#15
|
|||
|
|||
Dance Classes
Bruce,
Oh, you're right. I don't really need that kind of pmt detail in the deposit detail table. All I really need is a date, pmt type, I can get that info from the class payment tables. I agree with the comments field in the deposit detail. I like the idea of a separate table to list non class payment income. In fact, the more I think about it, the more I like it. Thank you for suggesting it. Thank you again for taking so much time to help. Nancy "BruceM via AccessMonster.com" u54429@uwe wrote in message news:a42b612cbe082@uwe... As I understand, a payment is made by the person identified in the Family table. It may be for more than one student, in which case it is for a larger amount than is needed for either of the students individually. tblClassPayment should have a CheckAmount field in addition to CheckNum, etc., and any link to DepositDetail should be by way of tblClassPayment, as you will be depositing the entire check amount, not the amount attributable to each student. How much detail do you need in the DepositDetail records? Do you want Family information, Student information, bank information, check number, or what exactly? If you need that type of information you would link to the ID field only, and use a query to show the rest of the data. At the same time you may need fields to store details about non-class payments. A separate table to list such income (which is different than a separate Deposit table) may be the best way to proceed, as you would be able to store a single ID field in the DepositDetail record. That said, I don't see a structural problem with leaving the ID field blank. Remember that your record of payment received is already in the tblClassPayment, so by associating a DepositDetail record with a ClassPayment record you are storing only the information of when a specific check was deposited, which may be more Deposit information than you need. It can be done, but it may be quite a bit of work for minimal gain. However, I think in any case the DepositDetail record should have a Comments field or some such. Nomy wrote: Bruce and Steve, Thank you both so much for your valued assistance. Sorry for the delay in answering but had to leave town for a family matter unexpectedly, and I didn't have access to a computer and have just returned. I've read the posts and there are several interesting possibilities. All the suggestions were excellent and I think it would be good to be able to track deposits of class payments and non class deposits but I don't think I need two sets of deposit tables. There is only one bank account and I think it would be easier to track back if all deposits were together and be able to identify what kind of deposit it was in addition to cash or check. If I had a field in the deposit detail table called ClassPaymentDetailID as Steve suggested in his first answer, then that field would link back to a class payment after appending the data. Correct? I would then enter the non class deposits manually as Bruce suggested. The other side of this coin would be the ClassPaymentDetailID field would be empty for deposit details that were not class payments. Do either of you see this as a problem? I'm going to read through everything again and think it all through very carefully. I'm not exactly new to access but have been away from it for a while and have to brush up on a lot. I have never used the VBA code to add new recordsets so I have my work cut out for me. I'm excited about doing this project and eager to continue. This newsgroup is an awesome tool. Thank you both again for all the help. Nancy I'll wait to see what the OP has to say. If there is a business need to separate class payments and non-class income, that is one thing, but I am [quoted text clipped - 25 lines] Thank you in advanch so much. Nancy -- Message posted via http://www.accessmonster.com |
#16
|
|||
|
|||
Dance Classes
Actually, I kind of wish I had not suggested a separate table for non-class
payments, as a single table for all payments/income may be preferable. Starting from Steve's suggested structure for tblClassPayment, maybe you could modify it along these lines: tblPayment PaymentID (primary key) FamilyID (linking field) PaymentDate PaymentReason (optional) PaymentMethod (e.g. check/cash) CheckNum Each family may make many payments. I don't know if you have gotten to the point of making forms, but if you have a Family form you can have a Payment subform based on tblPayment. Also, you could build a standalone form based on tblPayment for non-class payments. tblPayments would then contain a listing of all payments for all reasons. FamilyID would default to 0 for non- class payments. CheckNum would be 0 for Cash payments. It should be possible to build a query that lists each check separately, and combines all cash payments into a single line (although it would depend on you depositing all cash received). I won't go too deep into the options just now. Anyhow, the query could be the source for the deposit slip, with no need to store the deposited amounts. However, if it is your practice to keep deposit slips, at least for a while, if makes sense to store the deposit information, in which case you can create a deposit record, then open a recordset based on tblDepositDetails and add the records that appear in the query. This is not nearly as complicated as it may sound, but I will wait to see how you want to proceed before I launch into details. Nomy wrote: Bruce, Oh, you're right. I don't really need that kind of pmt detail in the deposit detail table. All I really need is a date, pmt type, I can get that info from the class payment tables. I agree with the comments field in the deposit detail. I like the idea of a separate table to list non class payment income. In fact, the more I think about it, the more I like it. Thank you for suggesting it. Thank you again for taking so much time to help. Nancy As I understand, a payment is made by the person identified in the Family table. It may be for more than one student, in which case it is for a [quoted text clipped - 77 lines] Thank you in advanch so much. Nancy -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...esign/201002/1 |
#17
|
|||
|
|||
Dance Classes
Bruce,
Thanks for the idea, I'll have some time later today to try it. A stand alone form for non class payments I can see working but I would have to have another form based on tblpayments with the fields you list and have a subform based on class enrollment to allocate the payment as a parent could pay make a single payment for two students in different classes. I've started to build a couple of small basic forms to test the suggestions I've received in this newsgroup. Thanks. Nancy "BruceM via AccessMonster.com" u54429@uwe wrote in message news:a438b14053915@uwe... Actually, I kind of wish I had not suggested a separate table for non-class payments, as a single table for all payments/income may be preferable. Starting from Steve's suggested structure for tblClassPayment, maybe you could modify it along these lines: tblPayment PaymentID (primary key) FamilyID (linking field) PaymentDate PaymentReason (optional) PaymentMethod (e.g. check/cash) CheckNum Each family may make many payments. I don't know if you have gotten to the point of making forms, but if you have a Family form you can have a Payment subform based on tblPayment. Also, you could build a standalone form based on tblPayment for non-class payments. tblPayments would then contain a listing of all payments for all reasons. FamilyID would default to 0 for non- class payments. CheckNum would be 0 for Cash payments. It should be possible to build a query that lists each check separately, and combines all cash payments into a single line (although it would depend on you depositing all cash received). I won't go too deep into the options just now. Anyhow, the query could be the source for the deposit slip, with no need to store the deposited amounts. However, if it is your practice to keep deposit slips, at least for a while, if makes sense to store the deposit information, in which case you can create a deposit record, then open a recordset based on tblDepositDetails and add the records that appear in the query. This is not nearly as complicated as it may sound, but I will wait to see how you want to proceed before I launch into details. Nomy wrote: Bruce, Oh, you're right. I don't really need that kind of pmt detail in the deposit detail table. All I really need is a date, pmt type, I can get that info from the class payment tables. I agree with the comments field in the deposit detail. I like the idea of a separate table to list non class payment income. In fact, the more I think about it, the more I like it. Thank you for suggesting it. Thank you again for taking so much time to help. Nancy As I understand, a payment is made by the person identified in the Family table. It may be for more than one student, in which case it is for a [quoted text clipped - 77 lines] Thank you in advanch so much. Nancy -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...esign/201002/1 |
#18
|
|||
|
|||
Dance Classes
The Family table is one of the top-level tables. Below that is the
ClassPayments table, followed by PaymentDetails to show how the payment was allocated. Even if a Family has just one person taking classes, you still would want a Detail record to show how the payment is allocated. With several students in one family it would of course be necessary to do it that way. In either case, the ClassPayments record is the one used for the deposit ticket (unless you require a separate check for each student, which would be unnecessary for the database and inconvenient for the client). Making some forms makes sense for parts of the database where you are clear on the design. Good luck. Keep us posted on your progress. Nomy wrote: Bruce, Thanks for the idea, I'll have some time later today to try it. A stand alone form for non class payments I can see working but I would have to have another form based on tblpayments with the fields you list and have a subform based on class enrollment to allocate the payment as a parent could pay make a single payment for two students in different classes. I've started to build a couple of small basic forms to test the suggestions I've received in this newsgroup. Thanks. Nancy Actually, I kind of wish I had not suggested a separate table for non-class [quoted text clipped - 60 lines] Thank you in advanch so much. Nancy -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...esign/201003/1 |
#19
|
|||
|
|||
Dance Classes
Bruce,
Thank you so much for your help. I will post back in a few days. You are so nice to do this for people. Nancy "BruceM via AccessMonster.com" u54429@uwe wrote in message news:a45e44be2cd73@uwe... The Family table is one of the top-level tables. Below that is the ClassPayments table, followed by PaymentDetails to show how the payment was allocated. Even if a Family has just one person taking classes, you still would want a Detail record to show how the payment is allocated. With several students in one family it would of course be necessary to do it that way. In either case, the ClassPayments record is the one used for the deposit ticket (unless you require a separate check for each student, which would be unnecessary for the database and inconvenient for the client). Making some forms makes sense for parts of the database where you are clear on the design. Good luck. Keep us posted on your progress. Nomy wrote: Bruce, Thanks for the idea, I'll have some time later today to try it. A stand alone form for non class payments I can see working but I would have to have another form based on tblpayments with the fields you list and have a subform based on class enrollment to allocate the payment as a parent could pay make a single payment for two students in different classes. I've started to build a couple of small basic forms to test the suggestions I've received in this newsgroup. Thanks. Nancy Actually, I kind of wish I had not suggested a separate table for non-class [quoted text clipped - 60 lines] Thank you in advanch so much. Nancy -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...esign/201003/1 |
#20
|
|||
|
|||
Dance Classes
This is where I learned a lot of what I know. Just returning the favor. I
will continue to monitor this thread. Good luck. Nomy wrote: Bruce, Thank you so much for your help. I will post back in a few days. You are so nice to do this for people. Nancy The Family table is one of the top-level tables. Below that is the ClassPayments table, followed by PaymentDetails to show how the payment [quoted text clipped - 33 lines] Thank you in advanch so much. Nancy -- Message posted via http://www.accessmonster.com |
Thread Tools | |
Display Modes | |
|
|