I have built my report that tracked entries from different "Home Stations" around the world. Those reports feed my PBI.
The reports log, Date of task, Labor type ( Direct, Indirect, and Training) along with the time spent on that task. " Duration".
The reports are by Site and not by employee, "Supporting FSR". Some employees are no longer on contract, but I still need to report on their past activities
Here are my tables :
- Appended queries of all 34 reports broken down, Each Query look exactly the same. See example table below.
| Date | Home Station | Supporting FSR | Labor Type | Contact Type | Category | Duration (Hr) |
| 1/1/23 | Okinawa | Joe Suffy | Direct | Support Requested | Engine | 3.0 |
| 1/2/23 | Camp Pendleton | Chris Carson | Indirect | Admin | Report | 1.5 |
| 1/3/23 | Wala Wala, WA | George Jetson | Training | Formal | FLMNET | 8.0 |
(Due to ITAR, CUI, and other regulations I cannot post a Snip of that table.)
- Service dates by employee “FSR POP”-
| Name | Location | Start Date | End Date |
| Joe Snuffy | Okinawa | 1 April 2022 | 1 OCT 2022 |
| Chris Carson | Camp Pendleton | 6 Jun 2022 | 28 Feb 2023 |
| George Jetson | Wala Wala, Washingtion | 1 April 2022 | Current |
- Tables for each region that sum the duration column and group by “Supporting FSR” and then “Labor Type” -column are named just like that as well Supporting FSR- Labor Type -Duratio
| Date | Supporting FSR | Home Station | Labor Type | Duration |
| 1/1/23 | Joe Snuffy | Okinawa | Direct | 5.3 |
| 1/1/23 | Joe Snuffy | Okinawa | Indirect | 1.0 |
| 1/1/23 | Joe Snuffy | Okinawa | Training | 2.7 |
| 1/1/23 | George Jetson | Wala Wala, WA | Direct | 6 |
| 1/1/23 | George Jetson | Wala Wala, WA | Indirect | 1 |
| 1/1/23 | Chris Carson | Camp Pendleton | Training | 7 |
| 1/1/23 | Chris Carson | Camp Pendleton | Direct | 1 |
(Due to ITARs I cannot post a Snip of that table.)
I need to use these tables to calculate the number of hours assigned M-F, 8 hours per day, between the start and end dates of each person, excluding Federal holidays that I have listed in another table.
Desired Result would look something like this.
| Date | Name | Home Station | Labor Type | Assigned Hours | Logged Hours | Utl Rate |
| 1/1/23 | Joe Snuffy | Okinawa | Direct | 8 | 5.3 | 66.2% |
| 1/1/23 | Joe Snuffy | Okinawa | Indirect | 8 | 1.0 | 12.5% |
| 1/1/23 | Joe Snuffy | Okinawa | Training | 8 | 2.7 | 33.7% |
| 1/1/23 | George Jetson | Wala Wala, WA | Direct | 8 | 6 | 75% |
| 1/1/23 | George Jetson | Wala Wala, WA | Indirect | 8 | 1 | 12.5% |
| 1/1/23 | George Jetson | Wala Wala, WA | Training | 8 | 0 | 0% |
| 1/1/23 | Chris Carson | Camp Pendleton | Direct | 8 | 1 | 12.5% |
| 1/1/23 | Chris Carson | Camp Pendleton | Indirect | 8 | 7 | 87.5% |
| 1/1/23 | Chris Carson | Camp Pendleton | Training | 8 | 1 | 12.5% |
I am asking a lot and giving a little. I know
