Prev: SQL for specific pattern of output?
Next: Search for Words in Documents
From: Emma on 7 Apr 2010 16:16
I have quite a complicated query:
SELECT [Loans - Payment History].DateOfPayment, [Loans - Table].[In
Default], [Loans - Table].AmountofLoan, [Loans - Table].[Client ID], [Loans -
Table].DateofLoanAgreementSigned, [Tbl Client Information].[Last Name], [Tbl
Client Information].[First Name], [Tbl Client Information].[Session Start
Date 2], [Tbl Client Information].[Session Start Date], [Loans - Payment
History].PaymentDue
FROM ([Loans - Table] INNER JOIN [Loans - Payment History] ON [Loans -
Table].ID = [Loans - Payment History].[LoanPayment ID]) INNER JOIN [Tbl
Client Information] ON [Loans - Table].[Client ID] = [Tbl Client
Information].[Client ID]
WHERE ((([Loans - Payment History].DateOfPayment) Between [Beginning Date]
And [Ending Date]) AND (([Loans - Table].[In Default])<>(0)))
ORDER BY [Loans - Payment History].DateOfPayment DESC;

It's doing what I like however I only need the last [Loans - Payment
History].DateOfPayment date. I'm not sure if I need to use a cross tab to get
rid of the extra rows? For example Hiei Clume comes up three times as she has
3 payments I only want the last payment to appear? Thanks in advance,Emma
From: KARL DEWEY on 7 Apr 2010 17:00
Try these two queries --
qryLast_Payment --
SELECT Max([Loans - Payment History].DateOfPayment) AS Last_Payment
FROM ([Loans - Payment History] INNER JOIN [Loans - Table] ON [Loans -
Payment History].[LoanPayment ID] =[Loans - Table].ID) INNER JOIN [Tbl
Client Information] ON
[Loans - Table].[Client ID] = [Tbl Client Information].[Client ID];

SELECT [Loans - Payment History].DateOfPayment, [Loans - Table].[In
Default], [Loans - Table].AmountofLoan, [Loans - Table].[Client ID], [Loans -
Table].DateofLoanAgreementSigned, [Tbl Client Information].[Last Name], [Tbl
Client Information].[First Name], [Tbl Client Information].[Session Start
Date 2], [Tbl Client Information].[Session Start Date], [Loans - Payment
History].PaymentDue
FROM ([Loans - Table] INNER JOIN [Loans - Payment History] ON [Loans -
Table].ID = [Loans - Payment History].[LoanPayment ID]) INNER JOIN [Tbl
Client Information] ON [Loans - Table].[Client ID] = [Tbl Client
Information].[Client ID]
WHERE ((([Loans - Payment History].DateOfPayment) Between [Beginning Date]
And [Ending Date]) AND (([Loans - Table].[In Default])<>(0))) AND [Loans -
Payment History].DateOfPayment = qryLast_Payment.Last_Payment
ORDER BY [Loans - Payment History].DateOfPayment DESC;


--
Build a little, test a little.


"Emma" wrote:

> I have quite a complicated query:
> SELECT [Loans - Payment History].DateOfPayment, [Loans - Table].[In
> Default], [Loans - Table].AmountofLoan, [Loans - Table].[Client ID], [Loans -
> Table].DateofLoanAgreementSigned, [Tbl Client Information].[Last Name], [Tbl
> Client Information].[First Name], [Tbl Client Information].[Session Start
> Date 2], [Tbl Client Information].[Session Start Date], [Loans - Payment
> History].PaymentDue
> FROM ([Loans - Table] INNER JOIN [Loans - Payment History] ON [Loans -
> Table].ID = [Loans - Payment History].[LoanPayment ID]) INNER JOIN [Tbl
> Client Information] ON [Loans - Table].[Client ID] = [Tbl Client
> Information].[Client ID]
> WHERE ((([Loans - Payment History].DateOfPayment) Between [Beginning Date]
> And [Ending Date]) AND (([Loans - Table].[In Default])<>(0)))
> ORDER BY [Loans - Payment History].DateOfPayment DESC;
>
> It's doing what I like however I only need the last [Loans - Payment
> History].DateOfPayment date. I'm not sure if I need to use a cross tab to get
> rid of the extra rows? For example Hiei Clume comes up three times as she has
> 3 payments I only want the last payment to appear? Thanks in advance,Emma
From: John W. Vinson on 7 Apr 2010 17:01
On Wed, 7 Apr 2010 13:16:02 -0700, Emma <Emma(a)discussions.microsoft.com>
wrote:

>I have quite a complicated query:
>SELECT [Loans - Payment History].DateOfPayment, [Loans - Table].[In
>Default], [Loans - Table].AmountofLoan, [Loans - Table].[Client ID], [Loans -
>Table].DateofLoanAgreementSigned, [Tbl Client Information].[Last Name], [Tbl
>Client Information].[First Name], [Tbl Client Information].[Session Start
>Date 2], [Tbl Client Information].[Session Start Date], [Loans - Payment
>History].PaymentDue
>FROM ([Loans - Table] INNER JOIN [Loans - Payment History] ON [Loans -
>Table].ID = [Loans - Payment History].[LoanPayment ID]) INNER JOIN [Tbl
>Client Information] ON [Loans - Table].[Client ID] = [Tbl Client
>Information].[Client ID]
>WHERE ((([Loans - Payment History].DateOfPayment) Between [Beginning Date]
>And [Ending Date]) AND (([Loans - Table].[In Default])<>(0)))
>ORDER BY [Loans - Payment History].DateOfPayment DESC;
>
>It's doing what I like however I only need the last [Loans - Payment
>History].DateOfPayment date. I'm not sure if I need to use a cross tab to get
>rid of the extra rows? For example Hiei Clume comes up three times as she has
>3 payments I only want the last payment to appear? Thanks in advance,Emma

Use a Subquery. Put a criterion on DateOfPayment of

=(SELECT Max([DateOfPayment] FROM [Loans- Payment History] AS X
WHERE X.[Client ID] = [Loans - Table].[Client ID])

--

John W. Vinson [MVP]
 | 
Pages: 1
Prev: SQL for specific pattern of output?
Next: Search for Words in Documents