Select Last payment date and amount of a group

  • Thread starter Thread starter Kathy Webster
  • Start date Start date
K

Kathy Webster

I am perplexed. Seems like it should be so easy, but...Can you help? My
table has:

Client PaymentDate Amount
AutoIncrementingPaymentID
3 5/20/06 $10
10

3 2/20/06 15
15

2 8/15/06 10
17

2 9/15/06 15
24

2 7/14/06 10
27

1 10/19/06 20
34

1 10/18/06 10
35

1 10/17/06 30
36

I want a query that will return the LAST payment date and dollar amount only
for each client.
So in this scenario, I want the query to return only the following:

Client PaymentDate Amount
AutoIncrementingPaymentID
1 10/19/06 20 34
2 9/15/06 15 24

3 5/20/06 10 10

As you can see, I can't assume that the highest AutoIncrementingPaymentID is
the last payment date, because payments may not have been entered
cronologically.

Can anyone help?
TIA,
Kathy
 
Kathy,

I would use a two-tier query. QryClientInfoSub might be

SELECT Client, Max(PaymentDate) As MaxOfPaymentDate FROM tblClientInfoTable
GROUP BY Client;

An upper-tier query, maybe qryClientLatestInfo, might be

SELECT Client, PaymentDate, Amount, AutoIncrementing, PaymentID FROM
tblClientInfoTable INNER JOIN qryClientInfoSub ON tblClientInfoTable.Client =
qryClientInfoSub.Client AND tblClientInfoTable.PaymentDate = qryClientInfoSub.
MaxOfPaymentDate GROUP BY tblClientInfoTable.Client, tblClientInfoTable.
PaymentDate, tblClientInfoTable.Amount, tblClientInfoTable.AutoIncrementing,
tblClientInfoTable.PaymentID ;

Hope this helps,

Sam
 
Sam,
I thought I'd try your suggestion first, since I could partially grasp it...
However...
I made the sub query, it works fine.

When trying to save main query, I am getting error:

Invalid use of '.', '!' or '()' in query expression.
 
Assumption
-- Only one payment is made per client per date

SELECT Client, PaymentDate, Amount
FROM YourTable as T1
WHERE T1.PaymentDate =
(SELECT Max(T2.PaymentDate)
FROM YourTable as T2
WHERE T2.Client = T1.Client)

Or faster
SELECT T1.Client, PaymentDate, Amount
FROM YourTable as T1 INNER JOIN
(SELECT Client, Max(T2.PaymentDate) as LastPay
FROM YourTable as T2
Group By Client) as T3
ON T1.Client = T3.Client AND T1.PaymentDate = T3.LastPay

Explanation - T1, T2 are just references to a copy of your table. T3 is a name
for the sub-query.
 
Kathy,

Please copy and paste your SQL statement.

Sam

Kathy said:
Sam,
I thought I'd try your suggestion first, since I could partially grasp it...
However...
I made the sub query, it works fine.

When trying to save main query, I am getting error:

Invalid use of '.', '!' or '()' in query expression.
Kathy,
Click to expand...
[quoted text clipped - 71 lines]
Click to expand...
 
SELECT FileNo, PaymentDate, AmountPaid, PaymentType, FileStatus FROM
t_StateFarmSub INNER JOIN q_StatusReport_PaymentMax_sub ON
t_StateFarmSub.FileNo =
q_StatusReport_PaymentMax_sub.FileNo AND t_StateFarmSub.PaymentDate =
q_StatusReport_PaymentMax_sub.
MaxOfPaymentDate GROUP BY t_StateFarmSub.FileNo, t_StateFarmSub.
PaymentDate, t_StateFarmSub.AmountPaid, t_StateFarmSub.PaymentType,
t_StateFarmSub.FileStatus ;

OfficeDev18 via AccessMonster.com said:
Kathy,

Please copy and paste your SQL statement.

Sam

Kathy said:
Sam,
I thought I'd try your suggestion first, since I could partially grasp
it...
However...
I made the sub query, it works fine.

When trying to save main query, I am getting error:

Invalid use of '.', '!' or '()' in query expression.
Kathy,
Click to expand...
[quoted text clipped - 71 lines]
TIA,
Kathy
Click to expand...
Click to expand...
Click to expand...
 
Then try

SELECT T1.Client, PaymentDate, SUM(Amount) at TotalPaid
FROM YourTable as T1 INNER JOIN
(SELECT Client, Max(T2.PaymentDate) as LastPay
FROM YourTable as T2
Group By Client) as T3
ON T1.Client = T3.Client AND T1.PaymentDate = T3.LastPay
GROUP BY T1.Client, T1.PaymentDate
 
After pasting this and replacing YourTable with tblClientPayments,
I get this error when trying to save:
Syntax error (missing operator) in query expression 'SUM(Amount) at
TotalPaid'.
 
TYPO on my part the "at" should be "as"


SELECT T1.Client, PaymentDate, SUM(Amount) as TotalPaid
FROM tblClientPayments as T1 INNER JOIN
(SELECT Client, Max(T2.PaymentDate) as LastPay
FROM tblClientPayments as T2
Group By Client) as T3
 
Back
Top