Combine Summary Reports

Question

I have two tables: Employee Leave and Active Directory. Employee Leave records have a Shift Type and Date. Active Directory records also have a Shift Type. The Active Directory can have many Employee Leave records.I want to summarize the number of Missed Shifts and Available Shifts in one summary report, grouped by week and Shift Type. I also need to add calculated columns like Total Missed %, and Missed % by Shift Type.This doesn't seem difficult in theory but I can't seem to combine the two. Attached is an excel version of what I'm trying to create.
Thanks in advance!William------------------------------William C------------------------------

MarkShnierYou · Answer

You will need to have a table of Weeks where the key field is the date that represents the Sunday of the week. Use Excel to load up all the Sundays for say the next 10 years. Set the Key field to be the date field.

Then make a relationship where One Sunday has many Employee Leaves. Let it create a field for Related Week, but then edit that field to make it a formula field

FirstDateOfWeek([my date field])

Then make summary fields on the Relationship to summarize up to Weeks the various fields you need to sum from Employe Leaves.

Repeat the process from the other table, Active Directory. Then you will be able to do your calculations on the weeks table.

------------------------------
Mark Shnier (YQC)
Quick Base Solution Provider
Your Quick Base Coach
http://QuickBaseCoach.com
mark.shnier@gmail.com
------------------------------

Sign up

Login to the community