I am trying to create a tool that compares schedules. There are two groups of schedules that I am comparing a ‘created’ group and an ‘implemented’ group. I want to know how many schedules in the created group were used in the implemented group. Additionally, I would like to know how many schedules were implemented that were not in the created group. I need to use a one to one relationship. It sounds pretty easy, but I sure am having a tough time.
The source data has the following fields:
Staff_group (the same name for both groups),
Sched_id (the id assigned to each schedule… each group starts with sched_id with 1, so there will be a 1, 2, 3 etc in both groups)
DOW (day of week, text, mon, tues, wed),
Start (start time of the schedule, (10/13/2008 6:00:00 am),
Stop (start time of the schedule, (10/13/2008 6:00:00 pm)
A note on start/stop time. The actual month, day, year will be different between the two groups of schedules for start and stop, but that is ok since I am only concerned about the day of week and hour/minute for the start/stop time.
The source data has 1 row for each day for each schedule. In order to consolidate multiple rows into one row for each schedule, I made a couple of alterations. First, I created a column for each day of the week and used the following formula (Mon: IIf([DOW]='mon',1,0)). I also changed the stop and start fields format to (Format(stop,"h:m")).
After making these alterations, I created a table by grouping by staff_group, sched_id, start, stop while summing each day of week column (mon, tues, wed, etc). This gives me one row for each schedule. I did this for implemented and created schedules.
I am trying to join the tables, but I am getting lots of duplicates. I tried doing a left join to capture all created schedules and only implemented schedules that matched. I also tried doing a right join to capture all of the implemented schedules and only created schedules that matched. There are 2-3 schedules that are identical that I think are creating the issues for me.
Sorry for the long post, but any guidance would be greatly appreciated.