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------------------------------

markshnier__you · 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
------------------------------

williamcundiff · Answer

Thanks, Mark! I did everything as instructed for the first table. When i tried to add summary fields to the first relationship i received the following:

We are sorry -- at this time, you cannot create a summary field using a relationship where the reference field is this type of formula field. Please visit our limits page (https://lamresearch.quickbase.com/db/main?act=dohelp&page=quickbase_limits) or contact Care for more information.

Any idea how to get around this?

Best,
William

------------------------------
William C
------------------------------

markshnier__you · Answer

Can you tell me more about the date field you are using on the child side? is this a data entry field type? Or a formula field based on Date Created.

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

williamcundiff · Answer

Yes, there is a "Start Date" that is a data entry field------------------------------William C------------------------------

markshnier__you · Answer

I was a&nbsp; bit afraid that would happen and so I did my own test first and it did work.&nbsp; I'm curious about this issue though and would not mind having a look at your app myself.&nbsp; Contact me directly by my email address below if you would like me to have a quick look.------------------------------Mark Shnier (YQC)Quick Base Solution ProviderYour Quick Base Coachhttp://QuickBaseCoach.commark.shnier@gmail.com------------------------------

Forum Discussion

Combine Summary Reports

Related Content

Summary Reports

Sorting Combined Text Field by Record ID#

Filtering a report for Combined Text Summary Field

Combining AND with OR in Formula Checkbox Field

Maximum Summary field not working