Real Rent Collected Query Needed

[Joe Cilinceon]

I'm looking for a method to query for the actual rent paid for given month.

Example:

The RentRate would be the monthly rental charge for a full month only. The
first month a tenant pays only for the days rented (prorated) so unless he
move ins on the 1st he will pay less than rent rate. Now we only prorate on
move in not move out, so if he leaves on the 5th he still pays the full
month. I now track the paid dates buy PaidFrom and PaidThru (will always be
the last day of a given month), but we do have many paid in advance.

I have a prorate function written that is based on RentRate/days (day move
in to last day of given month).

Tables with data:
LEASES
LedgerID (links to other table)
StartDate (day moved in)

LEDGER
Transaction (links to other tables)
PaidFrom
PaidThru
RentRate (This could change so the Rate is recorded at the time of any
payment)
PayAmt (this would be the total payment including months in advance, locks
and other charges)

 

[Jeff Boyce]

Joe

It may be only a semantic quibble, but "rent collected" implies "payment" to
me, not amount owed. Could you check in the payments table by date?

 

[Joe Cilinceon]

I'm not following your question about semantics. This is for getting amounts
collected not owed.

Maybe this will be easier to understand. Say a tenant rents a $30 (RentRate)
space on Jun 15 and pays for Jul and Aug at the same time. The single
transaction would show paidfrom (6/15/05) and paidthru (8/31/05) fields
updated. The paymentamount would be $76.00 and I need to know how to query
this to get just what was paid for say June. It should be $16.00 as that
would be the prorated amount collected for Jun on this space.

 

[Jeff Boyce]

Joe

From what you've written, it sounds like you have all the information you
need.

You need to know the monthly amount ($30).

You need to know start and end dates (6/15; 8/31).

You need to be able to determine that the start date is NOT the first of
that month (you could use the DateSerial() function to help with this).

You need to be able to determine what portion of the first month your start
date covers (15/30 X 30). Hmmm? this isn't $16.

You could calculate (i.e., confirm) that the total received ($76) is the sum
of the number of full months X monthly amount, plus the pro rata amount
during the first month (2 X 30, plus ?16).

You might need to use an IIF() statement in your query, or might need to
write/modify a user-written function to handle the calculation of partial
month amount.

Regards

Jeff Boyce
<Office/Access MVP>

 

[Joe Cilinceon]

Thanks Jeff I'll read it over and think about it a little more, maybe then
I understand what I need. Oh, and $16 would be correct. If you move in on
the 15th you pay for the 15th or 16 days total.

Joe

From what you've written, it sounds like you have all the information
you need.

You need to know the monthly amount ($30).

You need to know start and end dates (6/15; 8/31).

You need to be able to determine that the start date is NOT the first
of that month (you could use the DateSerial() function to help with
this).
You need to be able to determine what portion of the first month your
start date covers (15/30 X 30). Hmmm? this isn't $16.

You could calculate (i.e., confirm) that the total received ($76) is
the sum of the number of full months X monthly amount, plus the pro
rata amount during the first month (2 X 30, plus ?16).

You might need to use an IIF() statement in your query, or might need
to write/modify a user-written function to handle the calculation of
partial month amount.

Regards

Jeff Boyce
<Office/Access MVP>