Need help getting information

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Joe Cilinceon - 29 Nov 2005 15:05 GMT

I'm trying figure out a query or series of queries to give me data on units
we collected rent on more than once in a month. To help explain this a
little better we rent storage units. Now we prorate (charge by the day) the
first months rent, but after that we charge for full months with no refunds
on partial months used. In other words, if you vacate on the 15th of a month
and have paid for the month you get no refund on the unused portion of the
rent for that month. You would get a refund on unused months paid in
advance however.

With that said, we will often rent a unit again after a vacate under the
above circumstances. This is what I'm looking for is how many times it
happens in a given month. The table I figure will have that data is the
Lease table, the structure is show below:

LEASES
ledgerid (account #)
custno (renter)
unit (unit # rented)
startdate (date it was rented on)
enddate (date of vacate)
paidthru (date it was paid thru)

Now if the unit is still rented the enddate is a Null value, startdate will
always have a date. I hope that is clear, it was tough to explain.

Signature

Thanks

Joe Cilinceon

Reply to this Message

Chris2 - 29 Nov 2005 15:21 GMT

> I'm trying figure out a query or series of queries to give me data on units
> we collected rent on more than once in a month. To help explain this a
[quoted text clipped - 20 lines]
> Now if the unit is still rented the enddate is a Null value, startdate will
> always have a date. I hope that is clear, it was tough to explain.

Joe Cilinceon,

Could you pleas post your sample data and desired results?

Sincerely,

Chris O.

Reply to this Message

Joe Cilinceon - 29 Nov 2005 15:46 GMT

> Could you pleas post your sample data and desired results?
>
> Sincerely,
>
> Chris O.

Not sure I understand what you want but I'll try to give you an example.

Ok say John Doe rented Unit 44 on 10/1/05 and the rent was paid thru
11/30/05. Now John vacates on 11/15/05 and paid $30 for this months rent,
which we keep. Now we rent Unit 44 to Jack Johnson on 11/16/05 and collect
$15 for the the rest of this months rent (prorated). At this point Unit 44
has generated $45 in rent for this month. This is what I'm trying to write a
query to give me. The actual layout of the table is as show below. The
ledgerId is a unique code that ties a tenant to a unit as a tenant can rent
more than one unit or even rent a unit vacate and rent it again down the
road. CustNo is a unique code for each customer. Unit is unique number for
each storage unit. If there is a Leases record there will be a StartDate.
The EndDate will only have a date if it was vacated.

LEASES table
[LedgerID] (would be the account or CustNO to Unit combination)
[CustNo] (tenant's number)
[Unit] (Unit number)
[StartDate] (Move-In Date)
[EndDate] (date vacated and will be null if still rented)
[Rate] (monthly rate for this unit)
[Paidthru] (Rent is paid thru this date)

Signature

Thanks

Joe Cilinceon

Reply to this Message

Chris2 - 29 Nov 2005 16:05 GMT

> > Could you pleas post your sample data and desired results?
> >
[quoted text clipped - 24 lines]
> [Rate] (monthly rate for this unit)
> [Paidthru] (Rent is paid thru this date)

Joe,

What I'm looking for is:

DDL:

If you have the complete CREATE TABLE statement for your table, or
can write one for the rest of us, including all CONSTRAINTS,
providing that helps. The table structure description can be used,
but the time you save me on building your table is time I spend
solving your question instead of getting to the point where I can
begin to solve your question.

Your Sample Data:

LedgerID, CustNo, Unit, StartDate, EndDate, Rate, Paidthru
1, 1, 1, 01/01/2005, 02/01/2005, 50.00, 02/01/2005
etc.
etc.
etc.
etc.

You would be replacing the above will 5-10 key rows from your actual
data covering the cases necessary. When I create a copy of your
table to experiment with, I'll have something valid to load into it
(instead of something I guessed on).

Your Desired Results:

Col1, [Coln . . .]
<some value> , <other value>
<some value> , <other value>
<some value> , <other value>

Your output columns and the output data as you would expect to see
them upon the successful completion of the query. If you don't know
what you want out of this query, there is not much chance of me
guessing it. The narrative above was good, but let's be exact.

Although meant for an sqlserver newsgroup, the
following link is still applicable for MS Access:
http://www.aspfaq.com/etiquette.asp?id=5006, and
is excellent when it comes to detailing how to
provide the information that will best enable
others to answer your questions.

Sincerely,

Chris O.

Reply to this Message

Joe Cilinceon - 29 Nov 2005 16:51 GMT

> What I'm looking for is:
>
[quoted text clipped - 43 lines]
>
> Chris O.

Thanks Chris, the effort to help is appreciated. I can see though that I'm
lacking the language skills and etiquette required to ask a question like
this in this group. It seems to me that every time I ask a question that
seems to be a pretty straight forward, it seems to get a lot more
complicated than I really need. Thanks again and I have solved it with a
couple of queries then adding them together.

Signature

Joe Cilinceon

Reply to this Message

Marshall Barton - 29 Nov 2005 16:30 GMT

>I'm trying figure out a query or series of queries to give me data on units
>we collected rent on more than once in a month. To help explain this a
[quoted text clipped - 20 lines]
>Now if the unit is still rented the enddate is a Null value, startdate will
>always have a date. I hope that is clear, it was tough to explain.

This is a tough one Joe. I'm not sure this will even get
you started ib the right dirextion, but thought it might be
worth a shot:

SELECT O.Unit, Count(*) As LCnt
FROM Leases As O, Leases As N
WHERE O.Unit = N.Unit
    AND O.EndDate Is Not Null
    AND O.EndDate > N.StartDate
    AND Format(N.StartDate, "m/yyyy") = "11/2005"
GROUP BY O.Unit
    HAVING Count(*) > 1

Signature

Marsh
MVP [MS Access]

Reply to this Message

Joe Cilinceon - 29 Nov 2005 17:00 GMT

Thank you very much Marshall. I think I have it solved. I made one query
that gave me a list of the units that had vacated this month and been re
rented this gave me what I needed to get the amount of money each unit
generated.

>> I'm trying figure out a query or series of queries to give me data
>> on units we collected rent on more than once in a month. To help
[quoted text clipped - 35 lines]
> GROUP BY O.Unit
> HAVING Count(*) > 1

Signature

Joe Cilinceon

Reply to this Message

Joe Cilinceon - 29 Nov 2005 17:25 GMT

Here are the queries for those interested.

Query a1 (give me units that where paidthru and vacated)
SELECT LEASES.Unit, LEASES.Rate, LEASES.EndDate, LEASES.PaidThru
FROM LEASES
WHERE (((LEASES.EndDate) Is Not Null) AND
((LEASES.PaidThru)>=#11/30/2005#));

Query a2 (using a1 to get unit number found those that had been rented
again)
SELECT LEASES.Unit, LEASES.StartDate, LEASES.EndDate, LEASES.Rate,
RentDue([StartDate],DOMth([StartDate],2),[Leases]![Rate]) AS RentPaid
FROM LEASES INNER JOIN a1 ON LEASES.Unit = a1.Unit
WHERE (((LEASES.StartDate)>=#11/1/2005#) AND ((LEASES.EndDate) Is Null));

Query a3 (Got Rate + Prorate gave me the amount each of the units made)
SELECT a2.Unit, a2.RentPaid, a1.Rate, [RentPaid]+[a1]![Rate] AS Generated
FROM a2 INNER JOIN a1 ON a2.Unit = a1.Unit;

This was 3 rentals out of 22 this month.

Thanks again for those that offered help with this problem

Reply to this Message