Waterfall Report, how to
JohnG
How do I do a report that will count the number of employees present in each
month?
Now for even more challenging, I would like to count to be a weighted count,
so if an employee is present for half of the month, he/she is counted has 0.5
Any help would be appreciated, John.
Mike Painter
I assume you have a table that records their presence.
Build a query for the month. No records will show if the person was not
there.
Build a report grouping on the employee.
Count the number of records for each employee and compare it to the number
for half of a month.
IIF( Count(SomeFieldValue) > HalfThe Month, 1, 0.5)
Determining days in the month and handling part time, vacation time and sick
time is where the fun is.
JohnG
empl Started Employment Ended Employement
joe Jan 1, 2005
Sam Jan 20, 2005
Betty Feb 20, 2005 June 1, 2005
The report I would like looks like
Report 1: Simple count on 1st of each month
Jan Feb March April May June July Aug Sept Oct Nov Dev
1 2 3 3 3 3 2 2 2 2 2 2
Report 2: Avg Heads in Month
Jan Feb March April May June July Aug Sept Oct Nov Dev
1.2 2.2 3 3 3 3 2 2 2 2 2 2
Mike Painter
> The tables i have is as follows
> empl Started Employment Ended Employement
> joe Jan 1, 2005
> Sam Jan 20, 2005
> Betty Feb 20, 2005 June 1, 2005
>
> The report I would like looks like
>
> Report 1: Simple count on 1st of each month
> Jan Feb March April May June July Aug Sept Oct Nov Dev
> 1 2 3 3 3 3 2 2 2 2 2
> 2
>
> Report 2: Avg Heads in Month
> Jan Feb March April May June July Aug Sept Oct Nov Dev
> 1.2 2.2 3 3 3 3 2 2 2 2
> 2 2
>
>
working at the end of februrary.
If the first of the month counts towards employment then Betty should be
shown in the June report.
You asked for .5 if the person is there half the month or less. Joe is there
a full month and Sam less than half so you would show (1 + .5)/2 or .75 for
the average in Jan.
These number are not consistant with what you ask for in the original post.
Access does not like going across a page with this type of information.
Jan 1
Feb 2
Mar 3
is trivial and a simple report design. Your way will either take a cross
tab query or considerable coding.
James A. Fortune
> The tables i have is as follows
> empl Started Employment Ended Employement
> joe Jan 1, 2005
> Sam Jan 20, 2005
> Betty Feb 20, 2005 June 1, 2005
>
> The report I would like looks like
>
> Report 1: Simple count on 1st of each month
> Jan Feb March April May June July Aug Sept Oct Nov Dev
> 1 2 3 3 3 3 2 2 2 2 2 2
>
> Report 2: Avg Heads in Month
> Jan Feb March April May June July Aug Sept Oct Nov Dev
> 1.2 2.2 3 3 3 3 2 2 2 2 2 2
>
I used the following functions:
Function Round(varIn As Variant, intPlaces As Integer) As Variant
Round = Int(10 ^ intPlaces * varIn + 0.5) / 10 ^ intPlaces
End Function
Public Function ProRateMonth(dtMStart As Date, dtEStart As Date, varEEnd
As Variant) As Double
Dim dblDaysInMonth As Double
Dim dtMEnd As Date
Dim dtEEnd As Date
'Return the number of days in a month overlapping a date range divided
by the number of days in the month
'Assumes dtEStart <= varEEnd and dtMStart is the date of the first day
of the month
ProRateMonth = 0
dtMEnd = DateSerial(Year(dtMStart), Month(dtMStart) + 1, 0)
dblDaysInMonth = DateDiff("d", dtMStart, dtMEnd) + 1
If IsNull(varEEnd) Then
If dtEStart > dtMEnd Then Exit Function
If dtEStart < dtMStart Then
ProRateMonth = 1
Else
ProRateMonth = (DateDiff("d", dtEStart, dtMEnd) + 1) / dblDaysInMonth
End If
Exit Function
End If
If dtMEnd <= dtEStart Then
If dtMEnd = dtEStart Then ProRateMonth = 1# / dblDaysInMonth
Exit Function
End If
If varEEnd <= dtMStart Then
If varEEnd = dtMStart Then ProRateMonth = 1# / dblDaysInMonth
Exit Function
End If
If dtMStart <= dtEStart And dtEStart <= dtMEnd And dtMEnd <= varEEnd Then
ProRateMonth = (DateDiff("d", dtEStart, dtMEnd) + 1) / dblDaysInMonth
Exit Function
End If
If dtMStart <= dtEStart And dtEStart <= varEEnd And dtEEnd <= dtMEnd Then
ProRateMonth = (DateDiff("d", dtEStart, varEEnd) + 1) / dblDaysInMonth
Exit Function
End If
If dtEStart <= dtMStart And dtMStart <= dtMEnd And dtMEnd <= varEEnd Then
ProRateMonth = (DateDiff("d", dtMStart, dtMEnd) + 1) / dblDaysInMonth
Exit Function
End If
If dtEStart <= dtMStart And dtMStart <= varEEnd And varEEnd <= dtMEnd Then
ProRateMonth = (DateDiff("d", dtMStart, varEEnd) + 1) / dblDaysInMonth
End If
End Function
SELECT Sum(Abs(ProRateMonth(#Feb 1, 2005#,[Started Employment],[Ended
Employment])>0.99)) AS Report1Days FROM tblEmpl;
produced:
Report1Days
2
It counts the employee if the employee worked all the days of the month.
I'm not sure that's what you want since you have [Report 1].June = 3.
To have an employee count if they work any days in the month, replace
0.99 with 0.
SELECT Round(Sum(ProRateMonth(#Feb 1, 2005#, [Started Employment],
[Ended Employment])), 1) AS Report2Days FROM tblEmpl;
produced:
Report2Days
2.3
A crosstab query is probably required to get the months going across.
I'll see what I can come up with. Are the reports always for Jan to
Dec? My field format for [Started Employment] was mmm d", "yyyy. Also,
be sure to test the function ProRateMonth before use.
James A. Fortune
James A. Fortune
I found the following useful:
tblDisplayMonth
DisplayMonth MonthNumber
Jan 05 1
Feb 05 2
Mar 05 3
Apr 05 4
May 05 5
Jun 05 6
Jul 05 7
Aug 05 8
Sep 05 9
Oct 05 10
Nov 05 11
Dec 05 12
Note: DisplayMonth has a field type Date/Time with format mmm yy.
qryDisplayMonths:
SELECT tblDisplayMonth.DisplayMonth,
Round(Sum(ProRateMonth([DisplayMonth],[Started Employment],[Ended
Employment])),1) AS EmployeeProRateSum FROM tblDisplayMonth, tblEmpl
GROUP BY tblDisplayMonth.DisplayMonth;
!qryDisplayMonths:
DisplayMonth EmployeeProRateSum
Jan 05 1.4
Feb 05 2.4
Mar 05 3
Apr 05 3
May 05 3
Jun 05 2
Jul 05 2
Aug 05 2
Sep 05 2
Oct 05 2
Nov 05 2
Dec 05 2
Also of use is:
qryDisplayMonths_Crosstab:
TRANSFORM Round(Sum(qryDisplayMonths.EmployeeProRate),1) AS [The Value]
SELECT qryDisplayMonths.MonthNumber FROM qryDisplayMonths GROUP BY
qryDisplayMonths.MonthNumber ORDER BY qryDisplayMonths.MonthNumber,
Format([qryDisplayMonths].[DisplayMonth],'mmm yy') PIVOT
Format([qryDisplayMonths].[DisplayMonth],'mmm yy');
! qryDisplayMonths_Crosstab:
MonthNumber Apr 05 Aug 05 Dec 05 Feb 05 Jan 05 Jul 05 Jun 05 Mar 05 May
05 Nov 05 Oct 05 Sep 05
1 1.4
2 2.4
etc.
12 2
A separate totals query could give you your report fields:
qryTotalsForReport:
SELECT Sum(CDbl(Nz([Jan 05]))) AS [Jan05], Sum(CDbl(Nz([Feb 05]))) AS
[Feb05], Sum(CDbl(Nz([Mar 05]))) AS [Mar05], Sum(CDbl(Nz([Apr 05]))) AS
[Apr05], Sum(CDbl(Nz([May 05]))) AS [May05], Sum(CDbl(Nz([Jun 05]))) AS
[Jun05], Sum(CDbl(Nz([Jul 05]))) AS [Jul05], Sum(CDbl(Nz([Aug 05]))) AS
[Aug05], Sum(CDbl(Nz([Sep 05]))) AS [Sep05], Sum(CDbl(Nz([Oct 05]))) AS
[Oct05], Sum(CDbl(Nz([Nov 05]))) AS [Nov05], Sum(CDbl(Nz([Dec 05]))) AS
[Dec05] FROM qryDisplayMonths_Crosstab;
! qryTotalsForReport:
Jan05 Feb05 Mar05 Apr05 May05 Jun05 Jul05 Aug05 Sep05 Oct05 Nov05 Dec05
1.4 2.4 3 3 3 2 2 2 2 2 2 2
It really needs to be more automatic. Note especially that
qryTotalsForReport has the field names hard coded. In the past I have
used a table with one record to place the start month, then used the
month numbers in another table via the DateAdd function to get the
months I wanted. Hopefully these queries will give you a start in the
right direction.
James A. Fortune
James A. Fortune
> JohnG wrote:
>
>>The tables i have is as follows
>>empl Started Employment Ended Employement
>>joe Jan 1, 2005
>>Sam Jan 20, 2005
>>Betty Feb 20, 2005 June 1, 2005
>>
>>The report I would like looks like
>>
>>Report 1: Simple count on 1st of each month
>> Jan Feb March April May June July Aug Sept Oct Nov Dev
>> 1 2 3 3 3 3 2 2 2 2 2
>>2
>>
>>Report 2: Avg Heads in Month
>> Jan Feb March April May June July Aug Sept Oct Nov Dev
>> 1.2 2.2 3 3 3 3 2 2 2 2
>>2 2
>>
>>
>
> You show 1 employee for Jan and yet two started in Jan. and you had three
> working at the end of februrary.
> If the first of the month counts towards employment then Betty should be
> shown in the June report.
> You asked for .5 if the person is there half the month or less. Joe is there
> a full month and Sam less than half so you would show (1 + .5)/2 or .75 for
> the average in Jan.
>
> These number are not consistant with what you ask for in the original post.
His phrase 'Simple count on 1st of each month' is consistent with the
numbers he gave (i.e., June 1 counts for all of June, January 20 doesn't
count for January), but I'm still not sure that's what he really wants.
If it is I'll modify the SQL for Report 1 that I gave to handle it
like that.
James A. Fortune
Mike Painter
If employed on the first of the month counts as being employed then if the
last day employed falls on the first of the month it can't not count as it
does in his sample.
He only shows a table with start and stop dates.
He must have a table that records actual days worked to be compared against
workdays in a month less hoilidays etc.
In any event the 1.2 shown in January as an average can't be right if there
was only one employee as you claim or two as he shows.
His request for 1 if the employee works all days and .5 if he does not
gives an average of .75 for the two employees.
jimfo...@compumarc.com
> >James A. Fortune wrote:
> > His phrase 'Simple count on 1st of each month' is consistent with the
> > numbers he gave (i.e., June 1 counts for all of June, January 20
> > doesn't count for January), but I'm still not sure that's what he
> > really wants. If it is I'll modify the SQL for Report 1 that I gave
> > to handle it like that.
>
> If employed on the first of the month counts as being employed then if the
> last day employed falls on the first of the month it can't not count as it
> does in his sample.
> He only shows a table with start and stop dates.
> He must have a table that records actual days worked to be compared against
> workdays in a month less hoilidays etc.
> In any event the 1.2 shown in January as an average can't be right if there
> was only one employee as you claim or two as he shows.
> His request for 1 if the employee works all days and .5 if he does not
> gives an average of .75 for the two employees.
The 'If employed on the first of the month' simple count is only used
for Report1.
He may not have a table with actual days worked. Besides, if he does
I've already worked out the Workday/Holiday bit.
The 1.2 being different than the 1.4 I computed makes me suspect
something like workdays, but I couldn't get 1.2 using workdays either.
I don't think the .5 was intended to represent 'doesn't work all days'
given his followup explanation.
James A. Fortune
JohnG
Scott Lengyel
I have a similar situation. My table looks like this.
Date Emp CC Start End
8/1 m08 74843 3:15 PM 11:45PM
8/1 m01 74937 3:00 PM 11:30PM
8/1 m15 74843 3:00 PM 11:30PM
8/1 m12 74843 5:00 PM 11:00PM
8/1 m02 9420 4:30 PM 12:00AM
I need report that will indicate how many employees worked and the total
hours they worked for each hour of the day. For instance, at 4:00 PM to 5:00
PM 4 employees worked and the total hours they worked was 3.5. The report
would list these totals for every hour of the day. I would like the report
to be based on a date the user enters. I also want the ability to break out
the report by CC (Cost Center). I currently have this table in excel and use
sumproduct to get these totals. It is now starting to take a long time to
get these totals because there is 3 months of data for 80 employees. I
originally used Excel instead Access because I am not too familiar with VBA.
I have created other databases used by my department that have some code but
nothing this complex. I'm just looking for sources to get me started. Any
ideas?
James A. Fortune
This problem looks quite a bit different than that one. I'm still not
very experienced at using crosstab queries, but I'll take a look at your
problem tonight. Maybe I should also write a version of ProRateMonth
based on overlapping business days divided by the total number of
business days.
James A. Fortune
MPAP...@FortuneJames.com
James A. Fortune
>
> This problem looks quite a bit different than that one. I'm still not
> very experienced at using crosstab queries, but I'll take a look at your
> problem tonight. Maybe I should also write a version of ProRateMonth
> based on overlapping business days divided by the total number of
> business days.
>
> James A. Fortune
> MPAP...@FortuneJames.com
Here are my initial impressions:
I don't think you need to split a shift into pre- and post- midnight
portions if you use a date/time value for Start and End that includes
the date and time together. A TimeIntersection function that returns
the overlap (in hours?) between, say, 8/1/06 4:00 PM to 8/1/06 5:00 PM
and 8/1/06 3:15 PM to 8/1/06 11:45 PM can be used as part of a sum to
get the total hours worked during that time period by all employees. A
sum of the count of TimeIntersection values that are > 0 where each
employee is counted at most once can give the number of employees that
worked during that hour.
The following thread has some similar ideas but you still need to watch
out for the same employee being counted twice:
I think a TimeIntersection function will be a little cleaner than other
options and it will help keep the crosstab query from getting too
complex. I'll try to roll one out soon. Am I on the right track?
James A. Fortune
MPAP...@FortuneJames.com
Access Tip:
Every now and then, set a breakpoint and step through code that has no
errors. Set additional breakpoints to get past loops or to stop at the
end of each interation. You may see new ways to optimize the code or
discover places that need optimization the most.
Scott Lengyel
exactly what I want. It's reply 6 by HSalim. He wrote "What is the
coverage by hour? The answer should be a list time slots with the number of
employees worked." That's exactly what I need. The time slots should be
each hour of the day. If possible, I would like the total actual hours
worked in each time slot.
James A. Fortune
How about having the crosstab list all 24 hours across using IN? The
situation I warned against can happen if an employee stops a shift and
starts another within the current hour. Except for that situation the
TimeIntersection function seems to be the piece that makes everything
possible. I think the TimeIntersection function will be similar to my
DateIntersection function. I think we're making progress.
James A. Fortune
MPAP...@FortuneJames.com
James A. Fortune
>
> How about having the crosstab list all 24 hours across using IN? The
> situation I warned against can happen if an employee stops a shift and
> starts another within the current hour. Except for that situation the
> TimeIntersection function seems to be the piece that makes everything
> possible. I think the TimeIntersection function will be similar to my
> DateIntersection function. I think we're making progress.
>
> James A. Fortune
> MPAP...@FortuneJames.com
Here's what I came up with for a TimeIntersection function:
'--------Begin module code----------------
Public Function TimeIntersection(dt1 As Date, dt2 As Date, dt3 As Date,
dt4 As Date) As Double
'Return the number of hours overlapping two time intervals
'Assumes d1 <= d2 and d3 <= d4 Intervals can go in either order
'Use the convention that all endpoints are at the left-hand side of the
second interval.
'E.g., dt2 = dt3 => TimeIntersection = 0 rather than 1# / 3600 hour.
TimeIntersection = 0
If dt1 <= dt3 And dt3 <= dt2 And dt2 <= dt4 Then
TimeIntersection = DateDiff("s", dt3, dt2) / 3600#
Exit Function
End If
If dt1 <= dt3 And dt3 <= dt4 And dt4 <= dt2 Then
TimeIntersection = DateDiff("s", dt3, dt4) / 3600#
Exit Function
End If
If dt3 <= dt1 And dt1 <= dt2 And dt2 <= dt4 Then
TimeIntersection = DateDiff("s", dt1, dt2) / 3600#
Exit Function
End If
If dt3 <= dt1 And dt1 <= dt4 And dt4 <= dt2 Then
TimeIntersection = DateDiff("s", dt1, dt4) / 3600#
End If
End Function
'----------End module code----------------
Example:
MsgBox ("TimeIntersection = " & TimeIntersection(#8/18/2006 4:00:00 PM#,
#8/18/2006 5:00:00 PM#, #8/18/2006 4:15:01 PM#, #8/18/2006 10:30:00 PM#))
=> TimeIntersection = 0.749722222222222
I didn't recheck the logic for the = parts in the If statements but it
shouldn't hurt to have them.
James A. Fortune
MPAP...@FortuneJames.com
James A. Fortune
I'd like to follow the previous solution as closely as possible. I'm
thinking about:
tblDisplayHour
DisplayHour Date/Time h:nn ampm
HourNumber Integer
DisplayHour HourNumber
12:00 AM 1
1:00 AM 2
2:00 AM 3
3:00 AM 4
4:00 AM 5
5:00 AM 6
6:00 AM 7
7:00 AM 8
8:00 AM 9
9:00 AM 10
10:00 AM 11
11:00 AM 12
12:00 PM 13
1:00 PM 14
2:00 PM 15
3:00 PM 16
4:00 PM 17
5:00 PM 18
6:00 PM 19
7:00 PM 20
8:00 PM 21
9:00 PM 22
10:00 PM 23
11:00 PM 24
along with changing the TimeIntersection function to have three inputs
instead of four. I have to ensure that the chosen date will integrate
properly. Also note that the 12 AM in your example implies that its
corresponding date is the following day. Once I convince myself that a
qryDisplayHour will use the input date correctly, the basic part of your
problem should fall out. I haven't looked at grouping by CC yet. Let
me know when you reach the point where you think you've had enough
"spoilers" to solve the rest yourself :-). BTW, what is the name of the
table containing Start and End times? Also, StartTime and EndTime might
be more robust as field names.
James A. Fortune
MPAP...@FortuneJames.com
James A. Fortune
> along with changing the TimeIntersection function to have three inputs
> instead of four. I have to ensure that the chosen date will integrate
> properly. Also note that the 12 AM in your example implies that its
> corresponding date is the following day. Once I convince myself that a
> qryDisplayHour will use the input date correctly, the basic part of your
> problem should fall out. I haven't looked at grouping by CC yet. Let
> me know when you reach the point where you think you've had enough
> "spoilers" to solve the rest yourself :-). BTW, what is the name of the
> table containing Start and End times? Also, StartTime and EndTime might
> be more robust as field names.
>
> James A. Fortune
> MPAP...@FortuneJames.com
Here's the next step I took:
'-------Start Module Code------
Public Function AddDates(dt1 As Date, dt2 As Date) As Date
Dim dtDayPart As Date
Dim intSeconds As Integer
dtDayPart = DateAdd("s", -DatePart("s", dt1), dt1) + DateAdd("s",
-DatePart("s", dt2), dt2)
intSeconds = DatePart("s", dt1) + DatePart("s", dt2)
AddDates = DateAdd("s", intSeconds, dtDayPart)
End Function
Public Function TimeIntersection(dtSelected As Date, dtHourStart As
Date, dt3 As Date, dt4 As Date) As Double
Dim dt2 As Date
'Return the number of hours overlapping two time intervals
'Assumes d3 <= d4
'Use the convention that all endpoints are at the left-hand side of the
second interval.
'E.g., dt2 = dt3 => TimeIntersection = 0 rather than 1# / 3600 hour.
TimeIntersection = 0
'Calculate date for dtHourStart
'First, get rid of any existing date, if present
dtHourStart = TimeSerial(Format(dtHourStart, "h"), Format(dtHourStart,
"n"), Format(dtHourStart, "s"))
dtHourStart = AddDates(dtSelected, dtHourStart)
'For automatic shift of midnight to the next day use Select Case on
Format(dtHourStart, "h:nn ampm")
dt2 = DateAdd("h", 1, dtHourStart)
If dtHourStart <= dt3 And dt3 <= dt2 And dt2 <= dt4 Then
TimeIntersection = DateDiff("s", dt3, dt2) / 3600#
Exit Function
End If
If dtHourStart <= dt3 And dt3 <= dt4 And dt4 <= dt2 Then
TimeIntersection = DateDiff("s", dt3, dt4) / 3600#
Exit Function
End If
If dt3 <= dtHourStart And dtHourStart <= dt2 And dt2 <= dt4 Then
TimeIntersection = DateDiff("s", dtHourStart, dt2) / 3600#
Exit Function
End If
If dt3 <= dtHourStart And dtHourStart <= dt4 And dt4 <= dt2 Then
TimeIntersection = DateDiff("s", dtHourStart, dt4) / 3600#
End If
End Function
'-------End Module Code---------
'---------Test Code---------
Dim dtShiftStart As Date
Dim dtShiftEnd As Date
Dim dtSelected As Date
Dim dtHourStart As Date
dtSelected = Date
dtHourStart = #3:00:00 PM#
dtShiftStart = #8/24/2006 3:15:00 PM#
dtShiftEnd = #8/24/2006 4:00:00 PM#
MsgBox (TimeIntersection(dtSelected, dtHourStart, dtShiftStart, dtShiftEnd))
'-------End Test Code-------
=> 0.75
This was run on 8/24/06. Watch out for the line wrap.
James A. Fortune
MPAP...@FortuneJames.com
James A. Fortune
Perhaps this is of interest:
tblEmpl
ID Emp CC Start End
1 m08 74843 8/1/2006 3:15:00 PM 8/1/2006 11:45:00 PM
2 m01 74937 8/1/2006 3:00:00 PM 8/1/2006 11:30:00 PM
3 m15 74843 8/1/2006 3:00:00 PM 8/1/2006 11:30:00 PM
4 m12 74843 8/1/2006 5:00:00 PM 8/1/2006 11:00:00 PM
5 m02 9430 8/1/2006 4:30:00 PM 8/2/2006
tblDisplayHour as previously shown
qryDisplayHours:
SELECT CDate("8/1/06") AS dtSelected, tblDisplayHour.DisplayHour,
Round(Sum(TimeIntersection([dtSelected],tblDisplayHour.DisplayHour,tblEmpl.Start,tblEmpl.End)),2)
AS HourSum, tblDisplayHour.HourNumber, tblEmpl.CC FROM tblDisplayHour,
tblEmpl GROUP BY tblDisplayHour.DisplayHour, tblDisplayHour.HourNumber,
tblEmpl.CC ORDER BY DisplayHour;
!qryDisplayHours:
dtSelected DisplayHour HourSum HourNumber CC
8/1/2006 12:00 AM 0 1 74843
8/1/2006 12:00 AM 0 1 74937
8/1/2006 12:00 AM 0 1 9420
8/1/2006 1:00 AM 0 2 74843
8/1/2006 1:00 AM 0 2 74937
8/1/2006 1:00 AM 0 2 9420
...
8/1/2006 11:00 PM 1.25 24 74843
8/1/2006 11:00 PM 0.5 24 74937
8/1/2006 11:00 PM 1 24 9420
qryDisplayHours_Crosstab:
TRANSFORM Format(Round(Sum(CDbl(Nz(qryDisplayHours.HourSum, 0))),2),
"#0.00") AS theValue SELECT qryDisplayHours.CC FROM qryDisplayHours
GROUP BY qryDisplayHours.CC PIVOT qryDisplayHours.DisplayHour;
!qryDisplayHours_Crosstab:
CC 12:00:00 AM ... 2:00:00 PM 3:00:00 PM 4:00:00 PM 5:00:00 PM 6:00:00
PM ...
74843 0.00 ... 0.00 1.75 2.00 3.00 3.00 ...
74937 0.00 ... 0.00 1.00 1.00 1.00 1.00 ...
9420 0.00 ... 0.00 0.00 0.50 1.00 1.00 ...
Perhaps using FIRST can weed out the special situation I mentioned when
doing employee counts.
James A. Fortune
MPAP...@FortuneJames.com