Please Help

[Joe Cilinceon]

I've been trying to get some help on how to best tag a record in a payment
table to show a check has bounced. Now I don't want the record to delete the
payment as if it never happened for obvious reasons. I would like a method
that I could easily access from a report or query.

What I have is 2 tables one called Ledger that has one record per
transaction and a child called payments which can have more than one payment
method. This system allows for split payments such as cash and check
payments. In such a case I would like to flag just the check as the cash
would then become a credit.

 

[chris.nebinger]

A quick way would be to have a new type of payment type:

Cash
Credit Card
Check: Pending
Check: Approved
Check: Returned NSF

Something like that would work. Or, add a new field to your payments
table that lists possible states of payment.

 

[Joe Cilinceon]

A quick way would be to have a new type of payment type:

Cash
Credit Card
Check: Pending
Check: Approved
Check: Returned NSF

Something like that would work. Or, add a new field to your payments
table that lists possible states of payment.

Thanks Chris for answering. I'm just wondering which table now would be best
to add it too. My tables are setup as follows:

Ledger is a single record per transaction. It holds date paid, total amount
paid, period it pays for and break down of charges it pays.
Payments can have multiple records. It holds amount, method of payment,
tracking number (check numbers etc). This can have multiple records as we
often get paid by both check and cash on a single transaction.

 

[Joe Cilinceon]

Thanks Chris for answering. I'm just wondering which table now would
be best to add it too. My tables are setup as follows:

Ledger is a single record per transaction. It holds date paid, total
amount paid, period it pays for and break down of charges it pays.
Payments can have multiple records. It holds amount, method of
payment, tracking number (check numbers etc). This can have multiple
records as we often get paid by both check and cash on a single
transaction.

I had another thought on this also that might help explain it. I would also
need to keep the date is the payment was reversed. Now in the form that
would access the tenants ledger once I flagged a payment as reversed it
would need to do some changed to the account. For example say someone owed a
$100 and gave me a check for $75 and cash for $25 to cover their rent for
next month and the check bounced. It would need to roll back the paidfrom
and paidthru date. Mark the check as NSF adjust the amount paid in the
original transaction, give a credit of $25 for the cash portion of the
payment. Add an NSF fee. Now once that is corrected the next time the
payment screen would be pulled up is would show these things automatically
based on the paidfrom paidthru date. It would also add any late fees due
based on the current date. This would also have to apply if say the tenant
had made another payment before we where notified of an NSF check.

I'm thinking I need to add 1 more fields to the either the Payment or the
Ledger (not sure which would be best) for the Rev Date and add 2 new payment
options. I would need the one for a check but yet another for a returned
credit card. I could just add an additional field with a check box for
Reversed.

Thanks again Chris

 

[Jeff Boyce]

Joe

It sounds like you are trying to use Access to do accounting. If you don't
have a lot of time to devote to this effort, consider picking up one of the
off-the-shelf accounting packages...

(and if this is a "labor of love", been there!)

Regards

Jeff Boyce
<Office/Access MVP>

 

[Joe Cilinceon]

Joe

It sounds like you are trying to use Access to do accounting. If you
don't have a lot of time to devote to this effort, consider picking
up one of the off-the-shelf accounting packages...

(and if this is a "labor of love", been there!)

Regards

Jeff Boyce
<Office/Access MVP>

Actually Jeff I got what I was going for working now. I simply wanted a
simple method to flag a payment record. Once the record is flagged I can
then generate NSF letter, a note on the payment screen to not accept check
from the tenant, roll back the paid thru date if applicable. All of this is
done at the time I pull up the transaction and simply change the payment
method from a Check to a NSF Check. I also implemented it for credit cards
that might be reversed though rare. I knew how to program it but I wasn't
sure of how I wanted to implement a flag field for it and was asking for
some fresh ideas. As it turned out I only added a single field for the
RevDate to my payment table. Thanks for your response though, it is
appreciated. g

Now about the doing accounting with access. I almost stopped using these
groups when I started this project back in Oct 2004. It seems every time I
mentioned the word payments I got "the off-the-shelf accounting packages"
pitch. Well after looking at the top 10 dedicated packages for the
mini-storage business, I came to a couple of conclusions. The first being
that 8 of the 10 are written in Access and of those all have the accounting
using standard Access features. I've been using my little project on line in
the real world now since Dec 2004 with no errors in accounting documented to
date (about 6000 transactions to date). I also might add that an of the
shelf accounting package is a bit of over kill and will not handle many of
the things we need in this business. Please realize that I don't mean to
come across a jumping on you, I'm just a little curious as to why the off
the shelf answer so much in these groups. g

 

[Jeff Boyce]

Joe

No offense taken ... and speaking only for myself, the learning curve to get
Access to do accounting stuff is quite a bit steeper than picking up a
QuickBooks, or a Peachtree or a Great Plains package.

For me, it comes down to the question of whether the tool is the best fit
for the job. By all means, congratulations on your successful use of Access
to help in your situation.