Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations MikeeOK on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Monthly Totals and Fiscal Year to Date total 1

Status
Not open for further replies.

Melagan

MIS
Nov 24, 2004
443
US
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!
 
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
 
Excellent, that worked perfectly. Thank you PHV!
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top