Left Join

G

Guest

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

John Spencer

The problem is that applying criteria (WHERE) against the Payments table
negates the LEFT JOIN and turns it back into an INNER JOIN

One solution is to use another query to get the sums in a separate query -
qMonthlyPayment
SELECT [Loan#]
, Sum(Principal) as SumOfPrincipal
, Sum(Payments.Interest) AS SumOfInterest
FROM Payments
WHERE Payments.Date Between #10/1/2007# And #10/31/2007#

Now use that query in place of the Payments table
SELECT qryActiveLoans.[Loan#]
, SumOfPrincipal
, SumOfInterest
FROM qryActiveLoans LEFT JOIN qMonthlyPayment ON qryActiveLoans.[Loan#] =
qMonthlyPayment.[Loan#]

If your field names and table names consist of only letters, numbers, and
underscores then you could do the above in one query. However the use of #
in field Loan# requires you to use square brackets []. And you can't use
subqueries in the from clause if the subquery requires square brackets (in
Access).
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
G

Guest

Yes, that worked. Thank you for your response!

John Spencer said:
The problem is that applying criteria (WHERE) against the Payments table
negates the LEFT JOIN and turns it back into an INNER JOIN

One solution is to use another query to get the sums in a separate query -
qMonthlyPayment
SELECT [Loan#]
, Sum(Principal) as SumOfPrincipal
, Sum(Payments.Interest) AS SumOfInterest
FROM Payments
WHERE Payments.Date Between #10/1/2007# And #10/31/2007#

Now use that query in place of the Payments table
SELECT qryActiveLoans.[Loan#]
, SumOfPrincipal
, SumOfInterest
FROM qryActiveLoans LEFT JOIN qMonthlyPayment ON qryActiveLoans.[Loan#] =
qMonthlyPayment.[Loan#]

If your field names and table names consist of only letters, numbers, and
underscores then you could do the above in one query. However the use of #
in field Loan# requires you to use square brackets []. And you can't use
subqueries in the from clause if the subquery requires square brackets (in
Access).
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Casper1963 said:
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.
 

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


Top