Waterfall Report, how to

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a table of employees start data and termination date.

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.
 
JohnG said:
I have a table of employees start data and termination date.

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.
Click to expand...

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.
 
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
 
JohnG said:
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
Click to expand...
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.

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.
 
JohnG said:
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
Click to expand...

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 said:
JohnG said:
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
Click to expand...

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
Click to expand...

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
 
Mike said:
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.
Click to expand...

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
 
James said:
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.
Click to expand...

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.
 
Thanks the ProRateMonth vb was the perfect answer.

James A. Fortune said:
JohnG said:
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
Click to expand...

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
Click to expand...
 
Mike said:
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.
Click to expand...

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

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 said:
James said:
JohnG said:
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
Click to expand...

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
Click to expand...

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
Click to expand...
 
Scott said:
James,

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


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
Click to expand...

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
Click to expand...
Click to expand...

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
(e-mail address removed)
 
James said:
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
(e-mail address removed)
Click to expand...

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:

http://groups.google.com/group/micr...e_frm/thread/e8cf60b1838a3c1/26acc8b879f880fa

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
(e-mail address removed)

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.
 
I think you are on the right track. The thread you sent has a reply that is
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.
 
Scott said:
I think you are on the right track. The thread you sent has a reply that is
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.

:
Click to expand...

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
(e-mail address removed)
 
James said:
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
(e-mail address removed)
Click to expand...

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
(e-mail address removed)
 
James said:
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
(e-mail address removed)
Click to expand...

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
(e-mail address removed)
 
James said:
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
(e-mail address removed)
Click to expand...

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
(e-mail address removed)
 
James said:
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
(e-mail address removed)
Click to expand...

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
(e-mail address removed)
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Excel VBA - counting cells by colors and names to it in two different rows. 0
conditional reporting 4
Payroll Database - Need Help!! 1
Count number of records by date 2
Query to show relevant training 4
Duplicate a set of rows for each item (subdatasheet?) 0
Mailing multiple reports 1
Query trouble 1

Back
Top