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  

Help finding information on Database design



 
 
Thread Tools Display Modes
  #1  
Old January 14th, 2005, 01:20 AM
Joe Cilinceon
external usenet poster
 
Posts: n/a
Default Help finding information on Database design

I'm looking for some information on how to best design a transaction table
for monthly payments in regards to rentals. The specifics things I can think
of is each account has a account number and each unit has a number. Payments
are posted using both the account number and unit number. Payments can also
be made by check, cash, credit card, money order, or combinations of all the
above. The payment will have to be divided up into things like rent late
fees, merchandise purchase (locks). It is also possible that we receive a
payment that is short (Previous Balance Due) or over (Credits). Every one is
due on the 1st of the month so PaidFrom and PaidThru dates, late fees etc
are all based on this Ledger. It is also possible that some of the charges
could be waved, which further complicates it. Oh and the unit/custno combo
is subject to change in that a tenant may vacate one unit but still have one
and another tenant move into the old unit all within the same day. Hope that
is enough information to get me started on this little project.

--
Thanks

Joe Cilinceon



  #2  
Old January 14th, 2005, 02:02 PM
Jeff Boyce
external usenet poster
 
Posts: n/a
Default

Joe

Just an aside, it seems like you are willing to spend considerable effort
re-designing and re-inventing the wheel. Have you already ruled out using
one of the commercial accounting/bookkeeping packages?

--
Good luck

Jeff Boyce
Access MVP

"Joe Cilinceon" wrote in message
...
I'm looking for some information on how to best design a transaction table
for monthly payments in regards to rentals. The specifics things I can

think
of is each account has a account number and each unit has a number.

Payments
are posted using both the account number and unit number. Payments can

also
be made by check, cash, credit card, money order, or combinations of all

the
above. The payment will have to be divided up into things like rent late
fees, merchandise purchase (locks). It is also possible that we receive a
payment that is short (Previous Balance Due) or over (Credits). Every one

is
due on the 1st of the month so PaidFrom and PaidThru dates, late fees etc
are all based on this Ledger. It is also possible that some of the charges
could be waved, which further complicates it. Oh and the unit/custno combo
is subject to change in that a tenant may vacate one unit but still have

one
and another tenant move into the old unit all within the same day. Hope

that
is enough information to get me started on this little project.

--
Thanks

Joe Cilinceon




  #3  
Old January 14th, 2005, 02:31 PM
Joe Cilinceon
external usenet poster
 
Posts: n/a
Default

Yes, we have looked at several deacated to the storage business and all are
out of our budget till late next year. In the meantime we are using old
paper ledgers which is just not getting it. I done quite a bit already to
get this onto a modern system however I still have much to do.

Joe


"Jeff Boyce" -DISCARD_HYPHEN_TO_END wrote in message
...
Joe

Just an aside, it seems like you are willing to spend considerable effort
re-designing and re-inventing the wheel. Have you already ruled out using
one of the commercial accounting/bookkeeping packages?

--
Good luck

Jeff Boyce
Access MVP

"Joe Cilinceon" wrote in message
...
I'm looking for some information on how to best design a transaction
table
for monthly payments in regards to rentals. The specifics things I can

think
of is each account has a account number and each unit has a number.

Payments
are posted using both the account number and unit number. Payments can

also
be made by check, cash, credit card, money order, or combinations of all

the
above. The payment will have to be divided up into things like rent late
fees, merchandise purchase (locks). It is also possible that we receive a
payment that is short (Previous Balance Due) or over (Credits). Every one

is
due on the 1st of the month so PaidFrom and PaidThru dates, late fees etc
are all based on this Ledger. It is also possible that some of the
charges
could be waved, which further complicates it. Oh and the unit/custno
combo
is subject to change in that a tenant may vacate one unit but still have

one
and another tenant move into the old unit all within the same day. Hope

that
is enough information to get me started on this little project.

--
Thanks

Joe Cilinceon






  #4  
Old January 14th, 2005, 03:54 PM
Jeff Boyce
external usenet poster
 
Posts: n/a
Default

I asked because Quickbooks, among others, came to mind. I believe Microsoft
"adopted" Great Plains accounting software also, and may have reasonable
costs.

Have you factored in the value of your time?

Jeff Boyce
Access MVP

"Joe Cilinceon" wrote in message
...
Yes, we have looked at several deacated to the storage business and all

are
out of our budget till late next year. In the meantime we are using old
paper ledgers which is just not getting it. I done quite a bit already to
get this onto a modern system however I still have much to do.

Joe


"Jeff Boyce" -DISCARD_HYPHEN_TO_END wrote in message
...
Joe

Just an aside, it seems like you are willing to spend considerable

effort
re-designing and re-inventing the wheel. Have you already ruled out

using
one of the commercial accounting/bookkeeping packages?

--
Good luck

Jeff Boyce
Access MVP

"Joe Cilinceon" wrote in message
...
I'm looking for some information on how to best design a transaction
table
for monthly payments in regards to rentals. The specifics things I can

think
of is each account has a account number and each unit has a number.

Payments
are posted using both the account number and unit number. Payments can

also
be made by check, cash, credit card, money order, or combinations of

all
the
above. The payment will have to be divided up into things like rent

late
fees, merchandise purchase (locks). It is also possible that we receive

a
payment that is short (Previous Balance Due) or over (Credits). Every

one
is
due on the 1st of the month so PaidFrom and PaidThru dates, late fees

etc
are all based on this Ledger. It is also possible that some of the
charges
could be waved, which further complicates it. Oh and the unit/custno
combo
is subject to change in that a tenant may vacate one unit but still

have
one
and another tenant move into the old unit all within the same day. Hope

that
is enough information to get me started on this little project.

--
Thanks

Joe Cilinceon







  #5  
Old January 14th, 2005, 04:01 PM
Joe Cilinceon
external usenet poster
 
Posts: n/a
Default

I'm cheap and get paid regardless, joking of course. This is a real small
operation of just my wife and I but will look into it.


--
Joe Cilinceon


"Jeff Boyce" -DISCARD_HYPHEN_TO_END wrote in message
...
I asked because Quickbooks, among others, came to mind. I believe
Microsoft
"adopted" Great Plains accounting software also, and may have reasonable
costs.

Have you factored in the value of your time?

Jeff Boyce
Access MVP

"Joe Cilinceon" wrote in message
...
Yes, we have looked at several deacated to the storage business and all

are
out of our budget till late next year. In the meantime we are using old
paper ledgers which is just not getting it. I done quite a bit already to
get this onto a modern system however I still have much to do.

Joe


"Jeff Boyce" -DISCARD_HYPHEN_TO_END wrote in
message
...
Joe

Just an aside, it seems like you are willing to spend considerable

effort
re-designing and re-inventing the wheel. Have you already ruled out

using
one of the commercial accounting/bookkeeping packages?

--
Good luck

Jeff Boyce
Access MVP

"Joe Cilinceon" wrote in message
...
I'm looking for some information on how to best design a transaction
table
for monthly payments in regards to rentals. The specifics things I can
think
of is each account has a account number and each unit has a number.
Payments
are posted using both the account number and unit number. Payments can
also
be made by check, cash, credit card, money order, or combinations of

all
the
above. The payment will have to be divided up into things like rent

late
fees, merchandise purchase (locks). It is also possible that we
receive

a
payment that is short (Previous Balance Due) or over (Credits). Every

one
is
due on the 1st of the month so PaidFrom and PaidThru dates, late fees

etc
are all based on this Ledger. It is also possible that some of the
charges
could be waved, which further complicates it. Oh and the unit/custno
combo
is subject to change in that a tenant may vacate one unit but still

have
one
and another tenant move into the old unit all within the same day.
Hope
that
is enough information to get me started on this little project.

--
Thanks

Joe Cilinceon









  #6  
Old January 14th, 2005, 04:13 PM
Brendan Reynolds
external usenet poster
 
Posts: n/a
Default

Another way of looking at that, Joe, is that this project is occupying 50%
of your workforce! :-)

The value of your time, of course, is how much you would have made if you
had spent it doing, um, whatever you would have been doing if you hadn't
been doing this! :-)

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.


"Joe Cilinceon" wrote in message
...
I'm cheap and get paid regardless, joking of course. This is a real small
operation of just my wife and I but will look into it.


--
Joe Cilinceon


"Jeff Boyce" -DISCARD_HYPHEN_TO_END wrote in message
...
I asked because Quickbooks, among others, came to mind. I believe
Microsoft
"adopted" Great Plains accounting software also, and may have reasonable
costs.

Have you factored in the value of your time?

Jeff Boyce
Access MVP

"Joe Cilinceon" wrote in message
...
Yes, we have looked at several deacated to the storage business and all

are
out of our budget till late next year. In the meantime we are using old
paper ledgers which is just not getting it. I done quite a bit already
to
get this onto a modern system however I still have much to do.

Joe


"Jeff Boyce" -DISCARD_HYPHEN_TO_END wrote in
message
...
Joe

Just an aside, it seems like you are willing to spend considerable

effort
re-designing and re-inventing the wheel. Have you already ruled out

using
one of the commercial accounting/bookkeeping packages?

--
Good luck

Jeff Boyce
Access MVP

"Joe Cilinceon" wrote in message
...
I'm looking for some information on how to best design a transaction
table
for monthly payments in regards to rentals. The specifics things I
can
think
of is each account has a account number and each unit has a number.
Payments
are posted using both the account number and unit number. Payments
can
also
be made by check, cash, credit card, money order, or combinations of

all
the
above. The payment will have to be divided up into things like rent

late
fees, merchandise purchase (locks). It is also possible that we
receive

a
payment that is short (Previous Balance Due) or over (Credits). Every

one
is
due on the 1st of the month so PaidFrom and PaidThru dates, late fees

etc
are all based on this Ledger. It is also possible that some of the
charges
could be waved, which further complicates it. Oh and the unit/custno
combo
is subject to change in that a tenant may vacate one unit but still

have
one
and another tenant move into the old unit all within the same day.
Hope
that
is enough information to get me started on this little project.

--
Thanks

Joe Cilinceon











  #7  
Old January 14th, 2005, 08:10 PM
Joe Cilinceon
external usenet poster
 
Posts: n/a
Default

Yes, I know but the powers that be said no until the end of next year.
Things like concrete work, new doors, new roofs, our salaries etc have all
the money spent until the last quarter of next year. Now with that said the
only time problem is filling it. This is a small 455 unit storage facility
that is pretty stable for 15 years now. With my wife and I it only requires
about 2 real days of work out of a normal week, my time isn't that filled.

To give you a better idea of what we took over on Oct 1 of this year. No
computer, paper ledgers, hand written letters and such. Since Nov. I've
managed to get it converted over to a very simple Access database system
that now handles most of the things that where done in the past by hand.

I do own Office XP Developers. I'm also a retired systems analyst, though it
was with a school system and had little to do with computers. I've not
programmed since DOS and Windows 3.1 and then nothing major. I also have
enjoyed doing what I've done so far and would love to take it all the way if
for no other reason than just self satisfaction of learning new things even
at my age.

What I've done so far is get the tenant records finished and working very
smooth. These include all information including contact info. The unit
tables are done and tied in with the tenants files so we know who is in what
unit as well as well as what each person pays for their units. The payment
side of this is where I am at now even though the method used at the moment
is getting the results. With what I have written so far, we generate about
20 reports required by the investors that where done by hand, as well as
late notices, invoices, payment receipts, auction letters, deposit slips. I
have even added a competition survey section as well as payroll, bank
deposits, credit card tracking. I even have a reservation system, waiting
list, and the ability to scan tenant id into the system. I send the reports
now by email in PDF format to the investors. All of this has taken me since
Nov 1 to accomplish.

Now, I want to bring the payment side up to more than a simple spreadsheet
that requires us to enter every detail of a payment. It works just like the
paper system did but is very cumbersome though more accurate than the paper
system. This ledger has a Transaction number (autonumber) custno, unitno,
date paid, how much, payment method, tracking number (check number, credit
card approval code), dates if from and thru. It gets very had to do when you
get partial payments, split payments (cash and check etc), over payments
that don't cover a full month, so we wind up with a credit. We also have to
deal with bounced checks, units in lien, auctioned units (sold to general
public at auction). etc.

Well I didn't mean to go on this long but I've really been frustrated in
finding examples for this aspect of database applications. It seems all
examples are for the same things and nothing on accounting at all. If I need
to track inventory, ship goods or had to list suppliers I would be covered.
g


--

Joe Cilinceon


"Brendan Reynolds" brenreyn at indigo dot ie wrote in message
...
Another way of looking at that, Joe, is that this project is occupying 50%
of your workforce! :-)

The value of your time, of course, is how much you would have made if you
had spent it doing, um, whatever you would have been doing if you hadn't
been doing this! :-)

--
Brendan Reynolds (MVP)


SNIPPED for size



  #8  
Old January 14th, 2005, 10:20 PM
Brendan Reynolds
external usenet poster
 
Posts: n/a
Default

That sounds very ambitious, Joe. Don't you think that the end of next year
will have come and gone long before all that is finished?

Mind you, if you have the time, and you're enjoying it, good luck to you.

There is a potential 'middle way' in between buying an off-the-shelf package
and doing it all yourself, which is to buy a package with source code
included that you can customize to your needs. Here's one of the best known
....

http://www.databasecreations.com/

And Tony Toews has a list of such at

http://www.granite.ab.ca/accsacct.htm

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.


"Joe Cilinceon" wrote in message
...
Yes, I know but the powers that be said no until the end of next year.
Things like concrete work, new doors, new roofs, our salaries etc have all
the money spent until the last quarter of next year. Now with that said
the
only time problem is filling it. This is a small 455 unit storage facility
that is pretty stable for 15 years now. With my wife and I it only
requires
about 2 real days of work out of a normal week, my time isn't that filled.

To give you a better idea of what we took over on Oct 1 of this year. No
computer, paper ledgers, hand written letters and such. Since Nov. I've
managed to get it converted over to a very simple Access database system
that now handles most of the things that where done in the past by hand.

I do own Office XP Developers. I'm also a retired systems analyst, though
it
was with a school system and had little to do with computers. I've not
programmed since DOS and Windows 3.1 and then nothing major. I also have
enjoyed doing what I've done so far and would love to take it all the way
if
for no other reason than just self satisfaction of learning new things
even
at my age.

What I've done so far is get the tenant records finished and working very
smooth. These include all information including contact info. The unit
tables are done and tied in with the tenants files so we know who is in
what
unit as well as well as what each person pays for their units. The payment
side of this is where I am at now even though the method used at the
moment
is getting the results. With what I have written so far, we generate about
20 reports required by the investors that where done by hand, as well as
late notices, invoices, payment receipts, auction letters, deposit slips.
I
have even added a competition survey section as well as payroll, bank
deposits, credit card tracking. I even have a reservation system, waiting
list, and the ability to scan tenant id into the system. I send the
reports
now by email in PDF format to the investors. All of this has taken me
since
Nov 1 to accomplish.

Now, I want to bring the payment side up to more than a simple spreadsheet
that requires us to enter every detail of a payment. It works just like
the
paper system did but is very cumbersome though more accurate than the
paper
system. This ledger has a Transaction number (autonumber) custno, unitno,
date paid, how much, payment method, tracking number (check number, credit
card approval code), dates if from and thru. It gets very had to do when
you
get partial payments, split payments (cash and check etc), over payments
that don't cover a full month, so we wind up with a credit. We also have
to
deal with bounced checks, units in lien, auctioned units (sold to general
public at auction). etc.

Well I didn't mean to go on this long but I've really been frustrated in
finding examples for this aspect of database applications. It seems all
examples are for the same things and nothing on accounting at all. If I
need
to track inventory, ship goods or had to list suppliers I would be
covered.
g


--

Joe Cilinceon


"Brendan Reynolds" brenreyn at indigo dot ie wrote in message
...
Another way of looking at that, Joe, is that this project is occupying
50%
of your workforce! :-)

The value of your time, of course, is how much you would have made if you
had spent it doing, um, whatever you would have been doing if you hadn't
been doing this! :-)

--
Brendan Reynolds (MVP)


SNIPPED for size





  #9  
Old January 14th, 2005, 11:22 PM
Joe Cilinceon
external usenet poster
 
Posts: n/a
Default

WOW and I thought the storage software was expensive. If I could get the
accounting packages we could get the complete storage package. Thanks again.

--

Joe Cilinceon


"Brendan Reynolds" brenreyn at indigo dot ie wrote in message
...
That sounds very ambitious, Joe. Don't you think that the end of next year
will have come and gone long before all that is finished?

Mind you, if you have the time, and you're enjoying it, good luck to you.

There is a potential 'middle way' in between buying an off-the-shelf

package
and doing it all yourself, which is to buy a package with source code
included that you can customize to your needs. Here's one of the best

known
...

http://www.databasecreations.com/

And Tony Toews has a list of such at

http://www.granite.ab.ca/accsacct.htm

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.


"Joe Cilinceon" wrote in message
...
Yes, I know but the powers that be said no until the end of next year.
Things like concrete work, new doors, new roofs, our salaries etc have

all
the money spent until the last quarter of next year. Now with that said
the
only time problem is filling it. This is a small 455 unit storage

facility
that is pretty stable for 15 years now. With my wife and I it only
requires
about 2 real days of work out of a normal week, my time isn't that

filled.

To give you a better idea of what we took over on Oct 1 of this year. No
computer, paper ledgers, hand written letters and such. Since Nov. I've
managed to get it converted over to a very simple Access database system
that now handles most of the things that where done in the past by hand.

I do own Office XP Developers. I'm also a retired systems analyst,

though
it
was with a school system and had little to do with computers. I've not
programmed since DOS and Windows 3.1 and then nothing major. I also have
enjoyed doing what I've done so far and would love to take it all the

way
if
for no other reason than just self satisfaction of learning new things
even
at my age.

What I've done so far is get the tenant records finished and working

very
smooth. These include all information including contact info. The unit
tables are done and tied in with the tenants files so we know who is in
what
unit as well as well as what each person pays for their units. The

payment
side of this is where I am at now even though the method used at the
moment
is getting the results. With what I have written so far, we generate

about
20 reports required by the investors that where done by hand, as well as
late notices, invoices, payment receipts, auction letters, deposit

slips.
I
have even added a competition survey section as well as payroll, bank
deposits, credit card tracking. I even have a reservation system,

waiting
list, and the ability to scan tenant id into the system. I send the
reports
now by email in PDF format to the investors. All of this has taken me
since
Nov 1 to accomplish.

Now, I want to bring the payment side up to more than a simple

spreadsheet
that requires us to enter every detail of a payment. It works just like
the
paper system did but is very cumbersome though more accurate than the
paper
system. This ledger has a Transaction number (autonumber) custno,

unitno,
date paid, how much, payment method, tracking number (check number,

credit
card approval code), dates if from and thru. It gets very had to do when
you
get partial payments, split payments (cash and check etc), over payments
that don't cover a full month, so we wind up with a credit. We also have
to
deal with bounced checks, units in lien, auctioned units (sold to

general
public at auction). etc.

Well I didn't mean to go on this long but I've really been frustrated in
finding examples for this aspect of database applications. It seems all
examples are for the same things and nothing on accounting at all. If I
need
to track inventory, ship goods or had to list suppliers I would be
covered.
g


--

Joe Cilinceon


"Brendan Reynolds" brenreyn at indigo dot ie wrote in message
...
Another way of looking at that, Joe, is that this project is occupying
50%
of your workforce! :-)

The value of your time, of course, is how much you would have made if

you
had spent it doing, um, whatever you would have been doing if you

hadn't
been doing this! :-)

--
Brendan Reynolds (MVP)


SNIPPED for size







 




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

Similar Threads
Thread Thread Starter Forum Replies Last Post
Free Access Training Timboo New Users 8 August 17th, 2005 05:58 PM
Exclusive access to the database Steve Huff General Discussion 17 December 24th, 2004 07:23 PM
Unable to open Database in Design view. Weeksrw General Discussion 4 September 25th, 2004 09:54 PM
finding design examples for Invoices and Payments Paul James Database Design 2 June 9th, 2004 07:14 PM
How do I design a database based on the information that will be stored? - Copy of Tables and hirearchies.zip (0/1) Jim Database Design 1 June 1st, 2004 01:44 PM


All times are GMT +1. The time now is 06:43 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.