A tough problem for me to crack

[Joe Cilinceon]

What I need is a method to handle multiple auto pay accounts with the
following setup:

TENANT (General tenant info is kept in other tables such as address, phone
numbers etc.
CustNo (Unique number to tie this table to all others.)

TENANTAP ( holds credit card data such as number, expire date etc)
CustNo (# field that ties to other tables.)

UNITS (holds storage space data)
UnitNo (number field)

LEDGER (holds all account data based on which tenant rents what spaces)
LedgerID (main field in this table 1 for each CustNo -> Unit combo)
CustNo
UnitNo

Now each tenant can have more than one Units creating a new LEDGER record
for each combination. However I store 1 credit card number under the CustNo.
I'm looking for a way to have to have unit paid or not paid by a given card.
I also don't want to store the credit card data more than once per tenant.
That is not to say I'm not open to multiple cards on a single tenant.

The only thought I've had so far is add another table with a link to a
credit card account and allow for multiple accounts.

 

[Vincent Johns]

What I need is a method to handle multiple auto pay accounts with the
following setup:
[...]

The only thought I've had so far is add another table with a link to a
credit card account and allow for multiple accounts.

That's not a bad idea... but why just credit-card accounts? Since your
tenants use various forms of payment, you might (if you need to do so)
store information on all of several forms of payment available to a
client. For example, do you track checking-account information? This
new Table might be a place to put that, too.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

 

[Joe Cilinceon]

That's not a bad idea... but why just credit-card accounts? Since
your tenants use various forms of payment, you might (if you need to
do so) store information on all of several forms of payment available
to a client. For example, do you track checking-account information?
This new Table might be a place to put that, too.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

Actually we don't. We rent storage space and the only reason for this is so
tenant can pay without having to remember when they are due.
I do think I might have a method that will work. Please let me know what you
think.

New table that holds the CustNo, (a new CrcdID unique number one per credit
card) and the LedgerID (used to tie a customer to an account.

I will need to add just 1 field to the LEASE table on the LedgerID for the
CrcdID #. As for inputting the information I can modify the existing form
for the credit cards by simply adding a CrcdID # and some SQL on update to
add the data to the Leases table.

 

[Albert D.Kallal]

I'm looking for a way to have to have unit paid or not paid by a given

card.

Just add a field to the units that stores the tenantap ID....

If it is null, then all of the fields such a s card type, expires date etc
will NOT show.

(I am assuming you build a query based on UNITS + TENANTAP to "display" all
that card info.).

If that new field in UNITS for the card info is blank, then no card is being
used.....

So, just store the ID of the TENANTAP reocrd in your units table.

 

[Joe Cilinceon]

Just add a field to the units that stores the tenantap ID....

If it is null, then all of the fields such a s card type, expires
date etc will NOT show.

(I am assuming you build a query based on UNITS + TENANTAP to
"display" all that card info.).

If that new field in UNITS for the card info is blank, then no card
is being used.....

So, just store the ID of the TENANTAP reocrd in your units table.

I have it done Albert. What I did was added 2 fields to the LEASES table.

LEASES
AP (check box if AutoPaid)
CrcdID (for card ID it is related too)

I added one field to the TENANTAP table for the CrcdID (autonumber). I
didn't have anything but the custno which only allowed for 1 credit card.
Now I can have as many as needed.

Now the way it works is really very simple in that I have a main form called
TENANTS
one of the options on this form is an AutoPay button. When it is hit another
form that links to the TenantAP table with the credit card data. If there is
an associated record/records it shows up otherwise you simply start typing.
There is also a subform on this form tied to the Leases with the AP check
box, unit number, and a hidden field for the crcdID. Now it is filter to
show only Leases associated with the current tenant. If I check the box it
ties that units charges to the credit card with focus. Oh and the reason for
the extra check box is to simplifies the selecting of units.