Monthly Totals and Fiscal Year to Date total 1

[Melagan]

Greetings,

I have a database that tracks income from closed escrows. Each escrow is given it's own record that contains the following relevant fields:

Closing Date
Escrow Fee
Policy Fee
Misc Fees

Ultimately, I need a report that will show the SUM([Escrow Fee] + [Policy Fee] + [Misc Fees]) for each month on Closing Dates Between #12/01/2004# and #11/30/2005#.
The report needs to show only monthly totals, not every single closed transaction.

Obviously I'll need to start with building the right query to group all of the records by month, like Month: Format([Closing Date], "mmm yyyy"), but how do I get the query to give one "sub total", if you will, by each month, and include the Closing Date criteria?

The Detail Section of the report should come out looking like this:

December 2004 $ 300,000
January 2005 $ 425,000
February 2005 $ 450,000
...
November 2005 $ 650,000

Building the report is the easy part; building the datasource for the report has me stuck. Any ideas, thoughts, or solutions would be greatly appeciated!

 

[PHV]

Something like this ?
SELECT Format([Closing Date], "yyyymm") As SortOrder
, Format([Closing Date], "mmmm yyyy") As [Month]
, SUM(Nz([Escrow Fee],0) + Nz([Policy Fee],0) + Nz([Misc Fees],0)) As Total
FROM yourTable
WHERE [Closing Date] Between #2004-12-01# And #2005-11-30#
GROUP BY Format([Closing Date], "yyyymm"), Format([Closing Date], "mmmm yyyy")
ORDER BY Format([Closing Date], "yyyymm")

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Melagan]

Excellent, that worked perfectly. Thank you PHV!