C
Casper1963
I have a loan database that I want to write a query to reflect all active
loans with or without a current month's payments. In other words, I want it
to select all active loans even if there was no activety during the month. I
have 1 query that pulls out all active loans; qryActiveLoans. I then create a
left join (2) with that query Grouping on the Loan# and summing the Principal
and Interest fields where the payment Date is between #10/01/07# and
#10/31/07#. I need to group on loan the number because some loans have more
than one transaction in the month.
The problem is that the query does not select the loans that didn't have any
payments during the month. I thought that a left join (2) will pull out ALL
the records from the qryActiveLoans query. And yes, I have qryActiveLoans on
the left side. I've tried adding the word ALL after the SELECT statement but
that does not work. I've also tried various forms of nz without success. The
SQL looks like this.
SELECT qryActiveLoans.[Loan#], Sum(Payments.Principal) AS SumOfPrincipal,
Sum(Payments.Interest) AS SumOfInterest
FROM qryActiveLoans LEFT JOIN Payments ON qryActiveLoans.[Loan#] =
Payments.[Loan#]
WHERE (((Payments.Date) Between #10/1/2007# And #10/31/2007#))
GROUP BY qryActiveLoans.[Loan#];
If I don't include the Where clause it will list all the active loans but it
will also sum all the Principal and Interest paid for the entire life of the
loan.
Any help would be appreciated.
loans with or without a current month's payments. In other words, I want it
to select all active loans even if there was no activety during the month. I
have 1 query that pulls out all active loans; qryActiveLoans. I then create a
left join (2) with that query Grouping on the Loan# and summing the Principal
and Interest fields where the payment Date is between #10/01/07# and
#10/31/07#. I need to group on loan the number because some loans have more
than one transaction in the month.
The problem is that the query does not select the loans that didn't have any
payments during the month. I thought that a left join (2) will pull out ALL
the records from the qryActiveLoans query. And yes, I have qryActiveLoans on
the left side. I've tried adding the word ALL after the SELECT statement but
that does not work. I've also tried various forms of nz without success. The
SQL looks like this.
SELECT qryActiveLoans.[Loan#], Sum(Payments.Principal) AS SumOfPrincipal,
Sum(Payments.Interest) AS SumOfInterest
FROM qryActiveLoans LEFT JOIN Payments ON qryActiveLoans.[Loan#] =
Payments.[Loan#]
WHERE (((Payments.Date) Between #10/1/2007# And #10/31/2007#))
GROUP BY qryActiveLoans.[Loan#];
If I don't include the Where clause it will list all the active loans but it
will also sum all the Principal and Interest paid for the entire life of the
loan.
Any help would be appreciated.