I have a database where the main data is timecard information and is inserted into a table every week. I have a second table that has project specific data, including project number (linking fields) and a checkbox for active or not.
Code:
tblProject (sample data)
recordID projectName projectNumber projectTask submissionType numbersheets targetAveDR targetAvgDS startweek endweek active
1 Project A 1234567890 000.111 Prelim 26 7.5 4.5 10/18/2013 12/1/2013 N
2 Project B 2345678901 000.111 Prelim 20 7.5 4.5 11/15/2013 1/3/2014 Y
3 Project A 1234567890 000.111 Final 26 3.5 2 1/3/2014 3/1/2014 Y
Code:
tblTimecard (sample data)
recordID Wkending role employee project task sat sun mon tue wed thu fri total
1 10/25/2013 DR EmployeeA 1234567890 - Project A 000.111 0 0 7.5 7.5 7.5 7.5 7.5 37.5
2 11/1/2013 DR EmployeeA 1234567890 - Project A 000.111 0 0 7.5 7.5 7.5 7.5 7.5 37.5
3 1/3/2014 DR EmployeeA 1234567980 - Project A 000.111 0 0 7.5 7.5 7.5 7.5 7.5 37.5
4 1/10/2014 DR EmployeeA 1234567980 - Project A 000.111 0 0 7.5 7.5 7.5 7.5 7.5 37.5
I have a field in my query that truncates the project down to just the number portion. The rest of the text is added into the timecard system and isn't required if I manually enter the project info. Because of how the accounting for a project works, there is only one number for the project that lasts its entire lifecycle, but there can be a couple of submissions. What I want to do is pull all of the timecard data for the second submission, without including any information from the first submission.
As an example, Project A was active for the prelim submission from 10/18/2013-12/1/2013. The query works fine to pull this info, as long as it's the only project. Once the second submission to Project A starts, say 1/3/2014-3/1/2014, the active flag is flipped off for the first submission. In my query, I only want to see timecard entries for part 2 of Project A on a date that's equal to the start date of the second submission. I also don't want to modify the timecard data, as that defeats the purpose of having a DB do the heavy lifting.
The reason for the separation is the targets for each submission vary, and I need to track the hours charged for each submission. I don't want to have a separate report for each type, but since I can't filter the dates properly, that doesn't matter. I have even created a calculated field to calc the number of days since 1/1/1900 and filter rows >= to this number. That didn't work either.
Any help would be greatly appreciated.
I already shave my head, so I can't pull out any hair. This is probably leading to higher than normal frustration levels.
Jason