Identifying/Calculating Fiscal Year(s) based on Grant beginning and End Dates
Hi - I'm trying to track grants and FTE expenditures across and within fiscal years (10/01 -9/30). Some grants are multiple years, some are just months, most don't start and end on the FY dates. I want to be able to do reports by FY that will come up with all the grants that were active during that FY (even if they go on for multiple FYs), and calculate a total FTE by grant and then overall. So far, I've got three tables: Grants (which includes start and end date fields), Employees, and Grants_Employees which includes an FTE field (as a person may be on multiple grants).
The difficulty I have is that I don't know how best to identify Fiscal Year(s) for the grants so I can be able to create a report. Is that a repeating field within the Grants table? Is it a separate table? Any suggestions would be greatly appreciated.