Calculating a year to date margin in query?

[RahelWalther]

I have a report that is based on a query with two parameters:
EmployeeID and Month/Year

I was asked to display a Monthly Margin ((Sum Equipment$-Sum Equip Cost)/(Sum Equipment$), which is very easy. But, they also want me to display a Year to Date Margin for every month. In a report for August, they want me to display Margin calculated over January until August. In a report for September, they want me to display Margin calculated over January until September. All I know to do is calculate a yearly margin, but then, If it is september and they look at the previous august, the yearly margin in the report will also include numbers for september...:

Code for yearly margin:
SELECT DISTINCTROW Format$([tblSales].[SignedDate],'yyyy') AS [SignedDate By Year], Sum([tblSales].[Equipment$]) AS [Sum Of Equipment$], Sum([tblSales].[Equip Cost]) AS [Sum Of Equip Cost], [tblSales].[EmployeeID]
FROM tblSales
GROUP BY Format$([tblSales].[SignedDate],'yyyy'), [tblSales].[EmployeeID], Year([tblSales].[SignedDate]);

Is there no simple aggregate function in Access, such as if August 2004 gets entered as parameter, Equipment$ from January to August gets summed up, if one enters October 2004, Equipment$ from January to October gets summed up (same for cost)?

Any help is greatly appreciated.

Rahel Luisa

 

[Jon K]

It's much easier to calculate the Year To Date monthly margins in the report than in the query.

I have attached a sample database. You can open the form, select an EmployeeID and a month and click on the command button to preview the report.
.

 

[RahelWalther]

Thank you very much for your help. I was too fixed on solving the problem in a query.

Rahel