Select Last payment date and amount of a group

[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

 

[OfficeDev18 via AccessMonster.com]

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

 

[Kathy Webster]

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.

 

[John Spencer]

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.

 

[OfficeDev18 via AccessMonster.com]

Kathy,

Please copy and paste your SQL statement.

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.

Kathy,

[quoted text clipped - 71 lines]

 

[Kathy Webster]

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 ;

Kathy,

Please copy and paste your SQL statement.

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.

Kathy,

[quoted text clipped - 71 lines]

TIA,
Kathy

 

[Kathy Webster]

Sorry, John, that assumption is not the case here.
Thanks very much anyway.
Kathy

 

[John Spencer]

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

 

[Kathy Webster]

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

 

[John Spencer]

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