Select Last payment date and amount of a group

[Kathy Webster]

I apologize if this posts twice.

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

 

[Guest]

Try this ---
SELECT T.Client, T.PaymentDate, T.Amount, T.AutoIncrementingPaymentID,
(SELECT COUNT(*)
FROM [Kathy] T1
WHERE T1.Client = T.Client
AND T1.PaymentDate >= T.PaymentDate) AS Rank
FROM Kathy AS T
WHERE ((((SELECT COUNT(*)
FROM [Kathy] T1
WHERE T1.Client = T.Client
AND T1.PaymentDate >= T.PaymentDate))=1))
ORDER BY T.Client, T.PaymentDate DESC;

 

[Kathy Webster]

Karl,
Thanks. Pardon my lameness:
What is [Kathy] shorthand for?
What is T. shorthand for?
What is T1. shorthand for?

Kathy

Try this ---
SELECT T.Client, T.PaymentDate, T.Amount, T.AutoIncrementingPaymentID,
(SELECT COUNT(*)
FROM [Kathy] T1
WHERE T1.Client = T.Client
AND T1.PaymentDate >= T.PaymentDate) AS Rank
FROM Kathy AS T
WHERE ((((SELECT COUNT(*)
FROM [Kathy] T1
WHERE T1.Client = T.Client
AND T1.PaymentDate >= T.PaymentDate))=1))
ORDER BY T.Client, T.PaymentDate DESC;

I apologize if this posts twice.

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 Webster]

Oh. What would be the difference between T and T1? When would I use the
equivalent of Kathy, when would I use the equiv of T, and when would I use
the equiv of T1?

To test the query I create a table named Kathy with fields as you
outlined.
T and T1 are alias of the table Kathy.
Just substitute your table name and field names in the SQL.

Karl,
Thanks. Pardon my lameness:
What is [Kathy] shorthand for?
What is T. shorthand for?
What is T1. shorthand for?

Kathy

Try this ---
SELECT T.Client, T.PaymentDate, T.Amount, T.AutoIncrementingPaymentID,
(SELECT COUNT(*)
FROM [Kathy] T1
WHERE T1.Client = T.Client
AND T1.PaymentDate >= T.PaymentDate) AS Rank
FROM Kathy AS T
WHERE ((((SELECT COUNT(*)
FROM [Kathy] T1
WHERE T1.Client = T.Client
AND T1.PaymentDate >= T.PaymentDate))=1))
ORDER BY T.Client, T.PaymentDate DESC;


:

I apologize if this posts twice.

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 Webster]

OK. I figured out the T's and T1 references ,etc. Now, I sure wish I
understood your sql statement, because it works.

Doesn't it seem like it would be a common request to need other fields, or
bits of data from a MAX record or a LAST record? I mean, besides just the
MAX or LAST field value?

I am not that familiar with subqueries. The T and T1 are the first and
second instance of alias for the table name.

Search on subqueries to find more information.

Oh. What would be the difference between T and T1? When would I use the
equivalent of Kathy, when would I use the equiv of T, and when would I
use
the equiv of T1?

To test the query I create a table named Kathy with fields as you
outlined.
T and T1 are alias of the table Kathy.
Just substitute your table name and field names in the SQL.

:

Karl,
Thanks. Pardon my lameness:
What is [Kathy] shorthand for?
What is T. shorthand for?
What is T1. shorthand for?

Kathy

Try this ---
SELECT T.Client, T.PaymentDate, T.Amount,
T.AutoIncrementingPaymentID,
(SELECT COUNT(*)
FROM [Kathy] T1
WHERE T1.Client = T.Client
AND T1.PaymentDate >= T.PaymentDate) AS Rank
FROM Kathy AS T
WHERE ((((SELECT COUNT(*)
FROM [Kathy] T1
WHERE T1.Client = T.Client
AND T1.PaymentDate >= T.PaymentDate))=1))
ORDER BY T.Client, T.PaymentDate DESC;


:

I apologize if this posts twice.

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