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
  #11  
Old February 24th, 2010, 05:03 PM posted to microsoft.public.access.tablesdbdesign
Steve[_77_]
external usenet poster
 
Posts: 1,017
Default Dance Classes

Having separate deposit tables makes determining the total of all deposits
as well as tracking deposits of class payments and deposits of non-class
income separately very easy. TblBankAccount is necessary to link the two
deposit systems.

Steve


"BruceM via AccessMonster.com" [email protected] wrote in message
news:[email protected]
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



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

I'll wait to see what the OP has to say. If there is a business need to
separate class payments and non-class income, that is one thing, but I am not
going to tell the OP it is necessary to proceed in that way, and it may not
be possible to convince me that parallel tables (rather than a single field
to distinguish the deposit type as needed) are the way to proceed.

I wondered about the business need to associate a specific deposit detail
with a specific payment. Details in my previous posting. Also, I listed
some other considerations. No need to repeat them here.

Again, I will wait for the OP's response. Absent that I see no reason to
continue hashing this out.


Steve wrote:
Having separate deposit tables makes determining the total of all deposits
as well as tracking deposits of class payments and deposits of non-class
income separately very easy. TblBankAccount is necessary to link the two
deposit systems.

Steve

Steve, I was thinking that a Deposit record is a single deposit event, or
the

[quoted text clipped - 100 lines]
Thank you in advanch so much.
Nancy


--
Message posted via http://www.accessmonster.com

  #13  
Old February 24th, 2010, 09:18 PM posted to microsoft.public.access.tablesdbdesign
Nomy
external usenet poster
 
Posts: 11
Default Dance Classes

Bruce and Steve,

Thank you both so much for your valued assistance. Sorry for the delay in
answering but had to leave town for a family matter unexpectedly, and I
didn't have access to a computer and have just returned. I've read the
posts and there are several interesting possibilities.

All the suggestions were excellent and I think it would be good to be able
to track deposits of class payments and non class deposits but I don't think
I need two sets of deposit tables. There is only one bank account and I
think it would be easier to track back if all deposits were together and be
able to identify what kind of deposit it was in addition to cash or check.

If I had a field in the deposit detail table called ClassPaymentDetailID as
Steve suggested in his first answer, then that field would link back to a
class payment after appending the data. Correct? I would then enter the
non class deposits manually as Bruce suggested.

The other side of this coin would be the ClassPaymentDetailID field would be
empty for deposit details that were not class payments. Do either of you
see this as a problem?

I'm going to read through everything again and think it all through very
carefully. I'm not exactly new to access but have been away from it for a
while and have to brush up on a lot. I have never used the VBA code to add
new recordsets so I have my work cut out for me.

I'm excited about doing this project and eager to continue. This newsgroup
is an awesome tool.

Thank you both again for all the help.
Nancy





"BruceM via AccessMonster.com" [email protected] wrote in message
news:[email protected]
I'll wait to see what the OP has to say. If there is a business need to
separate class payments and non-class income, that is one thing, but I am
not
going to tell the OP it is necessary to proceed in that way, and it may
not
be possible to convince me that parallel tables (rather than a single
field
to distinguish the deposit type as needed) are the way to proceed.

I wondered about the business need to associate a specific deposit detail
with a specific payment. Details in my previous posting. Also, I listed
some other considerations. No need to repeat them here.

Again, I will wait for the OP's response. Absent that I see no reason to
continue hashing this out.


Steve wrote:
Having separate deposit tables makes determining the total of all deposits
as well as tracking deposits of class payments and deposits of non-class
income separately very easy. TblBankAccount is necessary to link the two
deposit systems.

Steve

Steve, I was thinking that a Deposit record is a single deposit event,
or
the

[quoted text clipped - 100 lines]
Thank you in advanch so much.
Nancy


--
Message posted via http://www.accessmonster.com



  #14  
Old February 25th, 2010, 05:59 PM posted to microsoft.public.access.tablesdbdesign
BruceM via AccessMonster.com
external usenet poster
 
Posts: 448
Default Dance Classes

As I understand, a payment is made by the person identified in the Family
table. It may be for more than one student, in which case it is for a larger
amount than is needed for either of the students individually.
tblClassPayment should have a CheckAmount field in addition to CheckNum, etc.,
and any link to DepositDetail should be by way of tblClassPayment, as you
will be depositing the entire check amount, not the amount attributable to
each student.

How much detail do you need in the DepositDetail records? Do you want Family
information, Student information, bank information, check number, or what
exactly? If you need that type of information you would link to the ID field
only, and use a query to show the rest of the data. At the same time you may
need fields to store details about non-class payments. A separate table to
list such income (which is different than a separate Deposit table) may be
the best way to proceed, as you would be able to store a single ID field in
the DepositDetail record. That said, I don't see a structural problem with
leaving the ID field blank.

Remember that your record of payment received is already in the
tblClassPayment, so by associating a DepositDetail record with a ClassPayment
record you are storing only the information of when a specific check was
deposited, which may be more Deposit information than you need. It can be
done, but it may be quite a bit of work for minimal gain. However, I think
in any case the DepositDetail record should have a Comments field or some
such.


Nomy wrote:
Bruce and Steve,

Thank you both so much for your valued assistance. Sorry for the delay in
answering but had to leave town for a family matter unexpectedly, and I
didn't have access to a computer and have just returned. I've read the
posts and there are several interesting possibilities.

All the suggestions were excellent and I think it would be good to be able
to track deposits of class payments and non class deposits but I don't think
I need two sets of deposit tables. There is only one bank account and I
think it would be easier to track back if all deposits were together and be
able to identify what kind of deposit it was in addition to cash or check.

If I had a field in the deposit detail table called ClassPaymentDetailID as
Steve suggested in his first answer, then that field would link back to a
class payment after appending the data. Correct? I would then enter the
non class deposits manually as Bruce suggested.

The other side of this coin would be the ClassPaymentDetailID field would be
empty for deposit details that were not class payments. Do either of you
see this as a problem?

I'm going to read through everything again and think it all through very
carefully. I'm not exactly new to access but have been away from it for a
while and have to brush up on a lot. I have never used the VBA code to add
new recordsets so I have my work cut out for me.

I'm excited about doing this project and eager to continue. This newsgroup
is an awesome tool.

Thank you both again for all the help.
Nancy

I'll wait to see what the OP has to say. If there is a business need to
separate class payments and non-class income, that is one thing, but I am

[quoted text clipped - 25 lines]
Thank you in advanch so much.
Nancy


--
Message posted via http://www.accessmonster.com

  #15  
Old February 26th, 2010, 06:31 PM posted to microsoft.public.access.tablesdbdesign
Nomy
external usenet poster
 
Posts: 11
Default Dance Classes

Bruce,
Oh, you're right. I don't really need that kind of pmt detail in the
deposit detail table. All I really need is a date, pmt type, I can get
that info from the class payment tables. I agree with the comments field in
the deposit detail.

I like the idea of a separate table to list non class payment income. In
fact, the more I think about it, the more I like it. Thank you for
suggesting it.

Thank you again for taking so much time to help.

Nancy


"BruceM via AccessMonster.com" [email protected] wrote in message
news:[email protected]
As I understand, a payment is made by the person identified in the Family
table. It may be for more than one student, in which case it is for a
larger
amount than is needed for either of the students individually.
tblClassPayment should have a CheckAmount field in addition to CheckNum,
etc.,
and any link to DepositDetail should be by way of tblClassPayment, as you
will be depositing the entire check amount, not the amount attributable to
each student.

How much detail do you need in the DepositDetail records? Do you want
Family
information, Student information, bank information, check number, or what
exactly? If you need that type of information you would link to the ID
field
only, and use a query to show the rest of the data. At the same time you
may
need fields to store details about non-class payments. A separate table
to
list such income (which is different than a separate Deposit table) may be
the best way to proceed, as you would be able to store a single ID field
in
the DepositDetail record. That said, I don't see a structural problem
with
leaving the ID field blank.

Remember that your record of payment received is already in the
tblClassPayment, so by associating a DepositDetail record with a
ClassPayment
record you are storing only the information of when a specific check was
deposited, which may be more Deposit information than you need. It can be
done, but it may be quite a bit of work for minimal gain. However, I
think
in any case the DepositDetail record should have a Comments field or some
such.


Nomy wrote:
Bruce and Steve,

Thank you both so much for your valued assistance. Sorry for the delay in
answering but had to leave town for a family matter unexpectedly, and I
didn't have access to a computer and have just returned. I've read the
posts and there are several interesting possibilities.

All the suggestions were excellent and I think it would be good to be able
to track deposits of class payments and non class deposits but I don't
think
I need two sets of deposit tables. There is only one bank account and I
think it would be easier to track back if all deposits were together and
be
able to identify what kind of deposit it was in addition to cash or check.

If I had a field in the deposit detail table called ClassPaymentDetailID
as
Steve suggested in his first answer, then that field would link back to a
class payment after appending the data. Correct? I would then enter the
non class deposits manually as Bruce suggested.

The other side of this coin would be the ClassPaymentDetailID field would
be
empty for deposit details that were not class payments. Do either of you
see this as a problem?

I'm going to read through everything again and think it all through very
carefully. I'm not exactly new to access but have been away from it for a
while and have to brush up on a lot. I have never used the VBA code to
add
new recordsets so I have my work cut out for me.

I'm excited about doing this project and eager to continue. This
newsgroup
is an awesome tool.

Thank you both again for all the help.
Nancy

I'll wait to see what the OP has to say. If there is a business need to
separate class payments and non-class income, that is one thing, but I
am

[quoted text clipped - 25 lines]
Thank you in advanch so much.
Nancy


--
Message posted via http://www.accessmonster.com



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

Actually, I kind of wish I had not suggested a separate table for non-class
payments, as a single table for all payments/income may be preferable.
Starting from Steve's suggested structure for tblClassPayment, maybe you
could modify it along these lines:

tblPayment
PaymentID (primary key)
FamilyID (linking field)
PaymentDate
PaymentReason (optional)
PaymentMethod (e.g. check/cash)
CheckNum

Each family may make many payments. I don't know if you have gotten to the
point of making forms, but if you have a Family form you can have a Payment
subform based on tblPayment. Also, you could build a standalone form based
on tblPayment for non-class payments. tblPayments would then contain a
listing of all payments for all reasons. FamilyID would default to 0 for non-
class payments. CheckNum would be 0 for Cash payments. It should be
possible to build a query that lists each check separately, and combines all
cash payments into a single line (although it would depend on you depositing
all cash received). I won't go too deep into the options just now. Anyhow,
the query could be the source for the deposit slip, with no need to store the
deposited amounts. However, if it is your practice to keep deposit slips, at
least for a while, if makes sense to store the deposit information, in which
case you can create a deposit record, then open a recordset based on
tblDepositDetails and add the records that appear in the query. This is not
nearly as complicated as it may sound, but I will wait to see how you want to
proceed before I launch into details.

Nomy wrote:
Bruce,
Oh, you're right. I don't really need that kind of pmt detail in the
deposit detail table. All I really need is a date, pmt type, I can get
that info from the class payment tables. I agree with the comments field in
the deposit detail.

I like the idea of a separate table to list non class payment income. In
fact, the more I think about it, the more I like it. Thank you for
suggesting it.

Thank you again for taking so much time to help.

Nancy

As I understand, a payment is made by the person identified in the Family
table. It may be for more than one student, in which case it is for a

[quoted text clipped - 77 lines]
Thank you in advanch so much.
Nancy


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...esign/201002/1

  #17  
Old March 1st, 2010, 05:07 PM posted to microsoft.public.access.tablesdbdesign
Nomy
external usenet poster
 
Posts: 11
Default Dance Classes

Bruce,
Thanks for the idea, I'll have some time later today to try it. A stand
alone form for non class payments I can see working but I would have to have
another form based on tblpayments with the fields you list and have a
subform based on class enrollment to allocate the payment as a parent could
pay make a single payment for two students in different classes.

I've started to build a couple of small basic forms to test the suggestions
I've received in this newsgroup.

Thanks.
Nancy


"BruceM via AccessMonster.com" [email protected] wrote in message
news:[email protected]
Actually, I kind of wish I had not suggested a separate table for
non-class
payments, as a single table for all payments/income may be preferable.
Starting from Steve's suggested structure for tblClassPayment, maybe you
could modify it along these lines:

tblPayment
PaymentID (primary key)
FamilyID (linking field)
PaymentDate
PaymentReason (optional)
PaymentMethod (e.g. check/cash)
CheckNum

Each family may make many payments. I don't know if you have gotten to
the
point of making forms, but if you have a Family form you can have a
Payment
subform based on tblPayment. Also, you could build a standalone form
based
on tblPayment for non-class payments. tblPayments would then contain a
listing of all payments for all reasons. FamilyID would default to 0 for
non-
class payments. CheckNum would be 0 for Cash payments. It should be
possible to build a query that lists each check separately, and combines
all
cash payments into a single line (although it would depend on you
depositing
all cash received). I won't go too deep into the options just now.
Anyhow,
the query could be the source for the deposit slip, with no need to store
the
deposited amounts. However, if it is your practice to keep deposit slips,
at
least for a while, if makes sense to store the deposit information, in
which
case you can create a deposit record, then open a recordset based on
tblDepositDetails and add the records that appear in the query. This is
not
nearly as complicated as it may sound, but I will wait to see how you want
to
proceed before I launch into details.

Nomy wrote:
Bruce,
Oh, you're right. I don't really need that kind of pmt detail in the
deposit detail table. All I really need is a date, pmt type, I can get
that info from the class payment tables. I agree with the comments field
in
the deposit detail.

I like the idea of a separate table to list non class payment income. In
fact, the more I think about it, the more I like it. Thank you for
suggesting it.

Thank you again for taking so much time to help.

Nancy

As I understand, a payment is made by the person identified in the
Family
table. It may be for more than one student, in which case it is for a

[quoted text clipped - 77 lines]
Thank you in advanch so much.
Nancy


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...esign/201002/1



  #18  
Old March 1st, 2010, 07:07 PM posted to microsoft.public.access.tablesdbdesign
BruceM via AccessMonster.com
external usenet poster
 
Posts: 448
Default Dance Classes

The Family table is one of the top-level tables. Below that is the
ClassPayments table, followed by PaymentDetails to show how the payment was
allocated. Even if a Family has just one person taking classes, you still
would want a Detail record to show how the payment is allocated. With
several students in one family it would of course be necessary to do it that
way. In either case, the ClassPayments record is the one used for the
deposit ticket (unless you require a separate check for each student, which
would be unnecessary for the database and inconvenient for the client).

Making some forms makes sense for parts of the database where you are clear
on the design. Good luck. Keep us posted on your progress.

Nomy wrote:
Bruce,
Thanks for the idea, I'll have some time later today to try it. A stand
alone form for non class payments I can see working but I would have to have
another form based on tblpayments with the fields you list and have a
subform based on class enrollment to allocate the payment as a parent could
pay make a single payment for two students in different classes.

I've started to build a couple of small basic forms to test the suggestions
I've received in this newsgroup.

Thanks.
Nancy

Actually, I kind of wish I had not suggested a separate table for
non-class

[quoted text clipped - 60 lines]
Thank you in advanch so much.
Nancy


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...esign/201003/1

  #19  
Old March 1st, 2010, 08:07 PM posted to microsoft.public.access.tablesdbdesign
Nomy
external usenet poster
 
Posts: 11
Default Dance Classes

Bruce,
Thank you so much for your help. I will post back in a few days. You are
so nice to do this for people.
Nancy


"BruceM via AccessMonster.com" [email protected] wrote in message
news:[email protected]
The Family table is one of the top-level tables. Below that is the
ClassPayments table, followed by PaymentDetails to show how the payment
was
allocated. Even if a Family has just one person taking classes, you still
would want a Detail record to show how the payment is allocated. With
several students in one family it would of course be necessary to do it
that
way. In either case, the ClassPayments record is the one used for the
deposit ticket (unless you require a separate check for each student,
which
would be unnecessary for the database and inconvenient for the client).

Making some forms makes sense for parts of the database where you are
clear
on the design. Good luck. Keep us posted on your progress.

Nomy wrote:
Bruce,
Thanks for the idea, I'll have some time later today to try it. A stand
alone form for non class payments I can see working but I would have to
have
another form based on tblpayments with the fields you list and have a
subform based on class enrollment to allocate the payment as a parent
could
pay make a single payment for two students in different classes.

I've started to build a couple of small basic forms to test the
suggestions
I've received in this newsgroup.

Thanks.
Nancy

Actually, I kind of wish I had not suggested a separate table for
non-class

[quoted text clipped - 60 lines]
Thank you in advanch so much.
Nancy


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...esign/201003/1



  #20  
Old March 1st, 2010, 09:34 PM posted to microsoft.public.access.tablesdbdesign
BruceM via AccessMonster.com
external usenet poster
 
Posts: 448
Default Dance Classes

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

Nomy wrote:
Bruce,
Thank you so much for your help. I will post back in a few days. You are
so nice to do this for people.
Nancy

The Family table is one of the top-level tables. Below that is the
ClassPayments table, followed by PaymentDetails to show how the payment

[quoted text clipped - 33 lines]
Thank you in advanch so much.
Nancy


--
Message posted via http://www.accessmonster.com

 




Thread Tools
Display Modes

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 04:16 PM.


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