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

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
(E-Mail Removed)







"Nomy" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>
>
>

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" <(E-Mail Removed)> wrote in message
news:enm%(E-Mail Removed)...
> 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
> (E-Mail Removed)
>
>
>
>
>
>
>
> "Nomy" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> 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
>>
>>
>>
>
>

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@uwe> wrote in message
news:a40759646ab95@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/For...esign/201002/1
>

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
(E-Mail Removed)

"Nomy" <(E-Mail Removed)> wrote in message
news:OVWAg$(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:enm%(E-Mail Removed)...
>> 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
>> (E-Mail Removed)
>>
>>
>>
>>
>>
>>
>>
>> "Nomy" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>>> 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
>>>
>>>
>>>
>>
>>
>
>

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" <(E-Mail Removed)> wrote in message
news:OVWAg$(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:enm%(E-Mail Removed)...
>> 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
>> (E-Mail Removed)
>>
>>
>>
>>
>>
>>
>>
>> "Nomy" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>>> 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
>>>
>>>
>>>
>>
>>
>
>

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
>

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
>

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
>

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
>