A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Dance Classes



 
 
Thread Tools Display Modes
  #1  
Old February 20th, 2010, 07:25 PM posted to microsoft.public.access.tablesdbdesign
Nomy
external usenet poster
 
Posts: 11
Default Dance Classes

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



  #2  
Old February 21st, 2010, 05:36 PM posted to microsoft.public.access.tablesdbdesign
Steve[_77_]
external usenet poster
 
Posts: 1,017
Default Dance Classes

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








"Nomy" wrote in message
...
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





  #3  
Old February 21st, 2010, 09:14 PM posted to microsoft.public.access.tablesdbdesign
Stop$teve
external usenet poster
 
Posts: 76
Default Dance Classes


"Nomy" schreef in bericht ...
Hi,
I posted a couple of weeks ago regarding a simple DB for dance classes and
received excellent advice. I've made good progress but have another
question. I've been reading a lot and understand the tables are the most
important part and would really appreciate help again.

snipped long post

Sorry for the long post - but to recap, do my tables look correct and if so, how do I relate the deposit table to payments?
Thank you in advanch so much.
Nancy



$teve answered your question...
Maybe $teve can help, but mind you... he is after your money...
http://home.tiscali.nl/arracom/whoissteve.html

Regards, Arno R



  #4  
Old February 22nd, 2010, 05:55 PM posted to microsoft.public.access.tablesdbdesign
Nomy
external usenet poster
 
Posts: 11
Default Dance Classes

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" wrote in message
...
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








"Nomy" wrote in message
...
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







  #5  
Old February 22nd, 2010, 09:12 PM posted to microsoft.public.access.tablesdbdesign
BruceM via AccessMonster.com
external usenet poster
 
Posts: 448
Default Dance Classes

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

  #6  
Old February 22nd, 2010, 10:52 PM posted to microsoft.public.access.tablesdbdesign
Nomy
external usenet poster
 
Posts: 11
Default Dance Classes

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



  #7  
Old February 23rd, 2010, 01:38 AM posted to microsoft.public.access.tablesdbdesign
Steve[_77_]
external usenet poster
 
Posts: 1,017
Default Dance Classes

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


"Nomy" wrote in message
...
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" wrote in message
...
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








"Nomy" wrote in message
...
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









  #8  
Old February 23rd, 2010, 01:49 PM posted to microsoft.public.access.tablesdbdesign
BruceM via AccessMonster.com
external usenet poster
 
Posts: 448
Default Dance Classes

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/For...esign/201002/1

  #9  
Old February 23rd, 2010, 08:33 PM posted to microsoft.public.access.tablesdbdesign
Steve[_77_]
external usenet poster
 
Posts: 1,017
Default Dance Classes

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" wrote in message
...
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" wrote in message
...
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








"Nomy" wrote in message
...
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









  #10  
Old February 24th, 2010, 01:51 PM posted to microsoft.public.access.tablesdbdesign
BruceM via AccessMonster.com
external usenet poster
 
Posts: 448
Default Dance Classes

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

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 09:01 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.