Need help getting information

J

Joe Cilinceon

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.
 
J

Joe Cilinceon

Chris2 said:
Could you pleas post your sample data and desired results?


Sincerely,

Chris O.
Click to expand...

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)
 
C

Chris2

Joe Cilinceon said:
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.
Click to expand...

Joe Cilinceon,

Could you pleas post your sample data and desired results?


Sincerely,

Chris O.
 
M

Marshall Barton

Joe said:
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.
Click to expand...


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
 
J

Joe Cilinceon

Chris2 said:
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.
Click to expand...

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.
 
J

Joe Cilinceon

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.
 
C

Chris2

Joe Cilinceon said:
Chris2 said:
Could you pleas post your sample data and desired results?


Sincerely,

Chris O.
Click to expand...

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)
Click to expand...

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.
 
J

Joe Cilinceon

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Real Rent Collected Query Needed 4
Confusing Query based on Dates 6
Help with a couple of Queries 14
Need help with a tracking table design 7
help with Sum in between dates 9
Creating query to batch print reports 5
Help on a query based on the latest date 5
How do I figure out amout due from transaction file 3

Top