Sum by month query

[BobNTN]

Would someone be so kind as to tell me why I get Err in the Runtot field from this query ? It seems to run fine except no totals.

SELECT DatePart("yyyy",[Recdate]) AS AYear, DatePart("m",[Recdate]) AS AMonth, DSum("Creditamt","DatePart('m', [Recdate])<=" & [AMonth] & " And DatePart('yyyy', [Recdate])<=" & [AYear] & "") AS RunTot, Format([Recdate],"mmm") AS FDate
FROM TblPayments
GROUP BY DatePart("yyyy",[Recdate]), DatePart("m",[Recdate]), Format([Recdate],"mmm")
ORDER BY DatePart("yyyy",[Recdate]), DatePart("m",[Recdate]), Format([Recdate],"mmm");

 

[BobNTN]

Never mind, I found the problem. Left out the table name in the expression.

However, would someone tell me how to get "FDate" in that query to print/display the whole month name rather than just the first 3 letters ? Or is that possible ?

 

[raskew]

Change instances of:
Format([Recdate],"mmm")
To
Format([Recdate],"mmmm")

Bob

 

[BobNTN]

Heck, knew it would be simple.

One more thing. Using this query in a report, I get no format choices in the field 'RunTot' so I can print / display it in currency format. Nor will it take a format, (that I can find) in the query layout.
Any idea on that ?

 

[raskew]

Try a variation of this:

x = 123.45
? "$" & format(x, "##,##0.00")
$123.45

No guarantees but I think it may work.

Bob

 

[Brianwarnock]

Wrap Format(......,"Currency") round it.

Brian

 

[BobNTN]

Wrap Format(......,"Currency") round it.

Brian

around the expression ??

 

[Brianwarnock]

Yep,

Format(Dsum(.........),"Currency") As RunTot

Brian

 

[BobNTN]

Thanks Brian.

However, I'm back to square one.
I just realized this is NOT a monthly total like I want.
I want a total BY MONTH then a total BY YEAR.
Oh well, back to the drawing board.

 

[BobNTN]

Well I can use this running total for a year's total.
Anyone got an idea how I can get monthly totals ?

 

[BobNTN]

Ok, this gives me the monthly totals but the months aren't in calendar order.
How can I get them in that order ?

SELECT Sum(TblPayments.Creditamt) AS SumOfCreditamt, Format([Recdate],"mmmm") AS Expr1
FROM TblPayments
GROUP BY Format([Recdate],"mmmm");

 

[twoplustwo]

Order By Month?

 

[BobNTN]

Yes, like Jan, Feb, Mar, Apr. This doesn't do that, they are in alphabetical order.

What I need is a monthly total with months in the correct order and a yearly total at the bottom.

 

[Brianwarnock]

Month(datefld) returns the month number, so you need a no show field of that just to sort on.

brian

 

[Brianwarnock]

To really achieve what you want I think ypu need to create a report and then you can put the year Total in the report footer, you cannot get the Year Total in the monthly Total query, you would need another query, maybe you could union the 2, but I would go the report route.

Brian

 

[BobNTN]

ok I got it with your help.

SELECT Sum(TblPayments.Creditamt) AS SumOfCreditamt, Format([Recdate],"mmmm") AS AMonth, DatePart("yyyy",[Recdate]) AS AYear
FROM TblPayments
GROUP BY Format([Recdate],"mmmm"), DatePart("yyyy",[Recdate]), DatePart("m",[Recdate])
ORDER BY DatePart("m",[Recdate]);

Now, how do I do a total of the year(s) in the report that will change for each year ?

 

[BobNTN]

Ok - I got it.
Thanks everyone