MTD and YTD Totals

[josros60]

I have an sales & charges database, so they will enter every day sales and charges and then run a reports every day, how can show in my report the mtd and ytd totals?

here my report query:

SELECT Transactions.SelectedPrint, Transactions.TransactionDate, Transactions.TransactionID, Transactions.EmployeeID, [Transactions Details].TransactionID, [Transactions Details].CodeID, [Transactions Details].Amount, CodesCategory.CodeCategoryID, CodesCategory.CategoryName, DateAdd("m",1,DateSerial(Format([TransactionDate],"y"),Format([TransactionDate],"m"),1))-1 AS EOMonth
FROM CodesCategory INNER JOIN ((Transactions INNER JOIN [Transactions Details] ON Transactions.TransactionID = [Transactions Details].TransactionID) INNER JOIN Codes ON [Transactions Details].CodeID = Codes.CodeID) ON CodesCategory.CodeCategoryID = Codes.CodeCategoryID
WHERE (((Transactions.SelectedPrint)=Yes));

thank you

 

[Montez659]

J,

This might not be the most efficient answer, but seeing as no one has answered yet, I will throw my two cents in.

I would create another query that limits the records retrieved by whatever the date field is. In the query you can use the sum button to get a total for the query and reference that total in the report.

Something like

(Month([thisIsTheDateField]) = Month(Date())) AND (Year([thisIsTheDateField]) = Year(Date()))

would be your limiting string in the query (this is not even close to the string you will need - it is hard for me to figure exactly what unless I am looking and playing around with it).

And not quite sure how to reference the sum that you get from the query, though I am sure there are resources out there for that.

Hope I have given you some direction. If not, well, at least I bumped the thread up for you!