Cutting excess rows

  • Thread starter Thread starter Emma
  • Start date Start date
E

Emma

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
 
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;
 
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
Click to expand...

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])
 
Back
Top