SQL: Selecting "Latest Transaction" & "Sum" them up

[Guest]

I have a table, recording different clients' transactions during a month:

Client_ID Client_Name Purchase_Amount($) Purchase_date
101 AAA 1000
1/Oct/2007
102 BBB 250
2/Oct/2007
103 CCC 800
3/oct/2007
101 AAA 200
4/oct/2007
101 AAA 500
5/oct/2007

------------------------------------------------------------------------------------

I'll have to sum them up by using "sum(Purchase_amount)"
while i'd like to show the lastest "purchase date".
------------------------------------------------------------------------------------


for example: The output should look like this:

Client_ID Client_Name Purchase_Amount($) Purchase_date
101 AAA 1700
5/Oct/2007
102 BBB 250
2/Oct/2007
---------------------------------------------------------------------------------------

i've got the total purchse amount from "Client AAA" & The latest "purchasing
date"


Experts, please help me !!

i urgently need an SQL for this ! ! !

thanks again!

 

[John Spencer]

Then include the Purchase_Date in the query and select MAX as the Total

In SQL view it would look something like the following.

SELECT Client_ID, Client_Name
, Sum([Purchase_Amount($)] ) as TotalPurchase
, Max(Purchase_Date) as LastPurchase
FROM TheTable
GROUP BY Client_ID, Client_Name

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Guest]

Try something like:

SELECT Client_ID, Client_Name, Sum(Purchase_Amount) as TotalPurchase,
Max(Purchase_Date) as MostRecentPurchase
GROUP BY Client_ID, Client_Name

You will need to add a WHERE clause in there to limit which records you want
to include in the TotalPurchase and MostRecentPurchase fields.

HTH
Dale