Dance Classes

[Nomy]

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]

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 address removed)

 

[Nomy]

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

 

[Nomy]

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
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.

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

 

[Steve]

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 address removed)

 

[Steve]

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]

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
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.

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

 

[Nomy]

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
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.

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

 

[Nomy]

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
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.

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

 

[Nomy]

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
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.

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

 

[Nomy]

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
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

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

 

[Nomy]

Bruce,
You've been so kind with your interest, I wanted to let you know what I've
done so far since you said you would check the threads for this post. I
took your advice and created one table (tblPayments) to hold
all payments and/or
purchases. I added a field called NonFamName so I can enter a non family
purchase name if I need to. I also created two different pmt forms based on
the pmt table. One stand alone continuous form as you suggested and one
with a subform to post
class pmts. This seems to work good with no problems yet. All my forms are
very simple at this time.

The payment table fields are as in previous posts except I added a yes/no
field called MkDep as a criteria selection field to append to the deposit
detail table.

I created another form to list the pmts that are marked "yes" in the in the
MkDep yes/no field. This form lists only pmts that have not yet been
deposited. The fields in the deposit table are depositID and
DepDate. I created an append query to append the pmts
to the dep detail after I entered a new record for the deposit date (in the
Deposit
table as Steve suggested) and a query to reset the yes/no field to "no"
after they have been successfully appended.

The query appends the pmts to the depdetail but unless I want to use only a
deposit detail
table, this method is seriously flawed as the query won't enter the
DepositID (linking field to depDetail info)
into the dep detail table. I was hoping I could do that with a query as
I've never used the AddNew function and I'm researching this on the
internet. There doesn't seem to be much in Access help but I think I will
need a
procedure to pass the data to an AddNew function.

I really want to learn to do this myself and was wondering if you have any
suggestions as to websites where I could find information
to learn more about this.

Thank you again for your help and patience. I just wanted to let you know
where I am in this and how much your advice has helped.
Nancy

This is where I learned a lot of what I know. Just returning the favor.
I
will continue to monitor this thread. Good luck.

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