Help finding information on Database design

[Joe Cilinceon]

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.

 

[Jeff Boyce]

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?

 

[Joe Cilinceon]

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]

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]

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.

 

[Brendan Reynolds]

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]

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

 

[Brendan Reynolds]

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]

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.