OK, sorry about the delay. I've come up with a couple of suggestions,
but since you said, "This is a fully functional program which is used
daily," it appears that you need to be pretty careful about making
changes, since it would be easy to do more harm than good.
Incidentally, to answer your original question, yes it should be pretty
easy to calculate the total amount due from a tenant. But keeping the
amounts due in an easy-to-use format can simplify the job.
I noticed a couple of things you might want to look at. These are
changes that I made in my version of your database, to some extent for
cosmetic purposes, as I think it's important to have names match what
they stand for. That makes wrong constructs look more obviously wrong,
so that they can be corrected or avoided.
The [LEASES].[LedgerID] field doesn't need to be stored, as it can be
computed. For example, it could be calculated as
[TENANTS]![CustNo] & Format$([LEASES]!UnitNo,"000")
& Format$([LEASES]![StartDate],"yyyymmdd")
I set the primary key in [LEASES] to the set of 3 fields
[LEASES].[CustNo], [LEASES].[UnitNo], and [LEASES].[StartDate].
Fields [LEASES].[InLien] and [LEASES].[FreeMonth] were referred to in
SQL but not described in your message. How do you intend to use them?
Where do the values come from?
I created a new field, [LEASES].[Leases_ID], to track lease records,
supplanting [LEASES].[LedgerID] (which doesn't follow my naming
convention, as it doesn't identify a record in [LEDGER]). Of course,
you may use any names you wish that Access will accept, but it's easier
for me to give advice if the names suggest what the contents are
supposed to be. For example, I would normally set the primary key of a
Table named [Abcde] to be a random Autonumber field called [Abcde_ID],
so that in any other Table, I can tell at a glance that the field
[Abcde_ID] is a Number field referring to Table [Abcde]. That makes it
easy to link Tables without much risk of using the wrong fields to do
the linking (which would cause trouble).
For that reason, I renamed [CHARGES].[ChgID] to [CHARGES].[Charges_ID],
to more easily track where it belongs.
[qryBalanceDue].[Unit] I changed to [qryBalanceDue].[UnitNo]; both
spellings appear in what you posted, and I assume both refer to a
3-digit number that is posted on the outside of a storage locker.
I added a new primary key field, [TENANTS].[Tenants_ID], to supplement
[TENANTS].[CustNo].
I switched relationships.
- I added a new foreign key, [LEASES].[Tenants_ID], intended to
replace [LEASES].[CustNo].
- I used [Q_Update_Tenants_ID] to update the corresponding foreign key
[LEASES].[Tenants_ID].
- I deleted the old relationship.
- I set [TENANTS].[Tenants_ID] as the new primary key.
- I set a new relationship in the Relationships Table between the two
[Tenants_ID] fields.
- There being no further need for the now-redundant foreign key
[LEASES].[CustNo], I deleted that field, keeping [TENANTS].[CustNo] for
reference purposes.
I did the same thing to [UNITS].[UnitNo], replacing it with
[UNITS].[Units_ID].
Do fields such as [TENANTS].[NSFFee] and [TENANTS].[UnPaidDue] really
describe a quality of a tenant? For example, if [NSFFee] relates to an
amount of money that you charge if a check is returned (I'm just
guessing here; you didn't say), is it different for different tenants?
Can the amount that you charge a given tenant for such events change?
If so, how do you track the changes? Is [UnPaidDue] another type of
service charge, that is different from some tenants from what it is for
others? If they are indeed different for different tenants, are there
just a few such values (corresponding to different classes of tenants),
or do they vary all over the place? My guess is that there are only a
few such possible values, as spelled out in your contracts, and that you
could put those values into another Table, to which [TENANTS] could
link. What you've done isn't wrong, it just involves what I think is
more work to maintain it than you need.
Is [LEASES].[Rent] ever different from [UNITS].[Rate] of the linked
[UNIT]? When the rental rate for a unit changes, how do you keep track
of when it changed, and to what new amount? I'd expect to see that in
some other Table (linked to [UNITS]). If you don't keep track of when
[UNITS].[Rate] changed, but you do make a change to it, not only is
[LEASES].[Rent] wrong (until you change it, too), but previous payments
on that unit now appear to be have been wrong as well, even though they
probably were OK at the time.
Since you didn't specify what functions like GetsFee2() do, I can't give
much advice on them. I know you didn't ASK for any such advice, but for
my purposes, I assumed they'd always return a result of False. However,
they appear to affect the amount due, so more details about them would
make it easier to suggest ways to calculate the total amount due.
-- Vincent Johns <
[email protected]>
Please feel free to quote anything I say here.
Joe said:
Thank Vincent I'll mess with it myself over the next day or two.
Vincent said:
Thanks for the additional information... I don't have time to do much
more on it now, will probably get back to you in a couple of days.
-- Vincent Johns <
[email protected]>
Please feel free to quote anything I say here.
Joe Cilinceon wrote:
Joe said:
Thanks Vincent. Below is a layout of the important stuff (I'll leave out
none needed fields as this program has really grown in size and shape)
TENANTS
CustNo
AccountName (name on the account for example Company Name or LastName,
FirstName)
rest of the typical customer/tenant type data not used for any of this.
UNITS (stores all storage units with each unit having a unique number)
UnitNo ( is a 3 digit number as we only have 455 units)
Type (links to another table not used here but covers size, AC / Non AC,
Parking etc
Rate (Montly rental rate)
LEASES ( ties the Tenant and Unit together)
LedgerID (a unique text fields made up of the CustNo & UnitNo & Date of
Rental in the yyyymmdd format. So CustNo 150 in unit 100 renting
11/1/05 would look like "15010020001101"
CustNo ties to Tenant table
UnitNo ties to Units
StartDate (date moved in or signed the lease)
EndDate (Is Null unless vacated and then not used)
Now comes the fun stuff
LEDGER (This is the main start of the money trail)
Transaction (every time a payment is posted it gets a unique auto number)
LedgerID (Ties this to a CustNo/UnitNo combo)
PaymentDate (Date of posted payment)
RentRate (if rent is paid it holds the current rate as price can change. I
will want to know how much was paid at this date)
PaidFrom (Now this and PaidThru are only used when a unit is being paid and
are the only ones used in the qryBalanceDue
PaidThru
PAYMENTS (Not used but might help understand)
Transaction
PayAmt
PayType
Tracking (for check # etc)
RevDate (used when a check bounces or a payment is reversed)
CHARGES
Transaction
ChgID
ChgAmt
ExplainID ' Used lookup explanation table
QUERY starts here with 2 queries
qryCURRENT (SQL below)
SELECT LEASES.LedgerID, LEASES.EndDate, Units.Status,
Max(Ledger.PaymentDate) AS MaxOfPaymentDate, Max(Ledger.PaidFrom) AS
MaxOfPaidFrom, Max(Ledger.PaidThru) AS MaxOfPaidThru
FROM (Ledger INNER JOIN LEASES ON Ledger.LedgerID = LEASES.LedgerID) INNER
JOIN Units ON LEASES.Unit = Units.Unit
GROUP BY LEASES.LedgerID, LEASES.EndDate, Units.Status
HAVING (((LEASES.EndDate) Is Null) AND ((Units.Status)=1)); ' Units.Status
woud be rented
qryBALANCEDUE
SELECT qryCurrent.LedgerID, LEASES.Unit, LEASES.CustNo,
qryCurrent.MaxOfPaymentDate, qryCurrent.MaxOfPaidFrom,
qryCurrent.MaxOfPaidThru, LEASES.Rate, TENANTS.NSFFee, TENANTS.Credits,
TENANTS.UnPaidDue, DOMth([MaxOfPaidThru],3) AS NewPaidFrom,
IIf([NewPaidFrom]>Date(),domth([NewPaidFrom],2),domth(Date(),2)) AS
NewPaidThru, RentDue([NewPaidFrom],[NewPaidThru],[Rate]) AS RentOwed,
IIf(GetsFee1([MaxOfPaidThru])=True,18) AS LateFee1,
IIf(GetsFee2([MaxOfPaidThru])=True,18) AS LateFee2,
IIf(GetsFee3([MaxOfPaidThru])=True,18) AS LateFee3,
DateDiff("d",[MaxOfPaidThru],Date()) AS Days, LEASES.InLien,
IIf([InLien]=True,25) AS LCFee, IIf([InLien]=True,60) AS ActFee,
Nz([LateFee1],0)+Nz([LateFee2],0)+Nz([LateFee3],0)+Nz([LCFee],0)+Nz([ActFee],0)
AS TotalFeesOwed, [RentOwed]+[TotalFeesOwed] AS TotalDue,
DateDiff("m",[MaxOfPaidThru],Date()) AS Months, LEASES.FreeMonth,
IIf([MaxOfPaidThru]<Date(),0,Round(ProrateOut(Date(),[Rate]),2))+[Refund] AS
Transfer,
IIf([MaxOfPaidThru]<Date(),0,Round(RefundAmt(Date(),[MaxOfPaidThru],[Rate],[FreeMonth]),0))
AS Refund,
IIf([MaxOfPaidThru]>Date(),0,Round(RentDue([MaxOfPaidThru],Date(),[Rate]),2)+[TotalFeesOwed])
AS BalanceDue
FROM (qryCurrent INNER JOIN LEASES ON qryCurrent.LedgerID = LEASES.LedgerID)
INNER JOIN TENANTS ON LEASES.CustNo = TENANTS.CustNo;
You will also see a few functions such as ProrateOut() and DOMth() these are
basically just functions I've written to handle basic math problems that
would be too large to fit in a query.
Now when we have a tenant with several units we usually get a single check
for the total. We now post it as separate payments using the amount for that
unit and the check number but each will have a separate transaction number.
I'm looking for a way on these type of payments that are correct (most are)
in what they pay to simple post it once and have the system divide it up
into a new transaction for each unit with the correct amount and tracking
number for the check or credit card approval code. These amount are all
based on qryBalanceDue.
This is a fully functional program which is used daily. I'm just adding some
features such as batch payments to make things a little faster with less
chance to screw-up.
Vincent Johns wrote:
<Cut for size>
