Hi
I have 2 queries where I calculated time elapsed. In the first I
calculated time spent by a employee in group meetings and in the second
query, I calculated time spent by an employee in personal meetings with
the clients. I wish to join the two queries to sum up the total time.
I have the following fields
Fields for Group hours query
CoID
CoFName
CoLName
Program Id
Session Number
Meeting Number
Date
Start Time
End Time
Minutes: DateDiff("n",[Start Time],[End Time])
where
CoID, CoFName, CoLName FROM COUNSELOR TABLE
Program Id, Session Number, Meeting Number,Date, Start Time, End Time
FROM GROUP PROGRAM SCHEDULE
Fields for the individual hours query
CoID
CoFName
CoLName
ClientID
PrgmID
Date
Meet Start Time
Meet End Time
Minutes: DateDiff("n",[Meet Start Time],[Meet End Time])
CoID, COFName, CoLName from COUNSELOR Table
ClientID, PrgmID, Date, Meet Start Time, Meet End Time FROM PERSONAL
MEETINGS Table
Following are the SQL views
SQL view of first query
SELECT [Personal Meetings].ClientID, Counselor.CoID, Counselor.CoFName,
Counselor.CoLName, [Personal Meetings].PrgmID, [Personal
Meetings].Date, [Personal Meetings].[Meet Start Time], [Personal
Meetings].[Meet End Time], DateDiff("n",[Meet Start Time],[Meet End
Time]) AS Minutes
FROM (Program INNER JOIN (Counselor INNER JOIN [Counselor Program
Administer] ON Counselor.CoID=[Counselor Program Administer].CoID) ON
Program.PrgmID=[Counselor Program Administer].PrgmID) INNER JOIN
[Personal Meetings] ON Program.PrgmID=[Personal Meetings].PrgmID;
SQL view of second query
SELECT Counselor.CoID, Counselor.CoFName, Counselor.CoLName, [Group
Program Schedule].[Meeting Number], [Group Program Schedule].[Program
Id], [Group Program Schedule].[Session Number], [Group Program
Schedule].Date, [Group Program Schedule].[Start Time], [Group Program
Schedule].[End Time], DateDiff("n",[Start Time],[End Time]) AS Minutes
FROM (Program INNER JOIN (Counselor INNER JOIN [Counselor Program
Administer] ON Counselor.CoID = [Counselor Program Administer].CoID) ON
Program.PrgmID = [Counselor Program Administer].PrgmID) INNER JOIN
[Group Program Schedule] ON Program.PrgmID = [Group Program
Schedule].[Program Id];
Now I wish to sum the minutes from two query since they refer to the
same counselor field.
Can anyone help me ?
Thanks
MaCh