I am trying to calculate a YTD value on a Subform (Datasheet View).
I have two tables, tblPaychecks and tblDeductions. tblDeductions is a child of tblPaychecks... tblDeductions.Paycheck_ID = tblPaychecks.ID
I am trying to display a form that shows the Paycheck details on top, and at the bottom a Datasheet view of all the deductions on the check being viewed. So far, this portion all works perfectly. The problem is, I also would like to add a calculated column to the Deductions Subform that shows a sum of all the deductions for the specified "deduction type" in the current fiscal year. I've tried using Domain based lookups, but ended up hitting a "string size limit" when defineing the ControlSource code. I then though of trying to build the code using VBA, but because of the way Form Events fire, i can't seem to get the values I need.
Attached is an image of my form with sample data. For each "Deduction" row I need to calculate the YTD for the Fiscal and Calendar year. The code should be the same except for the "Year Range" filter, so if I can get one to work, the other should be the same logic.
![Screenshot of Parent/Child Form]()
Anyone have any suggestions?
It is possible to calculate YTD using SQL and queries, but it is very time consuming. From my experience the quickest way is using VBA to calculate the YTD.
I do this by:
Create a 'Crosstab query' where your criteria is columns, the month/period is the row and the amount is the first(value).
This way you can use VBA to run through each field and add up all the values for the time period.
This can also be used for QTD (Code needs changing though)
You can either run this as a batch operation, or on the fly for the On Current Event where it calculates it for the selected Paycheck and store it in tblDeductions. (So you would need to actively change the SQL that is used in the RS).
An example of the VB is below, but without a copy of the access DB (put dummy data in and remove logo's etc), I can't do it exactly.
Hope this Helps.
Open in new window