the complement of two queries

[Guest]

Hi my access friends - hope you all had a nice weekend! Anyway, now to work:

I have 2 queries relating to rents on properties. One shows those properties
where

the rent agreement period was <=date() [Query 1]

the other shows those where the rent agreement period is >=date() [Query 2]

However, results which are in query1 can also be in query 2 because although
a rent period may have ended a new one could have begun.

SOOO! What I'm after is those results which are in query 1 ONLY - ie, where
the rent period has ended (ie is <=date()) but for which no new period exists
(ie there is no >=date() periods.

I'm guessing I need to do a 3rd query, bringing in those 2 original queries,
linking them on a suitable field (say on property name), and then restricting
in some way such as "in query1 but not in query2".

However what is the correct syntax as I'm not getting the correct result. Or
do I need to append the results of the two queries to separate tables and
then do the 3rd query on those two tables?

TIA

 

[George Nicholson]

Query 1: (Group By) Properties where Max(RentAgreementEnd) <= Date()
The idea being that, for each property, you only want to evaluate a single
EndDate, the "largest" one. Then you determine whether that EndDate has
passed or not. This gives you a list of the properties that don't currently
have an agreement in effect (assuming consecutive agreements).

BTW, in addition to the reason you site, you could also get properties in
both query 1 and query 2 because both queries include = (as part of <= or

=). Properties where RentAgreementEnd equals Date() would appear in both

queries.

You should probably make a decision where they should appear (appearing on
both can be confusing unless everyone is aware if it). My vote would be to
change query 1 to be < Date() rather than <=. That way you can simply say
"This report lists all expired agreements as of mmm ddd yyyy." If you leave
it <= then the report is really "...expired as of, or expiring on, mmm ddd
yyyy,...". If an agreement expires in the future (and "future" would include
end of business today) then a case can be made for it to only appear in
query 2, along with any agreements that expire tommorrow or the day after.

HTH,
--
George Nicholson

Remove 'Junk' from return address.

Hi my access friends - hope you all had a nice weekend! Anyway, now to
work:

I have 2 queries relating to rents on properties. One shows those
properties
where

the rent agreement period was <=date() [Query 1]

the other shows those where the rent agreement period is >=date() [Query
2]

However, results which are in query1 can also be in query 2 because
although
a rent period may have ended a new one could have begun.

SOOO! What I'm after is those results which are in query 1 ONLY - ie,
where
the rent period has ended (ie is <=date()) but for which no new period
exists
(ie there is no >=date() periods.

I'm guessing I need to do a 3rd query, bringing in those 2 original
queries,
linking them on a suitable field (say on property name), and then
restricting
in some way such as "in query1 but not in query2".

However what is the correct syntax as I'm not getting the correct result.
Or
do I need to append the results of the two queries to separate tables and
then do the 3rd query on those two tables?

TIA

 

[Guest]

Thanks George. I'll give that a whirl tomorrow. But I absolutely see your
point re the " = " in both - that's logically incorrect. Anyway, thanks and
I'll post back tomorrow.
James

Query 1: (Group By) Properties where Max(RentAgreementEnd) <= Date()
The idea being that, for each property, you only want to evaluate a single
EndDate, the "largest" one. Then you determine whether that EndDate has
passed or not. This gives you a list of the properties that don't currently
have an agreement in effect (assuming consecutive agreements).

BTW, in addition to the reason you site, you could also get properties in
both query 1 and query 2 because both queries include = (as part of <= or

=). Properties where RentAgreementEnd equals Date() would appear in both

queries.

You should probably make a decision where they should appear (appearing on
both can be confusing unless everyone is aware if it). My vote would be to
change query 1 to be < Date() rather than <=. That way you can simply say
"This report lists all expired agreements as of mmm ddd yyyy." If you leave
it <= then the report is really "...expired as of, or expiring on, mmm ddd
yyyy,...". If an agreement expires in the future (and "future" would include
end of business today) then a case can be made for it to only appear in
query 2, along with any agreements that expire tommorrow or the day after.

HTH,
--
George Nicholson

Remove 'Junk' from return address.

Hi my access friends - hope you all had a nice weekend! Anyway, now to
work:

I have 2 queries relating to rents on properties. One shows those
properties
where

the rent agreement period was <=date() [Query 1]

the other shows those where the rent agreement period is >=date() [Query
2]

However, results which are in query1 can also be in query 2 because
although
a rent period may have ended a new one could have begun.

SOOO! What I'm after is those results which are in query 1 ONLY - ie,
where
the rent period has ended (ie is <=date()) but for which no new period
exists
(ie there is no >=date() periods.

I'm guessing I need to do a 3rd query, bringing in those 2 original
queries,
linking them on a suitable field (say on property name), and then
restricting
in some way such as "in query1 but not in query2".

However what is the correct syntax as I'm not getting the correct result.
Or
do I need to append the results of the two queries to separate tables and
then do the 3rd query on those two tables?

TIA

 

[Guest]

Ok - tried it and now I get the following error:

Cannot have aggregate function in WHERE clause (Max("VarEndDate"<Date())

any ideas?

TIA

Query 1: (Group By) Properties where Max(RentAgreementEnd) <= Date()
The idea being that, for each property, you only want to evaluate a single
EndDate, the "largest" one. Then you determine whether that EndDate has
passed or not. This gives you a list of the properties that don't currently
have an agreement in effect (assuming consecutive agreements).

BTW, in addition to the reason you site, you could also get properties in
both query 1 and query 2 because both queries include = (as part of <= or

=). Properties where RentAgreementEnd equals Date() would appear in both

queries.

You should probably make a decision where they should appear (appearing on
both can be confusing unless everyone is aware if it). My vote would be to
change query 1 to be < Date() rather than <=. That way you can simply say
"This report lists all expired agreements as of mmm ddd yyyy." If you leave
it <= then the report is really "...expired as of, or expiring on, mmm ddd
yyyy,...". If an agreement expires in the future (and "future" would include
end of business today) then a case can be made for it to only appear in
query 2, along with any agreements that expire tommorrow or the day after.

HTH,
--
George Nicholson

Remove 'Junk' from return address.

Hi my access friends - hope you all had a nice weekend! Anyway, now to
work:

I have 2 queries relating to rents on properties. One shows those
properties
where

the rent agreement period was <=date() [Query 1]

the other shows those where the rent agreement period is >=date() [Query
2]

However, results which are in query1 can also be in query 2 because
although
a rent period may have ended a new one could have begun.

SOOO! What I'm after is those results which are in query 1 ONLY - ie,
where
the rent period has ended (ie is <=date()) but for which no new period
exists
(ie there is no >=date() periods.

I'm guessing I need to do a 3rd query, bringing in those 2 original
queries,
linking them on a suitable field (say on property name), and then
restricting
in some way such as "in query1 but not in query2".

However what is the correct syntax as I'm not getting the correct result.
Or
do I need to append the results of the two queries to separate tables and
then do the 3rd query on those two tables?

TIA

 

[George Nicholson]

In this case you have to use the less effecient HAVING, not WHERE.

HAVING "filters" after aggregation. WHERE filters before aggregation (which
you don't want to do in this case anyway, but if you did, it would be like
changing "WHERE Max(VarEndDate) < ..." to "WHERE VarEndDate <...", which
would give you the most-recent-expiration per property, totally ignoring
renewed contracts. Not at all what you want.)

If working in SQL view or in code, remember that HAVING *follows* the GROUP
BY clause (unlike WHERE, which precedes it). So you'll probably end up with
something like:

SELECT Property, Max(EndDate) as EndDate
FROM SomeTable
GROUP BY Property
HAVING Max(EndDate) < Date()

HTH,
--
George Nicholson

Remove 'Junk' from return address.

Ok - tried it and now I get the following error:

Cannot have aggregate function in WHERE clause (Max("VarEndDate"<Date())

any ideas?

TIA

Query 1: (Group By) Properties where Max(RentAgreementEnd) <= Date()
The idea being that, for each property, you only want to evaluate a
single
EndDate, the "largest" one. Then you determine whether that EndDate has
passed or not. This gives you a list of the properties that don't
currently
have an agreement in effect (assuming consecutive agreements).

BTW, in addition to the reason you site, you could also get properties in
both query 1 and query 2 because both queries include = (as part of <= or

=). Properties where RentAgreementEnd equals Date() would appear in

both
queries.

You should probably make a decision where they should appear (appearing
on
both can be confusing unless everyone is aware if it). My vote would be
to
change query 1 to be < Date() rather than <=. That way you can simply say
"This report lists all expired agreements as of mmm ddd yyyy." If you
leave
it <= then the report is really "...expired as of, or expiring on, mmm
ddd
yyyy,...". If an agreement expires in the future (and "future" would
include
end of business today) then a case can be made for it to only appear in
query 2, along with any agreements that expire tommorrow or the day
after.

HTH,
--
George Nicholson

Remove 'Junk' from return address.

Hi my access friends - hope you all had a nice weekend! Anyway, now to
work:

I have 2 queries relating to rents on properties. One shows those
properties
where

the rent agreement period was <=date() [Query 1]

the other shows those where the rent agreement period is >=date()
[Query
2]

However, results which are in query1 can also be in query 2 because
although
a rent period may have ended a new one could have begun.

SOOO! What I'm after is those results which are in query 1 ONLY - ie,
where
the rent period has ended (ie is <=date()) but for which no new period
exists
(ie there is no >=date() periods.

I'm guessing I need to do a 3rd query, bringing in those 2 original
queries,
linking them on a suitable field (say on property name), and then
restricting
in some way such as "in query1 but not in query2".

However what is the correct syntax as I'm not getting the correct
result.
Or
do I need to append the results of the two queries to separate tables
and
then do the 3rd query on those two tables?

TIA

 

[Guest]

Thanks for that - I'm away for a couple weeks now but will definitely give it
a go asap.
James

In this case you have to use the less effecient HAVING, not WHERE.

HAVING "filters" after aggregation. WHERE filters before aggregation (which
you don't want to do in this case anyway, but if you did, it would be like
changing "WHERE Max(VarEndDate) < ..." to "WHERE VarEndDate <...", which
would give you the most-recent-expiration per property, totally ignoring
renewed contracts. Not at all what you want.)

If working in SQL view or in code, remember that HAVING *follows* the GROUP
BY clause (unlike WHERE, which precedes it). So you'll probably end up with
something like:

SELECT Property, Max(EndDate) as EndDate
FROM SomeTable
GROUP BY Property
HAVING Max(EndDate) < Date()

HTH,
--
George Nicholson

Remove 'Junk' from return address.

Ok - tried it and now I get the following error:

Cannot have aggregate function in WHERE clause (Max("VarEndDate"<Date())

any ideas?

TIA

Query 1: (Group By) Properties where Max(RentAgreementEnd) <= Date()
The idea being that, for each property, you only want to evaluate a
single
EndDate, the "largest" one. Then you determine whether that EndDate has
passed or not. This gives you a list of the properties that don't
currently
have an agreement in effect (assuming consecutive agreements).

BTW, in addition to the reason you site, you could also get properties in
both query 1 and query 2 because both queries include = (as part of <= or
=). Properties where RentAgreementEnd equals Date() would appear in
both
queries.

You should probably make a decision where they should appear (appearing
on
both can be confusing unless everyone is aware if it). My vote would be
to
change query 1 to be < Date() rather than <=. That way you can simply say
"This report lists all expired agreements as of mmm ddd yyyy." If you
leave
it <= then the report is really "...expired as of, or expiring on, mmm
ddd
yyyy,...". If an agreement expires in the future (and "future" would
include
end of business today) then a case can be made for it to only appear in
query 2, along with any agreements that expire tommorrow or the day
after.

HTH,
--
George Nicholson

Remove 'Junk' from return address.


Hi my access friends - hope you all had a nice weekend! Anyway, now to
work:

I have 2 queries relating to rents on properties. One shows those
properties
where

the rent agreement period was <=date() [Query 1]

the other shows those where the rent agreement period is >=date()
[Query
2]

However, results which are in query1 can also be in query 2 because
although
a rent period may have ended a new one could have begun.

SOOO! What I'm after is those results which are in query 1 ONLY - ie,
where
the rent period has ended (ie is <=date()) but for which no new period
exists
(ie there is no >=date() periods.

I'm guessing I need to do a 3rd query, bringing in those 2 original
queries,
linking them on a suitable field (say on property name), and then
restricting
in some way such as "in query1 but not in query2".

However what is the correct syntax as I'm not getting the correct
result.
Or
do I need to append the results of the two queries to separate tables
and
then do the 3rd query on those two tables?

TIA