HR Stats (1 Viewer)

[sparklegrrl]

Good morning. I'm trying to create some employee statistical reports and for some reason I can't wrap my head around how to make it happen. For example: I need the average monthly employees for any given month. My CFO does wants the number each day and then the average so he can have the actual average. I need to be able to run it by month as well as by period and have each month average.

I have a single table with all the data. Employee name, hire and term (if applicable).

Any help is greatly appreciated.

 

[theDBguy]

Hi. We would love to help, but we'll probably need more information. What are the fields in your table? What are you trying to average?

 

[plog]

Please post two sets of data to demonstrate what you have and what you want:

A. Starting data from your table. Include table and field names and enough sample data to cover all cases.

B. Expected results. Show us what you expect the report tomlook like when you feed it the data in A.

Again, 2 sets of data.

 

[sparklegrrl]

Here you go:

Set 1: First Name, Middle Name, Last Name, Employee Number, Hire Date, Term Date

Set 2: Avg Employees by Month but he wants it calculated daily. I'm honestly not sure what he's thinking. I did it really quickly based on beginning number and ending number then averaged the 2. He said that was not as accurate. He wants the number daily then monthly but the just the finally monthly number displayed.

For example:

2020

Jan 300
Feb 325
Mar 297

Total 307

Does that make sense?

 

[theDBguy]

Here you go:

Set 1: First Name, Middle Name, Last Name, Employee Number, Hire Date, Term Date

Set 2: Avg Employees by Month but he wants it calculated daily. I'm honestly not sure what he's thinking. I did it really quickly based on beginning number and ending number then averaged the 2. He said that was not as accurate. He wants the number daily then monthly but the just the finally monthly number displayed.

For example:

2020

Jan 300
Feb 325
Mar 297

Total 307

Does that make sense?

Hi. Thanks for the additional information. The way I understand it, you want to list the number of "active" employees by month. Is that correct? If so, that should be possible.

 

[plog]

If so, that should be possible.

That's your help? Affirmation of its possibility?

This query is going to be painful actually. You want to reporting increments that don't naturally exist in your table (Monthly). Because people have active ranges (HireDate, TermDate) that could be employed for a month that isn't explicitly listed anywhere in their range. So, you are going to need a table to define the increments you want to report on. That means a ReportDates table like so:

tblReportDates
rd_StartDate, rd_EndDate
1/1/2020, 1/31/2020
2/1/2020, 2/29/2020
3/1/2020, 3/31/2020
...


If you truly want daily, then you need a table of unique dates--that means 366 records for just 2020. So, my example is going to show you how to do the monthly report.

First, create that table I listed above. Then you will build your query by bringing in tblReportDates and your existing table but not linking them in any way. In the bottom section bring down the EmployeeNumber to show in the query. And then add these fields:

ReportMonth: Month(rd_StartDate)
ReportYear: Year(rd_StartDate)

Then you add one more calculated field. This one will determine if the HireDate to TermDate range intersects with the rd_StartDate and rd_EndDate range.

Employeed: Iif(rd_EndDate>=HireDate AND (rd_StartDate<=TermDate OR TermDate IS NULL), True, False)

Lastly, you put TRUE under the Employeed field to show just those that meet the criteria. That will tel you if ea

 

[theDBguy]

That's your help? Affirmation of its possibility?

Sorry, I guess you're better than me. I'll let you take over then. Good luck!

 

[sparklegrrl]

Thank you both very much! That gives me a starting point to play with.

 

[sparklegrrl]

Ok that's not working for me. Is there some way to write an iif statement on a report and maybe put 1 for each month by employee then total? For example: If the HireDate is <2/1/20 and the TermDate is blank or between 1/1/20 and 1/31/20 then enter a 1, otherwise a 0? Where it would look like this:
Hire Term JAN FEB MAR APR MAY
John Doe 1/1/16 3/5/20 1 1 1 0 0
Jane Doe 2/15/20 0 1 1 1 1
Little Doe 1/20/20 4/16/20 1 1 1 1 0

Then I can calculate totals and averages?

 

[plog]

Yes you could do that.

 

[theDBguy]

Yes you could do that.

Couldn't have said it better myself. Very affirmative!

 

[plog]

Wow, I really got to you. Now you're passively aggressively affirming others affirmations.

I know it goes against this internet culture we've built but allow me to be direct: she initially requested instructions about what she wanted to accomplish. You provided no help just said 'that should be possible'. Now she just laid out a possible method and requested affirmation about it. I provided said affirmation. And now I am passively aggressively apologizing for hurting your feelings in the process of assisting her.

 

[theDBguy]

Wow, I really got to you. Now you're passively aggressively affirming others affirmations.

I know it goes against this internet culture we've built but allow me to be direct: she initially requested instructions about what she wanted to accomplish. You provided no help just said 'that should be possible'. Now she just laid out a possible method and requested affirmation about it. I provided said affirmation. And now I am passively aggressively apologizing for hurting your feelings in the process of assisting her.

Got it. But you didn't have to make other people look bad when you're trying to help someone, right?