Monthly running total 2

[TCARPENTER]

I hope I'm in the right spot for this...

I have a small table:

Account_Activity
[ul]
[li]AA_Recnum - PK[/li]
[li]Tenant_Name - FK[/li]
[li]AA_TDate - Date - Transaction date[/li]
[li]AA_TAmount - Currency - Tennant amount[/li]
[li]AA_SAmount - Currency - Subsidy amount[/li]
[/ul]

I need to create a summary query by month, showing whether they're behind or paid up. I keep getting close, and I've searched through all the threads but I can't seem to get what I need. When I group on name and date, I get a line for each transaction date (sometimes there can be many). How can I get what I need?

 

[fneily]

In the QBE pane (query design view), place your fields Tenant_Name, AA_TDate, AA_TAmount, and AA_SAmount.
Click on the Totals button. (button with sigma, looks like an E)
Under Tenant_Name it should say Group BY.
Under AA_TAmount and AA_SAmount change it to sum.
Under AA_TDate change it to Where.
In the criteria row under AA_TDate, put:
Between #11/1/2008# And #11/30/2008# or whatever month you want.

 

[fneily]

Forgot to tell you to UNcheck the box on the show row under AA_TDate. If you leave it checkec, Access will tell you to uncheck it.

 

[PHV]

SELECT Tenant_Name, Format(AA_TDate,"yyyymm") AS [Month], Sum(AA_TAmount) AS Tennant, Sum(AA_SAmount) AS Subsidy
FROM Account_Activity
GROUP BY Tenant_Name, Format(AA_TDate,"yyyymm")

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[TCARPENTER]

Wow! Thanks guys, I figured it couldn't be as hard as I was making it! Have some stars!