473,326 Members | 2,076 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

Summing of Hours

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

Sep 7 '06 #1
0 1362

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

9
by: Yaroslav Bulatov | last post by:
I made an array of 10 million floats timed how long it takes to sum the elements, here's what I got (millis): gcc -O2: 21 Python with numarray: 104 Python with Numeric: 302...
2
by: BillG | last post by:
I have a datagrid with a start and an end time. I subtract the start time from the end time in each row and place the difference into a TimeSpan variable. Now I want to total up the Timespans and...
2
by: SunMan | last post by:
Hello! I am trying to create a program that will ask for a user to enter a series of letters (codes) and then print out a table that shows the codes in decending frequency. Only letters will be...
2
by: steveprevost | last post by:
I have a text field that contains mostly numbers, but the rows without a value have a blank in them. I need to sum on this row, but I get a conversion error. I've tried using various functions...
13
by: Don Sealer | last post by:
I have about 40 different defects I'm tracking. I'd like to have all of these defects totaled individually, both by month and by year. I'd like to show these results in a report. I know I could...
4
by: King | last post by:
Hi I have two queries where in I calculated number of hours worked by an employee. Fields for the first query Meeting Id ( Auto Number ) Counselor ID ( text ) Date
3
by: Paul Wagstaff | last post by:
Hi All I have an aggregate query that groups by each user, then calculates Sum(TimeInvested). I need to display the total time (per user) in hrs / mins e.g. 45:30 (= 45 hrs 30 mins). Can...
2
by: ckpoll2 | last post by:
Hello, I have a report in which I'm trying to sum the numbers that appear in . The control source of is: =IIf( Between And And Like "Overtime - *",,"0") and 's control source is Hours...
7
by: lethek39 | last post by:
Hey I have been trying to figure out how to sum rows and columns in a matrix square. I also have been trying to get the program to list the numbers of the diagonal in the matrix. So far this is the...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.