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