Re: Find records without any corresponding record in another table

From: Gerald Stanley (gcstanley_at_dsl.pipex.com)
Date: 03/31/04 

Date: Wed, 31 Mar 2004 10:43:07 -0800

Try

SELECT tblLoanData.LoanID, Max(tblPayments.Date) AS LastPayment
FROM tblLoanData LEFT JOIN tblPayments ON
tblLoanData.LoanID = tblPayments.LoanID
GROUP BY tblLoanData.LoanID, tblLoanData.Deleted,
tblPayments.Deleted, tblPayments.Type
HAVING (((tblLoanData.Deleted)=False) AND
((tblPayments.Deleted)=False) AND
((tblPayments.Type)='Full/Partial Payment'))
UNION
SELECT tblLoanData.LoanID , Null
FROM tblLoanData
WHERE tblLoanData.LoanID NOT IN (SELECT DISTINCT
tblPayments.LoanID FROM tblPayments)

Hope This Helps
Gerald Stanley MCSD
>-----Original Message-----
>I have the following query:
>
>SELECT tblLoanData.LoanID, Max(tblPayments.Date) AS
LastPayment
>FROM tblLoanData LEFT JOIN tblPayments ON
tblLoanData.LoanID = tblPayments.LoanID
>GROUP BY tblLoanData.LoanID, tblLoanData.Deleted,
tblPayments.Deleted, tblPayments.Type
>HAVING (((tblLoanData.Deleted)=False) AND
((tblPayments.Deleted)=False) AND
((tblPayments.Type)='Full/Partial Payment'))
>
>So I want the LoanID and last payment date (loan is not
deleted, payment is not deleted)
>This works fine unless there is no payment for a
particular loan. How do I rework the query to give me a
null value for last payment if there is no payment record?
>
>Thanks in advance!
>.
>

Relevant Pages

  • Find records without any corresponding record in another table
    ... FROM tblLoanData LEFT JOIN tblPayments ON tblLoanData.LoanID = tblPayments.LoanID ... So I want the LoanID and last payment date ... This works fine unless there is no payment for a particular loan. ... How do I rework the query to give me a null value for last payment if there is no payment record? ...
    (microsoft.public.access.queries)
  • Re: How do I caluclate an Annual Percentage Rate in Excel?
    ... In regard to that $1,693.05 payment reference for cells a42:a365, ... the inflow is the initial loan amount ... account the prepaid interest illustrates an APR of 7.144% (note: ...
    (microsoft.public.excel.worksheet.functions)
  • Re: FV function (pv argument)
    ... >To find out how much you have paid out on a loan, ... >years making monthly repayments with the first due 1 month after draw ... >payment one period after draw down)." ... >The repayments accumulate to: ...
    (microsoft.public.excel.worksheet.functions)
  • Re: loan payments
    ... This isn't a real life situation. ... Instructor wanted us to use beginning of loan rather then end which is the ... one answer to your question is to use CUMIPMT(). ... periodic payment might be computed in A1 as: ...
    (microsoft.public.excel.worksheet.functions)
  • Re: loan payments
    ... This isn't a real life situation. ... us to use beginning of loan rather then end which is the default. ... one answer to your question is to use CUMIPMT(). ... periodic payment might be computed in A1 as: ...
    (microsoft.public.excel.worksheet.functions)