Number of hours in report

K

King

Hi

I have a report with the following fields: Employee number, Client ID,
Meeting Date, Meeting Start Time and Meeting End Time. I counted the
number of hours using the function Minutes:
DateDiff("n",[MeetStartTime],[MeetEndTime]). It works fine but with a
slight problem. When an employee meets two Clients on the same day at
the same time , it records two as separate hours and this creates
excess hours spent by the employee.

Employee Cient ID Meet Date Start Time End Time
Duration

1 c1 7/10/2006 10:00 AM 11:00 AM 1:00
1 c2 7/10/2006 10:00 AM 11:00 AM 1:00

Above is an example where in the it records an hour excess on the same
day and same time.

Also, is there anyway to calculate the total duration.

Thanks
 
D

Duane Hookom

Your sample indicates the date and times are the exact same so you should be
able to create a totals query and then sum the results.
 
K

King

Hi Duane

Thanks for the reply. But I couldn't get what exactly you meant.
Following is the sample report of hours spent by an employee E1.

Client ID Program ID Date Start Time End time Duration
1 ABC 07/10/2006 10:00 AM 11:00 AM 1:00
2 ABC 07/10/2006 10:00 AM 11:00 AM 1:00
3 ABC 07/10/2006 11:00 AM 12:00 AM 1:00

The first two meetings are same and it should be counted as 1 hour but
it counts are 2 hours.



Duane said:
Your sample indicates the date and times are the exact same so you should be
able to create a totals query and then sum the results.

--
Duane Hookom
MS Access MVP

King said:
Hi

I have a report with the following fields: Employee number, Client ID,
Meeting Date, Meeting Start Time and Meeting End Time. I counted the
number of hours using the function Minutes:
DateDiff("n",[MeetStartTime],[MeetEndTime]). It works fine but with a
slight problem. When an employee meets two Clients on the same day at
the same time , it records two as separate hours and this creates
excess hours spent by the employee.

Employee Cient ID Meet Date Start Time End Time
Duration

1 c1 7/10/2006 10:00 AM 11:00 AM 1:00
1 c2 7/10/2006 10:00 AM 11:00 AM 1:00

Above is an example where in the it records an hour excess on the same
day and same time.

Also, is there anyway to calculate the total duration.

Thanks
Click to expand...
Click to expand...
 
D

Duane Hookom

Do you need to see the detail of Client ID? If not, change the query to a
totals query and group by all fields and leave out the Client ID. You would
then combine similar records to remove duplicates.

Otherwise I can think of two other options that allow you to display clients
in the report.

--
Duane Hookom
MS Access MVP

King said:
Hi Duane

Thanks for the reply. But I couldn't get what exactly you meant.
Following is the sample report of hours spent by an employee E1.

Client ID Program ID Date Start Time End time Duration
1 ABC 07/10/2006 10:00 AM 11:00 AM 1:00
2 ABC 07/10/2006 10:00 AM 11:00 AM 1:00
3 ABC 07/10/2006 11:00 AM 12:00 AM 1:00

The first two meetings are same and it should be counted as 1 hour but
it counts are 2 hours.



Duane said:
Your sample indicates the date and times are the exact same so you should
be
able to create a totals query and then sum the results.

--
Duane Hookom
MS Access MVP

King said:
Hi

I have a report with the following fields: Employee number, Client ID,
Meeting Date, Meeting Start Time and Meeting End Time. I counted the
number of hours using the function Minutes:
DateDiff("n",[MeetStartTime],[MeetEndTime]). It works fine but with a
slight problem. When an employee meets two Clients on the same day at
the same time , it records two as separate hours and this creates
excess hours spent by the employee.

Employee Cient ID Meet Date Start Time End Time
Duration

1 c1 7/10/2006 10:00 AM 11:00 AM 1:00
1 c2 7/10/2006 10:00 AM 11:00 AM 1:00

Above is an example where in the it records an hour excess on the same
day and same time.

Also, is there anyway to calculate the total duration.

Thanks
Click to expand...
Click to expand...
Click to expand...
 
M

MaCh

Hi Duane

Thanks for your time. I need to see the detail of Client ID as to track
Counselor meetings with Clients.

Here is the query

ID Name Prgm ID Client ID Date Start Time End
Time Minutes
C4 Trisha TIP DEK0703 7 /7 /2006 10:00:00 AM 12:00:00 PM
120
C4 Trisha TIP DEK0703 7 /11/2006 3:30:00 PM 4:30:00 PM
60
C4 Trisha TIP DEK0703 7 /12/2006 2:30:00 PM 4:00:00 PM
90
C4 Trisha TIP DEK0703 7 /23/200 10:00:00 AM 11:00:00 AM
60
C4 Trisha TIP DEK0704 7 /23/2006 10:00:00 AM 11:00:00 AM
60
C4 Trisha TIP DEK0704 7 /24/2006 11:00:00 AM 12:00:00 PM
60
C4 Trisha TIP DEK0704 7 /25/2006 1:00:00 PM 2:30:00 PM
90


As you can see column 4 and column 5, they are for the meetings of the
same counselor with two different clients and it should treat as one
hour but instead treats it as 2 hours ( 60 min for column 4 and 60
minutes for column 5).

SQL View of the above query

SELECT Counselor.CoID, Counselor.CoFName, Counselor.CoLName, [Counselor
Program Administer].PrgmID AS [Counselor Program Administer_PrgmID],
[Client Program Attendance].ClientID, [Client Program
Attendance].[Session Number], [Client Program Attendance].MeetDate,
[Client Program Attendance].MeetStarttime, [Client Program
Attendance].MeetEndTime, DateDiff("n",[MeetStartTime],[MeetEndTime]) AS
Minutes
FROM (Program INNER JOIN [Client Program Attendance] ON Program.PrgmID
= [Client Program Attendance].PrgmID) INNER JOIN (Counselor INNER JOIN
[Counselor Program Administer] ON Counselor.CoID = [Counselor Program
Administer].CoID) ON Program.PrgmID = [Counselor Program
Administer].PrgmID
WHERE (((Counselor.CoID)=[ Enter Counselor ID ]))
ORDER BY [Client Program Attendance].MeetDate;

Please help me. I can send you the Access file if u need.

Thanks
MaCh

Duane said:
Do you need to see the detail of Client ID? If not, change the query to a
totals query and group by all fields and leave out the Client ID. You would
then combine similar records to remove duplicates.

Otherwise I can think of two other options that allow you to display clients
in the report.

--
Duane Hookom
MS Access MVP

King said:
Hi Duane

Thanks for the reply. But I couldn't get what exactly you meant.
Following is the sample report of hours spent by an employee E1.

Client ID Program ID Date Start Time End time Duration
1 ABC 07/10/2006 10:00 AM 11:00 AM 1:00
2 ABC 07/10/2006 10:00 AM 11:00 AM 1:00
3 ABC 07/10/2006 11:00 AM 12:00 AM 1:00

The first two meetings are same and it should be counted as 1 hour but
it counts are 2 hours.



Duane said:
Your sample indicates the date and times are the exact same so you should
be
able to create a totals query and then sum the results.

--
Duane Hookom
MS Access MVP

Hi

I have a report with the following fields: Employee number, Client ID,
Meeting Date, Meeting Start Time and Meeting End Time. I counted the
number of hours using the function Minutes:
DateDiff("n",[MeetStartTime],[MeetEndTime]). It works fine but with a
slight problem. When an employee meets two Clients on the same day at
the same time , it records two as separate hours and this creates
excess hours spent by the employee.

Employee Cient ID Meet Date Start Time End Time
Duration

1 c1 7/10/2006 10:00 AM 11:00 AM 1:00
1 c2 7/10/2006 10:00 AM 11:00 AM 1:00

Above is an example where in the it records an hour excess on the same
day and same time.

Also, is there anyway to calculate the total duration.

Thanks
Click to expand...
Click to expand...
Click to expand...
 
D

Duane Hookom

You can create a report grouping on all but the ClientID field and calculate
the duration in the group header or footer. Set the calculated text box to
Running Sum over the report. Name this text box "txtDurationRunningSum".
Add a text box to your report footer with a control source of:
=txtDurationRunningSum


--
Duane Hookom
MS Access MVP

MaCh said:
Hi Duane

Thanks for your time. I need to see the detail of Client ID as to track
Counselor meetings with Clients.

Here is the query

ID Name Prgm ID Client ID Date Start Time End
Time Minutes
C4 Trisha TIP DEK0703 7 /7 /2006 10:00:00 AM 12:00:00 PM
120
C4 Trisha TIP DEK0703 7 /11/2006 3:30:00 PM 4:30:00 PM
60
C4 Trisha TIP DEK0703 7 /12/2006 2:30:00 PM 4:00:00 PM
90
C4 Trisha TIP DEK0703 7 /23/200 10:00:00 AM 11:00:00 AM
60
C4 Trisha TIP DEK0704 7 /23/2006 10:00:00 AM 11:00:00 AM
60
C4 Trisha TIP DEK0704 7 /24/2006 11:00:00 AM 12:00:00 PM
60
C4 Trisha TIP DEK0704 7 /25/2006 1:00:00 PM 2:30:00 PM
90


As you can see column 4 and column 5, they are for the meetings of the
same counselor with two different clients and it should treat as one
hour but instead treats it as 2 hours ( 60 min for column 4 and 60
minutes for column 5).

SQL View of the above query

SELECT Counselor.CoID, Counselor.CoFName, Counselor.CoLName, [Counselor
Program Administer].PrgmID AS [Counselor Program Administer_PrgmID],
[Client Program Attendance].ClientID, [Client Program
Attendance].[Session Number], [Client Program Attendance].MeetDate,
[Client Program Attendance].MeetStarttime, [Client Program
Attendance].MeetEndTime, DateDiff("n",[MeetStartTime],[MeetEndTime]) AS
Minutes
FROM (Program INNER JOIN [Client Program Attendance] ON Program.PrgmID
= [Client Program Attendance].PrgmID) INNER JOIN (Counselor INNER JOIN
[Counselor Program Administer] ON Counselor.CoID = [Counselor Program
Administer].CoID) ON Program.PrgmID = [Counselor Program
Administer].PrgmID
WHERE (((Counselor.CoID)=[ Enter Counselor ID ]))
ORDER BY [Client Program Attendance].MeetDate;

Please help me. I can send you the Access file if u need.

Thanks
MaCh

Duane said:
Do you need to see the detail of Client ID? If not, change the query to a
totals query and group by all fields and leave out the Client ID. You
would
then combine similar records to remove duplicates.

Otherwise I can think of two other options that allow you to display
clients
in the report.

--
Duane Hookom
MS Access MVP

King said:
Hi Duane

Thanks for the reply. But I couldn't get what exactly you meant.
Following is the sample report of hours spent by an employee E1.

Client ID Program ID Date Start Time End time Duration
1 ABC 07/10/2006 10:00 AM 11:00 AM 1:00
2 ABC 07/10/2006 10:00 AM 11:00 AM 1:00
3 ABC 07/10/2006 11:00 AM 12:00 AM 1:00

The first two meetings are same and it should be counted as 1 hour but
it counts are 2 hours.



Duane Hookom wrote:
Your sample indicates the date and times are the exact same so you
should
be
able to create a totals query and then sum the results.

--
Duane Hookom
MS Access MVP

Hi

I have a report with the following fields: Employee number, Client
ID,
Meeting Date, Meeting Start Time and Meeting End Time. I counted the
number of hours using the function Minutes:
DateDiff("n",[MeetStartTime],[MeetEndTime]). It works fine but with
a
slight problem. When an employee meets two Clients on the same day
at
the same time , it records two as separate hours and this creates
excess hours spent by the employee.

Employee Cient ID Meet Date Start Time End Time
Duration

1 c1 7/10/2006 10:00 AM 11:00 AM 1:00
1 c2 7/10/2006 10:00 AM 11:00 AM 1:00

Above is an example where in the it records an hour excess on the
same
day and same time.

Also, is there anyway to calculate the total duration.

Thanks
Click to expand...
Click to expand...
Click to expand...
 
M

MaCh

Hi Duane

I tried the group and running sum and it still doesn;t work .

Is there any other way.

Thanks

MaCh

Duane said:
You can create a report grouping on all but the ClientID field and calculate
the duration in the group header or footer. Set the calculated text box to
Running Sum over the report. Name this text box "txtDurationRunningSum".
Add a text box to your report footer with a control source of:
=txtDurationRunningSum


--
Duane Hookom
MS Access MVP

MaCh said:
Hi Duane

Thanks for your time. I need to see the detail of Client ID as to track
Counselor meetings with Clients.

Here is the query

ID Name Prgm ID Client ID Date Start Time End
Time Minutes
C4 Trisha TIP DEK0703 7 /7 /2006 10:00:00 AM 12:00:00 PM
120
C4 Trisha TIP DEK0703 7 /11/2006 3:30:00 PM 4:30:00 PM
60
C4 Trisha TIP DEK0703 7 /12/2006 2:30:00 PM 4:00:00 PM
90
C4 Trisha TIP DEK0703 7 /23/200 10:00:00 AM 11:00:00 AM
60
C4 Trisha TIP DEK0704 7 /23/2006 10:00:00 AM 11:00:00 AM
60
C4 Trisha TIP DEK0704 7 /24/2006 11:00:00 AM 12:00:00 PM
60
C4 Trisha TIP DEK0704 7 /25/2006 1:00:00 PM 2:30:00 PM
90


As you can see column 4 and column 5, they are for the meetings of the
same counselor with two different clients and it should treat as one
hour but instead treats it as 2 hours ( 60 min for column 4 and 60
minutes for column 5).

SQL View of the above query

SELECT Counselor.CoID, Counselor.CoFName, Counselor.CoLName, [Counselor
Program Administer].PrgmID AS [Counselor Program Administer_PrgmID],
[Client Program Attendance].ClientID, [Client Program
Attendance].[Session Number], [Client Program Attendance].MeetDate,
[Client Program Attendance].MeetStarttime, [Client Program
Attendance].MeetEndTime, DateDiff("n",[MeetStartTime],[MeetEndTime]) AS
Minutes
FROM (Program INNER JOIN [Client Program Attendance] ON Program.PrgmID
= [Client Program Attendance].PrgmID) INNER JOIN (Counselor INNER JOIN
[Counselor Program Administer] ON Counselor.CoID = [Counselor Program
Administer].CoID) ON Program.PrgmID = [Counselor Program
Administer].PrgmID
WHERE (((Counselor.CoID)=[ Enter Counselor ID ]))
ORDER BY [Client Program Attendance].MeetDate;

Please help me. I can send you the Access file if u need.

Thanks
MaCh

Duane said:
Do you need to see the detail of Client ID? If not, change the query to a
totals query and group by all fields and leave out the Client ID. You
would
then combine similar records to remove duplicates.

Otherwise I can think of two other options that allow you to display
clients
in the report.

--
Duane Hookom
MS Access MVP

Hi Duane

Thanks for the reply. But I couldn't get what exactly you meant.
Following is the sample report of hours spent by an employee E1.

Client ID Program ID Date Start Time End time Duration
1 ABC 07/10/2006 10:00 AM 11:00 AM 1:00
2 ABC 07/10/2006 10:00 AM 11:00 AM 1:00
3 ABC 07/10/2006 11:00 AM 12:00 AM 1:00

The first two meetings are same and it should be counted as 1 hour but
it counts are 2 hours.



Duane Hookom wrote:
Your sample indicates the date and times are the exact same so you
should
be
able to create a totals query and then sum the results.

--
Duane Hookom
MS Access MVP

Hi

I have a report with the following fields: Employee number, Client
ID,
Meeting Date, Meeting Start Time and Meeting End Time. I counted the
number of hours using the function Minutes:
DateDiff("n",[MeetStartTime],[MeetEndTime]). It works fine but with
a
slight problem. When an employee meets two Clients on the same day
at
the same time , it records two as separate hours and this creates
excess hours spent by the employee.

Employee Cient ID Meet Date Start Time End Time
Duration

1 c1 7/10/2006 10:00 AM 11:00 AM 1:00
1 c2 7/10/2006 10:00 AM 11:00 AM 1:00

Above is an example where in the it records an hour excess on the
same
day and same time.

Also, is there anyway to calculate the total duration.

Thanks
Click to expand...
Click to expand...
Click to expand...
 
D

Duane Hookom

"it still doesn;t work" doesn't give us any opportunity to make suggestions
specific to your issue.

The other solution is to create totals queries that group by all the fields
that I have mentioned and calculate your total outside of the report. Then
either add the final totals query to the report's record source or use a
subreport or use DLookup() to display the final calculation in your report.

--
Duane Hookom
MS Access MVP

MaCh said:
Hi Duane

I tried the group and running sum and it still doesn;t work .

Is there any other way.

Thanks

MaCh

Duane said:
You can create a report grouping on all but the ClientID field and
calculate
the duration in the group header or footer. Set the calculated text box
to
Running Sum over the report. Name this text box "txtDurationRunningSum".
Add a text box to your report footer with a control source of:
=txtDurationRunningSum


--
Duane Hookom
MS Access MVP

MaCh said:
Hi Duane

Thanks for your time. I need to see the detail of Client ID as to track
Counselor meetings with Clients.

Here is the query

ID Name Prgm ID Client ID Date Start Time End
Time Minutes
C4 Trisha TIP DEK0703 7 /7 /2006 10:00:00 AM 12:00:00 PM
120
C4 Trisha TIP DEK0703 7 /11/2006 3:30:00 PM 4:30:00 PM
60
C4 Trisha TIP DEK0703 7 /12/2006 2:30:00 PM 4:00:00 PM
90
C4 Trisha TIP DEK0703 7 /23/200 10:00:00 AM 11:00:00 AM
60
C4 Trisha TIP DEK0704 7 /23/2006 10:00:00 AM 11:00:00 AM
60
C4 Trisha TIP DEK0704 7 /24/2006 11:00:00 AM 12:00:00 PM
60
C4 Trisha TIP DEK0704 7 /25/2006 1:00:00 PM 2:30:00 PM
90


As you can see column 4 and column 5, they are for the meetings of the
same counselor with two different clients and it should treat as one
hour but instead treats it as 2 hours ( 60 min for column 4 and 60
minutes for column 5).

SQL View of the above query

SELECT Counselor.CoID, Counselor.CoFName, Counselor.CoLName, [Counselor
Program Administer].PrgmID AS [Counselor Program Administer_PrgmID],
[Client Program Attendance].ClientID, [Client Program
Attendance].[Session Number], [Client Program Attendance].MeetDate,
[Client Program Attendance].MeetStarttime, [Client Program
Attendance].MeetEndTime, DateDiff("n",[MeetStartTime],[MeetEndTime]) AS
Minutes
FROM (Program INNER JOIN [Client Program Attendance] ON Program.PrgmID
= [Client Program Attendance].PrgmID) INNER JOIN (Counselor INNER JOIN
[Counselor Program Administer] ON Counselor.CoID = [Counselor Program
Administer].CoID) ON Program.PrgmID = [Counselor Program
Administer].PrgmID
WHERE (((Counselor.CoID)=[ Enter Counselor ID ]))
ORDER BY [Client Program Attendance].MeetDate;

Please help me. I can send you the Access file if u need.

Thanks
MaCh

Duane Hookom wrote:
Do you need to see the detail of Client ID? If not, change the query
to a
totals query and group by all fields and leave out the Client ID. You
would
then combine similar records to remove duplicates.

Otherwise I can think of two other options that allow you to display
clients
in the report.

--
Duane Hookom
MS Access MVP

Hi Duane

Thanks for the reply. But I couldn't get what exactly you meant.
Following is the sample report of hours spent by an employee E1.

Client ID Program ID Date Start Time End time
Duration
1 ABC 07/10/2006 10:00 AM 11:00 AM 1:00
2 ABC 07/10/2006 10:00 AM 11:00 AM 1:00
3 ABC 07/10/2006 11:00 AM 12:00 AM 1:00

The first two meetings are same and it should be counted as 1 hour
but
it counts are 2 hours.



Duane Hookom wrote:
Your sample indicates the date and times are the exact same so you
should
be
able to create a totals query and then sum the results.

--
Duane Hookom
MS Access MVP

Hi

I have a report with the following fields: Employee number,
Client
ID,
Meeting Date, Meeting Start Time and Meeting End Time. I counted
the
number of hours using the function Minutes:
DateDiff("n",[MeetStartTime],[MeetEndTime]). It works fine but
with
a
slight problem. When an employee meets two Clients on the same
day
at
the same time , it records two as separate hours and this creates
excess hours spent by the employee.

Employee Cient ID Meet Date Start Time End Time
Duration

1 c1 7/10/2006 10:00 AM 11:00 AM
1:00
1 c2 7/10/2006 10:00 AM 11:00 AM
1:00

Above is an example where in the it records an hour excess on the
same
day and same time.

Also, is there anyway to calculate the total duration.

Thanks
Click to expand...
Click to expand...
Click to expand...
 

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

start and end times graphically 1
Add Hours by Looping through Data Q 27
Excel Facetime Measurement 1
Calculate Face Time 4
How to enter start and end date/time in excel based on some specific value. 7
Determine if night shift by start & end time 2
sum of expression 1
DATABASE REPORT/CROSSTAB QUERY HELP 2

Top