Forum Discussion
Are you doing this to forecast out a specific time frame such as the next 6 months to 1 year?
The long and the short answer if that a simple summary report isn't going to be sufficient. You're reporting on the combination of employees and time which can't be represented by single field(s) such as active / start / term. You have a couple different options of how you could go about it - but my overall recommendation would be to create a 'Months' table and use formula queries to count the number of employees and then report from that table.
Your months table would represent each Month as a record - so one record is January 2023. The only field you need is a 'date' to represent the month.
Your formula query then would query for employees where:
Start Date is On or Before the End of the Month AND
Term Date is After the End of the Month.
The start date param ensures that you can count employees that start mid-month and the term makes sure that the employee is still counted in the month they leave but then not after.
------------------------------
Chayce Duncan
------------------------------
- BradJohnson12 years agoQrew Trainee
Thanks for the feedback. I have created the months table and established a relationship between the months table and the employees table which contains the start and termed dates. How would I go about creating the formula queries you suggest?
Brad Johnson Regional Technology Coordinator Pike Engineering, LLC 850 Trafalgar Court, Suite 300 Maitland , FL 32751 wbjohnson@pike.com www.pike.com The information contained in this electronic message is information intended for the use of only the individual or entity named above and may be PRIVILEGED and CONFIDENTIAL. If the reader of this message is not the intended recipient or the employee or agent responsible for delivering it to the recipient, you are hereby notified that any review, disclosure, dissemination, distribution, or copying of this communication is strictly prohibited. If you received this electronic message in error, please notify the sender immediately by replying to this e-mail and permanently delete the original message. Thank you - ChayceDuncan2 years agoQrew Captain
I can only go so far without knowing the exact field ID #s but you'll need:
DBID of the employees table
Field ID of their start date = fid_start (below)
Field ID of their term date = fid_term (below)
Then your query would be something like:
Size(GetRecords("{'fid_start'.OBF.'" & [Field for Last Day Of Month Record] & "'}AND{'fid_term'.AF.'" & [Field for Last Day of Month Record] & "'}", Dbid of Employees))
You'd be swapping out your field IDs and value for the last day of the month. If you dont' already - you'll want a field in your months table that does LastDayOfMonth([Date Value In that Month]) so you can count employees that started anytime during the month.
------------------------------
Chayce Duncan
------------------------------- BradJohnson12 years agoQrew Trainee
Thanks this is very helpful. Would I be placing this formula in a new formula-numeric field in the Months table?
Brad Johnson Regional Technology Coordinator Pike Engineering, LLC 850 Trafalgar Court, Suite 300 Maitland , FL 32751 wbjohnson@pike.com www.pike.com The information contained in this electronic message is information intended for the use of only the individual or entity named above and may be PRIVILEGED and CONFIDENTIAL. If the reader of this message is not the intended recipient or the employee or agent responsible for delivering it to the recipient, you are hereby notified that any review, disclosure, dissemination, distribution, or copying of this communication is strictly prohibited. If you received this electronic message in error, please notify the sender immediately by replying to this e-mail and permanently delete the original message. Thank you