Summarize data on one line

[jasonhuibers]

SELECT Acct_Code, Address, Rent_Date, Rent_Amount FROM Renter_Info

From my query here are the results.

Acct_Code Address Rent_Date Rent_Amount
123456 123 Main St 12/25/10 $100.00
123456 123 Main St 01/01/11 $250.00
123456 123 Main St 12/23/10 $50.00

I want to take this a step further and have it like this all on one line as a summary:

Acct_Code Address Rent_Date_From_To Rent_Amount
123456 123 Main St 12/23/10 - 01/01/11 $400.00

Basically Grouped by AcctCode, Address and have the min and max date for the column Rent_Date_From_To and a sum of rent amount

 

[dhookom]

Try something like:

SELECT Acct_Code, Address, Min(Rent_Date) + ' - ' + Max(Rent_Date) Rent_Date_From_To, Sum(Rent_Amount) as Rent_Amount
FROM Renter_Info
GROUP BY Acct_Code, Address

Duane
Hook'D on Access
MS Access MVP