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