I am trying to set up a query to build a report off of that has multiple data sources. I am using two tables in my database, (PROJECTS and ALLOCATIONS) and one linked spreadsheet called TIMESHEET kept in Sharepoint that will be replaced each week with data from the previous week.
The query contains the Following Fields:
- Project_Title from PROJECTS
- Contract_Hours from PROJECTS
- Allocated_Hours from Allocation (joined with PROJECTS by Project_ID)
- Worked_Hours from TIMESHEET (joined with PROJECTS by Project_ID)
Both the Allocated_Hours and the Worked_Hours fields need to be sums grouped by Project_Title.
In order to generate this report the user is going to have to go online and download and replace the TIMESHEET file which will automatically update the linked table in my database. When the user does this they will have to input a date criteria into the website that generates the timesheet in order to filter out results for previous weeks and get the total for only the week we want to look at. The issue is that after this is all done and the user wants to run the report, they have to reenter the date criteria into the database in order to filter the Allocated_Hours field. My goal is to eliminate this step. I want to do this by checking the domain of the date field of the TIMESHEET and setting it as the criteria for Allocated_Hours, so that the data is always for the same period of time. If I can figure out how to check the domain of an entire field of a table I believe it will be simple enough to use it as a criteria for my query, however I don't know how to go about this, nor has any of my research provided me with an answer so far.
So I suppose the first step in accomplishing my goal, and my primary question is: How can I check the Domain of a date field in a linked table in order to apply it as a criteria for another field? But moreover I would just like to make sure what I am attempting is actually possible.