tough query

  • Thread starter Thread starter Brad
  • Start date Start date
B

Brad

Here's my query so far:

SELECT DL.CustID AS CustID, DL.FullName AS FullName,
DL.LoanID AS LoanID, DL.DateIssued AS DateIssued,
DL.DueDate AS DueDate, DL.Balance AS Balance, DL.OrigBal
AS OrigBal, CA.DateAssigned, IIf(CA.DateAssigned Is Not
Null,IIf(Max(C.DateofCall)>CA.DateAssigned,Max
(C.DateofCall),''),'') AS LastCall, IIf(CA.DateAssigned
Is Not Null,Sum(P.Amount),0) AS Collected
FROM ((qryDelinquentLoan AS DL LEFT JOIN
tblCallAssignment AS CA ON CA.CustID=DL.CustID) LEFT JOIN
tblCalls AS C ON DL.LoanID=C.LoanID) LEFT JOIN
tblPayments AS P ON DL.LoanID=P.LoanID
WHERE (CA.DateAssigned Is Not Null And
P.Date>CA.DateAssigned And P.Deleted=False) Or
(CA.DateAssigned Is Null)
GROUP BY DL.CustID, DL.FullName, DL.LoanID,
DL.DateIssued, DL.DueDate, DL.Balance, DL.OrigBal,
CA.DateAssigned, DL.LoanID
ORDER BY DL.CustID;

I run into trouble with tblPayments (P). I want the
query to return the sum of all payments after a certain
date (P.Date > CA.DateAssigned) but if there have been no
payments after that date, return 0 (there have most
likely been payments before that date.) Currently, if
there are no payments after the date, the records drop
out of the query. I tried to add P.Date to a nested IIF
statement in the select "IIf(CA.DateAssigned Is Not Null,
IIF(P.[Date] > CA.DateAssigned, Sum(P.Amount),0), 0) AS
Collected," but then I had to add P.Date to the Group By
and the query returned a record for every payment in the
table (even records prior to the DateAssigned), rather
than one record with a sum. Any way to get this to work?

Thanks
 
Hi,



Try replacing

IIf(CA.DateAssigned Is Not Null,
IIF(P.[Date] > CA.DateAssigned, Sum(P.Amount),0), 0)

by:

Nz( Sum(P.Amount), 0 )



or by:

iif( Sum(P.Amount) Is Null, 0, Sum(P.Amount) )


Hoping it may help,
Vanderghast, Access MVP
 
Thank you for your suggestions, but neither of these
worked... the problem is that the where clause eliminates
the records which have CA.DateAssigned not null, but no
payments in the table after CA.DateAssigned. I have to
get the WHERE clause to not remove those records. I
can't put an aggregate function in the where clause to
have an IIF there... is there another way to manipulate
the where clause?

Thanks

Brad
-----Original Message-----
Hi,



Try replacing

IIf(CA.DateAssigned Is Not Null,
IIF(P.[Date] > CA.DateAssigned, Sum(P.Amount),0), 0)

by:

Nz( Sum(P.Amount), 0 )



or by:

iif( Sum(P.Amount) Is Null, 0, Sum(P.Amount) )


Hoping it may help,
Vanderghast, Access MVP


Here's my query so far:

SELECT DL.CustID AS CustID, DL.FullName AS FullName,
DL.LoanID AS LoanID, DL.DateIssued AS DateIssued,
DL.DueDate AS DueDate, DL.Balance AS Balance, DL.OrigBal
AS OrigBal, CA.DateAssigned, IIf(CA.DateAssigned Is Not
Null,IIf(Max(C.DateofCall)>CA.DateAssigned,Max
(C.DateofCall),''),'') AS LastCall, IIf(CA.DateAssigned
Is Not Null,Sum(P.Amount),0) AS Collected
FROM ((qryDelinquentLoan AS DL LEFT JOIN
tblCallAssignment AS CA ON CA.CustID=DL.CustID) LEFT JOIN
tblCalls AS C ON DL.LoanID=C.LoanID) LEFT JOIN
tblPayments AS P ON DL.LoanID=P.LoanID
WHERE (CA.DateAssigned Is Not Null And
P.Date>CA.DateAssigned And P.Deleted=False) Or
(CA.DateAssigned Is Null)
GROUP BY DL.CustID, DL.FullName, DL.LoanID,
DL.DateIssued, DL.DueDate, DL.Balance, DL.OrigBal,
CA.DateAssigned, DL.LoanID
ORDER BY DL.CustID;

I run into trouble with tblPayments (P). I want the
query to return the sum of all payments after a certain
date (P.Date > CA.DateAssigned) but if there have been no
payments after that date, return 0 (there have most
likely been payments before that date.) Currently, if
there are no payments after the date, the records drop
out of the query. I tried to add P.Date to a nested IIF
statement in the select "IIf(CA.DateAssigned Is Not Null,
IIF(P.[Date] > CA.DateAssigned, Sum(P.Amount),0), 0) AS
Collected," but then I had to add P.Date to the Group By
and the query returned a record for every payment in the
table (even records prior to the DateAssigned), rather
than one record with a sum. Any way to get this to work?

Thanks
Click to expand...


.
Click to expand...
 
Hi,


I am not sure I understand. Definitively, an aggregate cannot appear in
the WHERE clause (but there was none either in the original statement you
posted).

If you have a condition on an aggregate, you have to use a HAVING, not a
WHERE clause.

My suggestion was not to modify the initial WHERE clause at all, but
just the iif( ..., iif( ) ) in your select clause. Not having to add a
P.Date in the SELECT clause, you don't have to include it either in the
GROUP BY clause. Sure, the WHERE clause seems a little bit strange when
examined with the outer joins you use:


....
FROM (( dl LEFT JOIN ca ON... )
LEFT JOIN c ON c.LoadID=dl.LoanID )
LEFT JOIN p ON p.LoanID=dl.LoanID

WHERE
(CA.DateAssigned Is Not Null
And P.Date>CA.DateAssigned
And P.Deleted=False)
OR
(CA.DateAssigned Is Null)


Clearly, p values can be null (table p is on the unpreserved side of an
outer join). It seems you are interested in keeping the records build so far
by the joins, and associated to those null p. We will do that, but first, we
will simplify your WHERE clause. From

a OR ( ~ a AND b )

it is equivalent to

(a OR ~a) AND ( a OR b ) ' distributive

Since a is either true, either false (not null), the previous line is
equivalent to

True AND (a OR b)


which is always equivalent to

a OR b


since TRUE is the neutral element of operator AND (like 0 + x always
returns x, we have True AND x always returns x). So, the original WHERE
clause is equivalent to:


WHERE (ca.DateAssigned IS NULL)
OR
( p.deleted=false
AND
p.Date>ca.DateAssigned
)


and if we wish to still keep records where p.date is null:

WHERE ca.DateAssigned Is Null
OR
p.date Is Null
OR
p.deleted Is Null
OR
( p.deleted = false
AND
p.Date>ca.DateAssigned
)



would not destroy the effect of your outer joins. Note the simple pattern:
test for NULL for each value involved further on.

SO, change the iif( ... iif(... )), in the SELECT, into a Nz, which avoid
the requirement to add an extra unwanted field in the GROUP BY, and modify
the WHERE clause to what just pop out above. Note that those conclusions are
mainly derived from purely technical, mechanical, considerations, so, I may
be out of target about what you really want to accomplish.



Hoping that makes sense,
Vanderghast, Access MVP.


Brad said:
Thank you for your suggestions, but neither of these
worked... the problem is that the where clause eliminates
the records which have CA.DateAssigned not null, but no
payments in the table after CA.DateAssigned. I have to
get the WHERE clause to not remove those records. I
can't put an aggregate function in the where clause to
have an IIF there... is there another way to manipulate
the where clause?

Thanks

Brad
-----Original Message-----
Hi,



Try replacing

IIf(CA.DateAssigned Is Not Null,
IIF(P.[Date] > CA.DateAssigned, Sum(P.Amount),0), 0)

by:

Nz( Sum(P.Amount), 0 )



or by:

iif( Sum(P.Amount) Is Null, 0, Sum(P.Amount) )


Hoping it may help,
Vanderghast, Access MVP


Here's my query so far:

SELECT DL.CustID AS CustID, DL.FullName AS FullName,
DL.LoanID AS LoanID, DL.DateIssued AS DateIssued,
DL.DueDate AS DueDate, DL.Balance AS Balance, DL.OrigBal
AS OrigBal, CA.DateAssigned, IIf(CA.DateAssigned Is Not
Null,IIf(Max(C.DateofCall)>CA.DateAssigned,Max
(C.DateofCall),''),'') AS LastCall, IIf(CA.DateAssigned
Is Not Null,Sum(P.Amount),0) AS Collected
FROM ((qryDelinquentLoan AS DL LEFT JOIN
tblCallAssignment AS CA ON CA.CustID=DL.CustID) LEFT JOIN
tblCalls AS C ON DL.LoanID=C.LoanID) LEFT JOIN
tblPayments AS P ON DL.LoanID=P.LoanID
WHERE (CA.DateAssigned Is Not Null And
P.Date>CA.DateAssigned And P.Deleted=False) Or
(CA.DateAssigned Is Null)
GROUP BY DL.CustID, DL.FullName, DL.LoanID,
DL.DateIssued, DL.DueDate, DL.Balance, DL.OrigBal,
CA.DateAssigned, DL.LoanID
ORDER BY DL.CustID;

I run into trouble with tblPayments (P). I want the
query to return the sum of all payments after a certain
date (P.Date > CA.DateAssigned) but if there have been no
payments after that date, return 0 (there have most
likely been payments before that date.) Currently, if
there are no payments after the date, the records drop
out of the query. I tried to add P.Date to a nested IIF
statement in the select "IIf(CA.DateAssigned Is Not Null,
IIF(P.[Date] > CA.DateAssigned, Sum(P.Amount),0), 0) AS
Collected," but then I had to add P.Date to the Group By
and the query returned a record for every payment in the
table (even records prior to the DateAssigned), rather
than one record with a sum. Any way to get this to work?

Thanks
Click to expand...


.
Click to expand...
Click to expand...
 
Let's see if I can better explain this.

I have a query that identifies the records I want to look
at. You are correct that I always want the results of
that query to come up, regardless of the fields in the
joined tables (thus the left joins).

For a record from that query, I want to get the latest
date called from tblCalls, provided the date is AFTER the
date the record was assigned to a user. It is possible
that the record has not been assigned (CA.DateAssigned =
NULL) or that the record has been assigned but no call
has been made since the assignment. This works fine with
the IIF in the select.

Next, I want to see if there have been any payments made
since the assignment. Again, it is possible that there
has not been an assignment (therefore 0 in payments.) If
there has been an assignment, I want the sum of payments
made after the assignment. There will always be payments
made before the assignment, but I want to ignore those
payments. In my original query, if payments have been
made after the assignment, the query works and provides
the sum of those payments. If, however, there have been
no payments after the assignment, the records drop out of
the query altogether.
Using the Nz in the select worked (i.e., did not throw an
error) but it still did not bring up the records where
there is an assignment but no payment after the
assignment. If I remove the p.date > ca.dateassigned in
the where clause, then the records come back, but the
records have the sum of all payments, not just the
payments since the assignment.

I haven't yet tried to work on the having clause, so I
will work on that while waiting for any other advice. I
hope this clarifies things.

Brad

-----Original Message-----
Hi,


I am not sure I understand. Definitively, an aggregate cannot appear in
the WHERE clause (but there was none either in the original statement you
posted).

If you have a condition on an aggregate, you have to use a HAVING, not a
WHERE clause.

My suggestion was not to modify the initial WHERE clause at all, but
just the iif( ..., iif( ) ) in your select clause. Not having to add a
P.Date in the SELECT clause, you don't have to include it either in the
GROUP BY clause. Sure, the WHERE clause seems a little bit strange when
examined with the outer joins you use:


....
FROM (( dl LEFT JOIN ca ON... )
LEFT JOIN c ON c.LoadID=dl.LoanID )
LEFT JOIN p ON p.LoanID=dl.LoanID

WHERE
(CA.DateAssigned Is Not Null
And P.Date>CA.DateAssigned
And P.Deleted=False)
OR
(CA.DateAssigned Is Null)


Clearly, p values can be null (table p is on the unpreserved side of an
outer join). It seems you are interested in keeping the records build so far
by the joins, and associated to those null p. We will do that, but first, we
will simplify your WHERE clause. From

a OR ( ~ a AND b )

it is equivalent to

(a OR ~a) AND ( a OR b ) ' distributive

Since a is either true, either false (not null), the previous line is
equivalent to

True AND (a OR b)


which is always equivalent to

a OR b


since TRUE is the neutral element of operator AND (like 0 + x always
returns x, we have True AND x always returns x). So, the original WHERE
clause is equivalent to:


WHERE (ca.DateAssigned IS NULL)
OR
( p.deleted=false
AND
p.Date>ca.DateAssigned
)


and if we wish to still keep records where p.date is null:

WHERE ca.DateAssigned Is Null
OR
p.date Is Null
OR
p.deleted Is Null
OR
( p.deleted = false
AND
p.Date>ca.DateAssigned
)



would not destroy the effect of your outer joins. Note the simple pattern:
test for NULL for each value involved further on.

SO, change the iif( ... iif(... )), in the SELECT, into a Nz, which avoid
the requirement to add an extra unwanted field in the GROUP BY, and modify
the WHERE clause to what just pop out above. Note that those conclusions are
mainly derived from purely technical, mechanical, considerations, so, I may
be out of target about what you really want to accomplish.



Hoping that makes sense,
Vanderghast, Access MVP.


Thank you for your suggestions, but neither of these
worked... the problem is that the where clause eliminates
the records which have CA.DateAssigned not null, but no
payments in the table after CA.DateAssigned. I have to
get the WHERE clause to not remove those records. I
can't put an aggregate function in the where clause to
have an IIF there... is there another way to manipulate
the where clause?

Thanks

Brad
-----Original Message-----
Hi,



Try replacing

IIf(CA.DateAssigned Is Not Null,
IIF(P.[Date] > CA.DateAssigned, Sum(P.Amount),0), 0)

by:

Nz( Sum(P.Amount), 0 )



or by:

iif( Sum(P.Amount) Is Null, 0, Sum(P.Amount) )


Hoping it may help,
Vanderghast, Access MVP


Here's my query so far:

SELECT DL.CustID AS CustID, DL.FullName AS FullName,
DL.LoanID AS LoanID, DL.DateIssued AS DateIssued,
DL.DueDate AS DueDate, DL.Balance AS Balance, DL.OrigBal
AS OrigBal, CA.DateAssigned, IIf(CA.DateAssigned Is Not
Null,IIf(Max(C.DateofCall)>CA.DateAssigned,Max
(C.DateofCall),''),'') AS LastCall, IIf (CA.DateAssigned
Is Not Null,Sum(P.Amount),0) AS Collected
FROM ((qryDelinquentLoan AS DL LEFT JOIN
tblCallAssignment AS CA ON CA.CustID=DL.CustID) LEFT JOIN
tblCalls AS C ON DL.LoanID=C.LoanID) LEFT JOIN
tblPayments AS P ON DL.LoanID=P.LoanID
WHERE (CA.DateAssigned Is Not Null And
P.Date>CA.DateAssigned And P.Deleted=False) Or
(CA.DateAssigned Is Null)
GROUP BY DL.CustID, DL.FullName, DL.LoanID,
DL.DateIssued, DL.DueDate, DL.Balance, DL.OrigBal,
CA.DateAssigned, DL.LoanID
ORDER BY DL.CustID;

I run into trouble with tblPayments (P). I want the
query to return the sum of all payments after a certain
date (P.Date > CA.DateAssigned) but if there have
Click to expand...
been
no
payments after that date, return 0 (there have most
likely been payments before that date.) Currently, if
there are no payments after the date, the records drop
out of the query. I tried to add P.Date to a nested IIF
statement in the select "IIf(CA.DateAssigned Is Not Null,
IIF(P.[Date] > CA.DateAssigned, Sum(P.Amount),0), 0) AS
Collected," but then I had to add P.Date to the
Click to expand...
Group
By
and the query returned a record for every payment in the
table (even records prior to the DateAssigned), rather
than one record with a sum. Any way to get this to work?

Thanks


.
Click to expand...
Click to expand...


.
Click to expand...
 
Back
Top