Database Design to track research grants coming into the College
Hi, I'm a student working at my summer job which is to design and put together a database that can track research grants received by all the faculty members at my college. I'm a beginner at using MS Access.
I need help with setting up a query that can generate data based on fiscal year as well as calendar year. However, the grants are covering a period of time (1, 2 years) not a one time transaction. When I used the Date/time function in Access I can't get the result I'm looking to generate. For instance, the fiscal year is from 7/1/2013 to 6/30/2014, but if I have a grant that started in March of 2013 and does not end until Nov of next year, how would I capture that value?
What is the best way to define the range of dates? Right now for the fields of the Grants table, I have included the "start date" and "end date", but maybe I need more than just the start and end date to generate the queries I want?