Comparing next in a sequence

[Bibi]

I am working with a property management database. I need to summarize how
many leases written during a give period are renewals.

qLease Renewal Step 1
Property IDNumber Lease Begins Lease EndsDuration Date SignedCount
121es 2817 8/20/2005 8/15/2006 360 5/4/2005 1
121es 3968 12/6/2006 8/20/2007 257 12/6/2006 1
121es 4626 8/30/2007 8/10/2008 346 8/14/2007 1

If the next lease on a property begins the day after the previous lease
expired, the lease is considered a RENEWAL as long as it is for a minimum of
180 days in duration.
If the next lease on a property begins the day after the previous lease
expired but the duration of the next lease is for less than 180 days then it
is considered NEW.
If the Lease Ends date is greater than (Lease Ends + 1) then it is
considered NEW.

The user may decide to designate those leases less than 180 days as
extensions – I have not heard back from them yet. But in the meantime, I
really need help with the basic NEXT lease query - I do not know how to
express this. I can not write code. All help will be greatly appreciated.

 

[Marshall Barton]

I am working with a property management database. I need to summarize how
many leases written during a give period are renewals.

qLease Renewal Step 1
Property IDNumber Lease Begins Lease EndsDuration Date SignedCount
121es 2817 8/20/2005 8/15/2006 360 5/4/2005 1
121es 3968 12/6/2006 8/20/2007 257 12/6/2006 1
121es 4626 8/30/2007 8/10/2008 346 8/14/2007 1

If the next lease on a property begins the day after the previous lease
expired, the lease is considered a RENEWAL as long as it is for a minimum of
180 days in duration.
If the next lease on a property begins the day after the previous lease
expired but the duration of the next lease is for less than 180 days then it
is considered NEW.
If the Lease Ends date is greater than (Lease Ends + 1) then it is
considered NEW.

The user may decide to designate those leases less than 180 days as
extensions – I have not heard back from them yet. But in the meantime, I
really need help with the basic NEXT lease query - I do not know how to
express this.

I think this is what you are looking for:

SELECT T.Property, T.IDNumber, T.[Lease Begins],
T.[Lease Ends], T.Duration, T.[Date Signed,
T.[Count],
IIf(P.Property Is Null, "New",
IIf(T.Duration >= 180, "Renewal", "Extension"))
FROM table As T LEFT JOIN table As P
ON T.Property = P.Property
And P.[Lease Ends] + 1 = T.[Lease Begins]
WHERE T.[Date Signed] Between [Period Start]
And [Period End]

 

[Bibi]

Marsh
Thank you for taking the time to look at my problem - I mistated part of it.
It should read: If the next lease on a property begins the day after the
previous lease expired, the lease is considered a RENEWAL as long as it is
for a minimum of 180 days in duration.
If the next lease on a property begins the day after the previous lease
expired but the duration of the next lease is for less than 180 days then it
is considered NEW.
If the next Lease Begins (not lease ends) date is greater than the prior
lease (Lease Ends + 1) then it is considered NEW. If this is the first lease
for a property it is considered NEW.
I sorry for the misstatement of the problem - I also was not clear in that
all the data is coming from the lease table so I can't have a null property
value. Could you look at this again?
All help is truly appreciated.


--
TIA
Bibi

I am working with a property management database. I need to summarize how
many leases written during a give period are renewals.

qLease Renewal Step 1
Property IDNumber Lease Begins Lease EndsDuration Date SignedCount
121es 2817 8/20/2005 8/15/2006 360 5/4/2005 1
121es 3968 12/6/2006 8/20/2007 257 12/6/2006 1
121es 4626 8/30/2007 8/10/2008 346 8/14/2007 1

If the next lease on a property begins the day after the previous lease
expired, the lease is considered a RENEWAL as long as it is for a minimum of
180 days in duration.
If the next lease on a property begins the day after the previous lease
expired but the duration of the next lease is for less than 180 days then it
is considered NEW.
If the Lease Ends date is greater than (Lease Ends + 1) then it is
considered NEW.

The user may decide to designate those leases less than 180 days as
extensions – I have not heard back from them yet. But in the meantime, I
really need help with the basic NEXT lease query - I do not know how to
express this.

I think this is what you are looking for:

SELECT T.Property, T.IDNumber, T.[Lease Begins],
T.[Lease Ends], T.Duration, T.[Date Signed,
T.[Count],
IIf(P.Property Is Null, "New",
IIf(T.Duration >= 180, "Renewal", "Extension"))
FROM table As T LEFT JOIN table As P
ON T.Property = P.Property
And P.[Lease Ends] + 1 = T.[Lease Begins]
WHERE T.[Date Signed] Between [Period Start]
And [Period End]

 

[Marshall Barton]

Thank you for taking the time to look at my problem - I mistated part of it.
It should read: If the next lease on a property begins the day after the
previous lease expired, the lease is considered a RENEWAL as long as it is
for a minimum of 180 days in duration.
If the next lease on a property begins the day after the previous lease
expired but the duration of the next lease is for less than 180 days then it
is considered NEW.
If the next Lease Begins (not lease ends) date is greater than the prior
lease (Lease Ends + 1) then it is considered NEW. If this is the first lease
for a property it is considered NEW.
I sorry for the misstatement of the problem - I also was not clear in that
all the data is coming from the lease table so I can't have a null property
value. Could you look at this again?

Ok, I looked at it again. Except for using Extension
instead of New in one case, I still think you should try
what I posted before (after changing the field names and
table name to your real names). If it does not do what you
want, post back with a small sample dataset and the results
along with an explanation of where the result is not what
you want.

Note that the test for Null is used to see if the Left Join
found a match or not and has nothing to do with your data.

 

[Bibi]

I am lost. I am self taught and have major gaps in my understanding. I have
two tables - Properties and Leases. I tried to follow your outline but I was
truly lost after FROM on. I can't figure out what T. Property and P. Propetry
should be and the significance of Period Start and Period End

this is what I wrote:
SELECT Properties.[Property ID], Leases.[Lease Begins], Leases.[Lease
Ends], Leases.Number, Leases.[Date Signed], [Lease Ends]-[Lease Begins] AS
Duration, Leases.Count, IIf([Properties].[Property ID] Is
Null,"New",IIf([duration]>=180,"Renewal","Extension")) AS [Lease Type]
FROM Properties LEFT JOIN Leases ON Properties.[Property ID] =
Leases.[Property Id]
WHERE (((Properties.[Property ID]) Is Not Null) AND ((Leases.[Date Signed])
Is Not Null))
ORDER BY Properties.[Property ID], Leases.[Lease Begins];

The result for one property follows:
Property ID Number Lease Begins Lease Ends DurationDate SignedCount LeaseType
1230tt 1122 10/25/2002 8/9/2003 288 0/18/2002 1 Renewal
1230tt 1192 8/15/2003 8/10/2004 361 2/26/2003 1 Renewal
1230tt 2489 1/7/2005 1/2/2006 360 12/30/20041 Renewal
1230tt 3515 7/25/2006 7/31/2007 371 5/5/2006 1 Renewal
1230tt 4466 8/1/2007 7/31/2008 365 6/20/2007 1 Renewal

The results should be New, New New, New, Renewal.
Thanks for all help.

TIA
Bibi

 

[Marshall Barton]

I am lost. I am self taught and have major gaps in my understanding. I have
two tables - Properties and Leases. I tried to follow your outline but I was
truly lost after FROM on. I can't figure out what T. Property and P. Propetry
should be and the significance of Period Start and Period End

this is what I wrote:
SELECT Properties.[Property ID], Leases.[Lease Begins], Leases.[Lease
Ends], Leases.Number, Leases.[Date Signed], [Lease Ends]-[Lease Begins] AS
Duration, Leases.Count, IIf([Properties].[Property ID] Is
Null,"New",IIf([duration]>=180,"Renewal","Extension")) AS [Lease Type]
FROM Properties LEFT JOIN Leases ON Properties.[Property ID] =
Leases.[Property Id]
WHERE (((Properties.[Property ID]) Is Not Null) AND ((Leases.[Date Signed])
Is Not Null))
ORDER BY Properties.[Property ID], Leases.[Lease Begins];

The result for one property follows:
Property ID Number Lease Begins Lease Ends DurationDate SignedCount LeaseType
1230tt 1122 10/25/2002 8/9/2003 288 0/18/2002 1 Renewal
1230tt 1192 8/15/2003 8/10/2004 361 2/26/2003 1 Renewal
1230tt 2489 1/7/2005 1/2/2006 360 12/30/20041 Renewal
1230tt 3515 7/25/2006 7/31/2007 371 5/5/2006 1 Renewal
1230tt 4466 8/1/2007 7/31/2008 365 6/20/2007 1 Renewal

The results should be New, New New, New, Renewal.

You need to join the Leases table to itself to tell if one
lease begins the day after another lease ends. Note that
the T and P are aliases for the Leases table and are
necessary because it is crtical that we specify which
instance of the table is being used for different purposes.

I was unaware of the properties table and am still unsure of
how it relates to the Leases table. Because you are not
using any data from the Properties table, I can see no point
in having it in the query. (The Property ID field is in
both tables, so it doesn't matter which table you get it
from.)

SELECT T.[Property ID], T.[Lease Begins],
T.[Lease Ends], T.Number, T.[Date Signed],
T.[Lease Ends] - T.[Lease Begins] AS Duration,
T.Count,
IIf(T.[Property ID] Is Null,"New",
IIf(Duration >= 180,"Renewal","Extension"))
AS [Lease Type]
FROM Leases As T LEFT JOIN Leases As P
ON T.[Property ID] = P.[Property Id]
And P.[Lease Ends] + 1 = T.[Lease Begins]
WHERE T.[Date Signed]) Is Not Null
ORDER BY T.[Property ID], T.[Lease Begins]

It think I have all the names straight this time so you can
just Copy/Paste the above over the SQL view of a new query.

 

[Bibi]

Marsh
Thank you. I was able to get the query to run and work but I did have to
make one change - from Table T to Table P in the first IIF statement. With
that change I got consistent, expected results but there is one problem when
the first lease signed for a property does not have the first Lease Begins
date. This is not a significant problem but I tried to work through the
changes needed to change it and couldn't. We can live with it. I'll
continue to try though because I don't completely understand how the query
works and I need to learn that. Again, thank you so much for your help. I
will try to pass along the kindness.

--
TIA
Bibi

I am lost. I am self taught and have major gaps in my understanding. I have
two tables - Properties and Leases. I tried to follow your outline but I was
truly lost after FROM on. I can't figure out what T. Property and P. Propetry
should be and the significance of Period Start and Period End

this is what I wrote:
SELECT Properties.[Property ID], Leases.[Lease Begins], Leases.[Lease
Ends], Leases.Number, Leases.[Date Signed], [Lease Ends]-[Lease Begins] AS
Duration, Leases.Count, IIf([Properties].[Property ID] Is
Null,"New",IIf([duration]>=180,"Renewal","Extension")) AS [Lease Type]
FROM Properties LEFT JOIN Leases ON Properties.[Property ID] =
Leases.[Property Id]
WHERE (((Properties.[Property ID]) Is Not Null) AND ((Leases.[Date Signed])
Is Not Null))
ORDER BY Properties.[Property ID], Leases.[Lease Begins];

The result for one property follows:
Property ID Number Lease Begins Lease Ends DurationDate SignedCount LeaseType
1230tt 1122 10/25/2002 8/9/2003 288 0/18/2002 1 Renewal
1230tt 1192 8/15/2003 8/10/2004 361 2/26/2003 1 Renewal
1230tt 2489 1/7/2005 1/2/2006 360 12/30/20041 Renewal
1230tt 3515 7/25/2006 7/31/2007 371 5/5/2006 1 Renewal
1230tt 4466 8/1/2007 7/31/2008 365 6/20/2007 1 Renewal

The results should be New, New New, New, Renewal.

You need to join the Leases table to itself to tell if one
lease begins the day after another lease ends. Note that
the T and P are aliases for the Leases table and are
necessary because it is crtical that we specify which
instance of the table is being used for different purposes.

I was unaware of the properties table and am still unsure of
how it relates to the Leases table. Because you are not
using any data from the Properties table, I can see no point
in having it in the query. (The Property ID field is in
both tables, so it doesn't matter which table you get it
from.)

SELECT T.[Property ID], T.[Lease Begins],
T.[Lease Ends], T.Number, T.[Date Signed],
T.[Lease Ends] - T.[Lease Begins] AS Duration,
T.Count,
IIf(T.[Property ID] Is Null,"New",
IIf(Duration >= 180,"Renewal","Extension"))
AS [Lease Type]
FROM Leases As T LEFT JOIN Leases As P
ON T.[Property ID] = P.[Property Id]
And P.[Lease Ends] + 1 = T.[Lease Begins]
WHERE T.[Date Signed]) Is Not Null
ORDER BY T.[Property ID], T.[Lease Begins]

It think I have all the names straight this time so you can
just Copy/Paste the above over the SQL view of a new query.

 

[Marshall Barton]

Thank you. I was able to get the query to run and work but I did have to
make one change - from Table T to Table P in the first IIF statement. With
that change I got consistent, expected results but there is one problem when
the first lease signed for a property does not have the first Lease Begins
date. This is not a significant problem but I tried to work through the
changes needed to change it and couldn't. We can live with it. I'll
continue to try though because I don't completely understand how the query
works and I need to learn that. Again, thank you so much for your help. I
will try to pass along the kindness.

You're right. the first IIf should use P. instead of T.

I don't understand what you are describing about a Null
begin date in the earliest record for a property. The query
should return NEW for such records.

If you have further questions about the query, please
include a copy of the SQL statement as you have it. Some
sample records that help demonstrate the problem would also
help me understand what you are after.

 

[Bibi]

Marsh
I am working with properties that may be rented for an academic year. A
lease might be written in March that will start in August (the first lease –
lower auto lease Number) but then in May someone might lease the property for
the summer session starting in June and ending in August (higher auto lease
Number, later Date Signed. When this happens, the query may return
EXTENSION as a value for the lease with the earliest start date based on its
duration rather than the fact that it is the first occupancy, that is, it has
the earliest Lease Begins date and in an ideal world should be considered NEW.

qLeaseType
Property IDLease BeginsLease EndsNumberDate Signed DurationCountLease Type
3333ec 7/1/2002 7/24/2002 853 5/10/2002 23 1 Extension
3333ec 7/30/2002 7/15/2003 779 3/22/2002 350 1 New
3333ec 7/21/2003 7/31/2004 1184 2/19/2003 376 1 New
3333ec 8/1/2004 7/31/2005 2076 2/13/2004 364 1 Renewal
3333ec 8/8/2005 8/3/2006 2801 5/2/2005 360 1 New
3333ec 8/9/2006 6/25/2007 3184 1/13/2006 320 1 New
3333ec 7/3/2007 8/4/2008 4089 2/3/2007 398 1 New


Here’s the code that yielded the above results:
SELECT T.[Property ID], T.[Lease Begins], T.[Lease Ends], T.Number, T.[Date
Signed], T.[Lease Ends]-T.[Lease Begins] AS Duration, T.Count,
IIf(P.[Property ID] Is Null,"New",IIf(Duration>=180,"Renewal","Extension"))
AS [Lease Type]
FROM Leases AS T LEFT JOIN Leases AS P ON (T.[Property ID]=P.[Property Id])
AND (P.[Lease Ends]+1=T.[Lease Begins])
WHERE T.[Date Signed] Is Not Null
ORDER BY T.[Property ID], T.[Lease Begins];

I’m still working with it but haven’t come up with the solution yet.
It would be super if you have the time to look at it.
Many thanks.

 

[Marshall Barton]

I am working with properties that may be rented for an academic year. A
lease might be written in March that will start in August (the first lease –
lower auto lease Number) but then in May someone might lease the property for
the summer session starting in June and ending in August (higher auto lease
Number, later Date Signed. When this happens, the query may return
EXTENSION as a value for the lease with the earliest start date based on its
duration rather than the fact that it is the first occupancy, that is, it has
the earliest Lease Begins date and in an ideal world should be considered NEW.

qLeaseType
Property IDLease BeginsLease EndsNumberDate Signed DurationCountLease Type
3333ec 7/1/2002 7/24/2002 853 5/10/2002 23 1 Extension
3333ec 7/30/2002 7/15/2003 779 3/22/2002 350 1 New
3333ec 7/21/2003 7/31/2004 1184 2/19/2003 376 1 New
3333ec 8/1/2004 7/31/2005 2076 2/13/2004 364 1 Renewal
3333ec 8/8/2005 8/3/2006 2801 5/2/2005 360 1 New
3333ec 8/9/2006 6/25/2007 3184 1/13/2006 320 1 New
3333ec 7/3/2007 8/4/2008 4089 2/3/2007 398 1 New


Here’s the code that yielded the above results:
SELECT T.[Property ID], T.[Lease Begins], T.[Lease Ends], T.Number, T.[Date
Signed], T.[Lease Ends]-T.[Lease Begins] AS Duration, T.Count,
IIf(P.[Property ID] Is Null,"New",IIf(Duration>=180,"Renewal","Extension"))
AS [Lease Type]
FROM Leases AS T LEFT JOIN Leases AS P ON (T.[Property ID]=P.[Property Id])
AND (P.[Lease Ends]+1=T.[Lease Begins])
WHERE T.[Date Signed] Is Not Null
ORDER BY T.[Property ID], T.[Lease Begins];

That can only happen if there is a lease entry for that
property with an end date of 6/30/02. Since there is no
such record in the result, the offending record's date
signed field will be null. You could remove the query's
WHERE clause to have the record show up in the result.

I don't understand why you have that WHERE clause. What
good is a record of leases that were never used?

 

[Bibi]

Deleting the Where clause does not change the result for lease number 853 –
it is the first fully documented lease for the property – even though it is
of short duration, it is not an EXTENSION – it is a NEW lease – it is the
first lease to have three valid date fields – Lease Begins, Lease Ends, Date
Signed.

This is the result of the query when the WHERE clause is deleted:

Property IDLease BeginsLease EndsNumberDateSigned DurationCountLease Type
3333ec 3/29/2000 5/4/2001 1426 401 1 New
3333ec 3/29/2000 5/4/2001 116 401 1 New
3333ec 5/11/2001 6/30/2002 901 415 1 New
3333ec 7/1/2002 7/24/2002 853 5/10/2002 23 1
Extension
3333ec 7/30/2002 7/15/2003 779 3/22/2002 350 1 New
3333ec 7/21/2003 7/31/2004 1184 2/19/2003 376 1 New
3333ec 8/1/2004 7/31/2005 2076 2/13/2004 364 1 Renewal
3333ec 8/8/2005 8/3/2006 2801 5/2/2005 360 1 New
3333ec 8/9/2006 6/25/2007 3184 1/13/2006 320 1 New
3333ec 7/3/2007 8/4/2008 4089 2/3/2007 398 1 New

The first three leases shown above may or may not be valid – since in the
data base they were never signed, for our purposes right now we have to
consider them data entry errors- they are eliminated by the WHERE clause.

I think I need to put something in the IIF statement that shows that the
Type for the lease with the earliest Lease Begins Date, whatever its
duration, is always NEW and unfortunately I do not know how to write that.

Again, thanks. I hope you find this interesting because I do appreciate
your input. Thank you for your patience.

--
TIA
Bibi

I am working with properties that may be rented for an academic year. A
lease might be written in March that will start in August (the first lease –
lower auto lease Number) but then in May someone might lease the property for
the summer session starting in June and ending in August (higher auto lease
Number, later Date Signed. When this happens, the query may return
EXTENSION as a value for the lease with the earliest start date based on its
duration rather than the fact that it is the first occupancy, that is, it has
the earliest Lease Begins date and in an ideal world should be considered NEW.

qLeaseType
Property IDLease BeginsLease EndsNumberDate Signed DurationCountLease Type
3333ec 7/1/2002 7/24/2002 853 5/10/2002 23 1 Extension
3333ec 7/30/2002 7/15/2003 779 3/22/2002 350 1 New
3333ec 7/21/2003 7/31/2004 1184 2/19/2003 376 1 New
3333ec 8/1/2004 7/31/2005 2076 2/13/2004 364 1 Renewal
3333ec 8/8/2005 8/3/2006 2801 5/2/2005 360 1 New
3333ec 8/9/2006 6/25/2007 3184 1/13/2006 320 1 New
3333ec 7/3/2007 8/4/2008 4089 2/3/2007 398 1 New


Here’s the code that yielded the above results:
SELECT T.[Property ID], T.[Lease Begins], T.[Lease Ends], T.Number, T.[Date
Signed], T.[Lease Ends]-T.[Lease Begins] AS Duration, T.Count,
IIf(P.[Property ID] Is Null,"New",IIf(Duration>=180,"Renewal","Extension"))
AS [Lease Type]
FROM Leases AS T LEFT JOIN Leases AS P ON (T.[Property ID]=P.[Property Id])
AND (P.[Lease Ends]+1=T.[Lease Begins])
WHERE T.[Date Signed] Is Not Null
ORDER BY T.[Property ID], T.[Lease Begins];

That can only happen if there is a lease entry for that
property with an end date of 6/30/02. Since there is no
such record in the result, the offending record's date
signed field will be null. You could remove the query's
WHERE clause to have the record show up in the result.

I don't understand why you have that WHERE clause. What
good is a record of leases that were never used?

 

[Marshall Barton]

Deleting the Where clause does not change the result for lease number 853 –
it is the first fully documented lease for the property – even though it is
of short duration, it is not an EXTENSION – it is a NEW lease – it is the
first lease to have three valid date fields – Lease Begins, Lease Ends, Date
Signed.

This is the result of the query when the WHERE clause is deleted:

Property IDLease BeginsLease EndsNumberDateSigned DurationCountLease Type
3333ec 3/29/2000 5/4/2001 1426 401 1 New
3333ec 3/29/2000 5/4/2001 116 401 1 New
3333ec 5/11/2001 6/30/2002 901 415 1 New
3333ec 7/1/2002 7/24/2002 853 5/10/2002 23 1
Extension
3333ec 7/30/2002 7/15/2003 779 3/22/2002 350 1 New
3333ec 7/21/2003 7/31/2004 1184 2/19/2003 376 1 New
3333ec 8/1/2004 7/31/2005 2076 2/13/2004 364 1 Renewal
3333ec 8/8/2005 8/3/2006 2801 5/2/2005 360 1 New
3333ec 8/9/2006 6/25/2007 3184 1/13/2006 320 1 New
3333ec 7/3/2007 8/4/2008 4089 2/3/2007 398 1 New

The first three leases shown above may or may not be valid – since in the
data base they were never signed, for our purposes right now we have to
consider them data entry errors- they are eliminated by the WHERE clause.

I think I need to put something in the IIF statement that shows that the
Type for the lease with the earliest Lease Begins Date, whatever its
duration, is always NEW and unfortunately I do not know how to write that.

Don't mess with the IIf expression.

You have exactly what I deduced must exist. The third
record in the a above dataset is an unsigned lease ending
6/30/02. Since that ends the day before the next lease (the
first one in the filtered dataset), that makes the next
lease an extension (or renewal).

IMO, those unsigned lease records are garbage, but you might
have a vaild business reason for them. If you really need
to have unsigned leases in the table, then change the ON
clause to:

SELECT T.[Property ID], T.[Lease Begins], T.[Lease Ends],
T.Number, T.[Date Signed],
T.[Lease Ends]-T.[Lease Begins] AS Duration,
T.Count,
IIf(P.[Property ID] Is Null,"New",
IIf(Duration>=180,"Renewal","Extension"))
AS [Lease Type]
FROM Leases AS T LEFT JOIN Leases AS P
ON T.[Property ID]=P.[Property Id]
AND (P.[Lease Ends]+1=T.[Lease Begins]
And P.[Date Signed] Is Not Null)
WHERE T.[Date Signed] Is Not Null
ORDER BY T.[Property ID], T.[Lease Begins]

Note that the parenthesis in the ON clause are important and
should not be rearranged in spite of the seemingly
commutative And operators. Switching the query to design
view may confuse Access so it rearranges them resulting in
an error.

 

[Bibi]

Marsh
Thank you. I finally understand WHY the query works......It is a such a
nice feeling when the light bulb goes on.
TIA
Bibi

Deleting the Where clause does not change the result for lease number 853 –
it is the first fully documented lease for the property – even though it is
of short duration, it is not an EXTENSION – it is a NEW lease – it is the
first lease to have three valid date fields – Lease Begins, Lease Ends, Date
Signed.

This is the result of the query when the WHERE clause is deleted:

Property IDLease BeginsLease EndsNumberDateSigned DurationCountLease Type
3333ec 3/29/2000 5/4/2001 1426 401 1 New
3333ec 3/29/2000 5/4/2001 116 401 1 New
3333ec 5/11/2001 6/30/2002 901 415 1 New
3333ec 7/1/2002 7/24/2002 853 5/10/2002 23 1
Extension
3333ec 7/30/2002 7/15/2003 779 3/22/2002 350 1 New
3333ec 7/21/2003 7/31/2004 1184 2/19/2003 376 1 New
3333ec 8/1/2004 7/31/2005 2076 2/13/2004 364 1 Renewal
3333ec 8/8/2005 8/3/2006 2801 5/2/2005 360 1 New
3333ec 8/9/2006 6/25/2007 3184 1/13/2006 320 1 New
3333ec 7/3/2007 8/4/2008 4089 2/3/2007 398 1 New

The first three leases shown above may or may not be valid – since in the
data base they were never signed, for our purposes right now we have to
consider them data entry errors- they are eliminated by the WHERE clause.

I think I need to put something in the IIF statement that shows that the
Type for the lease with the earliest Lease Begins Date, whatever its
duration, is always NEW and unfortunately I do not know how to write that.

Don't mess with the IIf expression.

You have exactly what I deduced must exist. The third
record in the a above dataset is an unsigned lease ending
6/30/02. Since that ends the day before the next lease (the
first one in the filtered dataset), that makes the next
lease an extension (or renewal).

IMO, those unsigned lease records are garbage, but you might
have a vaild business reason for them. If you really need
to have unsigned leases in the table, then change the ON
clause to:

SELECT T.[Property ID], T.[Lease Begins], T.[Lease Ends],
T.Number, T.[Date Signed],
T.[Lease Ends]-T.[Lease Begins] AS Duration,
T.Count,
IIf(P.[Property ID] Is Null,"New",
IIf(Duration>=180,"Renewal","Extension"))
AS [Lease Type]
FROM Leases AS T LEFT JOIN Leases AS P
ON T.[Property ID]=P.[Property Id]
AND (P.[Lease Ends]+1=T.[Lease Begins]
And P.[Date Signed] Is Not Null)
WHERE T.[Date Signed] Is Not Null
ORDER BY T.[Property ID], T.[Lease Begins]

Note that the parenthesis in the ON clause are important and
should not be rearranged in spite of the seemingly
commutative And operators. Switching the query to design
view may confuse Access so it rearranges them resulting in
an error.