Sum and Avg with DateDiff

P

pvdalen

Hi all,

This seems a little strange, but I have this query:

SELECT Worker.WorkerName, Sum((DateDiff("n",([taskall].[dateordered] & " " &
[taskall].[timein]),([taskall].[datecompleted] & " " &
[taskall].[timeout]))/60)) AS [Task Hours]
FROM Worker RIGHT JOIN TaskAll ON Worker.WorkerID = TaskAll.WorkerID
WHERE (((TaskAll.DateOrdered) Between #8/28/2005# And #8/30/2005#))
GROUP BY Worker.WorkerName;

which works just fine. The correct total of hours is shown in the second
column as expected. However, if I try to get the daily average of those
hours worked, changing only "Sum" to "Avg" in the query above:

SELECT Worker.WorkerName, Avg((DateDiff("n",([taskall].[dateordered] & " " &
[taskall].[timein]),([taskall].[datecompleted] & " " &
[taskall].[timeout]))/60)) AS [Task Hours]
FROM Worker RIGHT JOIN TaskAll ON Worker.WorkerID = TaskAll.WorkerID
WHERE (((TaskAll.DateOrdered) Between #8/28/2005# And #8/30/2005#))
GROUP BY Worker.WorkerName;

I get, in the second column, anything but the correct number. It seems,
from the results, as if the system is dividing the total hours by a multiple
of the days indicated (?). Anyone have any clues? Does the AVG function not
work so well with the DateDiff function?

Thanks very much,
Paul
 
K

Ken Snell [MVP]

Try adding the date and time fields, not concatenating them:

SELECT Worker.WorkerName, Avg((DateDiff("n",([taskall].[dateordered] +
[taskall].[timein]),([taskall].[datecompleted] + [taskall].[timeout]))/60))
AS [Task Hours]
FROM Worker RIGHT JOIN TaskAll ON Worker.WorkerID = TaskAll.WorkerID
WHERE (((TaskAll.DateOrdered) Between #8/28/2005# And #8/30/2005#))
GROUP BY Worker.WorkerName;
 
P

pvdalen

Hey Ken, thanks for the quick response.

I tried what you suggest, but got the same results, sorry.

Anything else come to mind?

Thanks again,
Paul


Ken Snell said:
Try adding the date and time fields, not concatenating them:

SELECT Worker.WorkerName, Avg((DateDiff("n",([taskall].[dateordered] +
[taskall].[timein]),([taskall].[datecompleted] + [taskall].[timeout]))/60))
AS [Task Hours]
FROM Worker RIGHT JOIN TaskAll ON Worker.WorkerID = TaskAll.WorkerID
WHERE (((TaskAll.DateOrdered) Between #8/28/2005# And #8/30/2005#))
GROUP BY Worker.WorkerName;

--

Ken Snell
<MS ACCESS MVP>


pvdalen said:
Hi all,

This seems a little strange, but I have this query:

SELECT Worker.WorkerName, Sum((DateDiff("n",([taskall].[dateordered] & " "
&
[taskall].[timein]),([taskall].[datecompleted] & " " &
[taskall].[timeout]))/60)) AS [Task Hours]
FROM Worker RIGHT JOIN TaskAll ON Worker.WorkerID = TaskAll.WorkerID
WHERE (((TaskAll.DateOrdered) Between #8/28/2005# And #8/30/2005#))
GROUP BY Worker.WorkerName;

which works just fine. The correct total of hours is shown in the second
column as expected. However, if I try to get the daily average of those
hours worked, changing only "Sum" to "Avg" in the query above:

SELECT Worker.WorkerName, Avg((DateDiff("n",([taskall].[dateordered] & " "
&
[taskall].[timein]),([taskall].[datecompleted] & " " &
[taskall].[timeout]))/60)) AS [Task Hours]
FROM Worker RIGHT JOIN TaskAll ON Worker.WorkerID = TaskAll.WorkerID
WHERE (((TaskAll.DateOrdered) Between #8/28/2005# And #8/30/2005#))
GROUP BY Worker.WorkerName;

I get, in the second column, anything but the correct number. It seems,
from the results, as if the system is dividing the total hours by a
multiple
of the days indicated (?). Anyone have any clues? Does the AVG function
not
work so well with the DateDiff function?

Thanks very much,
Paul
Click to expand...
Click to expand...
 
K

Ken Snell [MVP]

Why are you using a RIGHT JOIN in the query? That will return all records
from TASKALL table. Try this:

SELECT Worker.WorkerName, Avg((DateDiff("n",([taskall].[dateordered] +
[taskall].[timein]),([taskall].[datecompleted] + [taskall].[timeout]))/60))
AS [Task Hours]
FROM Worker INNER JOIN TaskAll ON Worker.WorkerID = TaskAll.WorkerID
WHERE (((TaskAll.DateOrdered) Between #8/28/2005# And #8/30/2005#))
GROUP BY Worker.WorkerName;

--

Ken Snell
<MS ACCESS MVP>

pvdalen said:
Hey Ken, thanks for the quick response.

I tried what you suggest, but got the same results, sorry.

Anything else come to mind?

Thanks again,
Paul


Ken Snell said:
Try adding the date and time fields, not concatenating them:

SELECT Worker.WorkerName, Avg((DateDiff("n",([taskall].[dateordered] +
[taskall].[timein]),([taskall].[datecompleted] +
[taskall].[timeout]))/60))
AS [Task Hours]
FROM Worker RIGHT JOIN TaskAll ON Worker.WorkerID = TaskAll.WorkerID
WHERE (((TaskAll.DateOrdered) Between #8/28/2005# And #8/30/2005#))
GROUP BY Worker.WorkerName;

--

Ken Snell
<MS ACCESS MVP>


pvdalen said:
Hi all,

This seems a little strange, but I have this query:

SELECT Worker.WorkerName, Sum((DateDiff("n",([taskall].[dateordered] &
" "
&
[taskall].[timein]),([taskall].[datecompleted] & " " &
[taskall].[timeout]))/60)) AS [Task Hours]
FROM Worker RIGHT JOIN TaskAll ON Worker.WorkerID = TaskAll.WorkerID
WHERE (((TaskAll.DateOrdered) Between #8/28/2005# And #8/30/2005#))
GROUP BY Worker.WorkerName;

which works just fine. The correct total of hours is shown in the
second
column as expected. However, if I try to get the daily average of
those
hours worked, changing only "Sum" to "Avg" in the query above:

SELECT Worker.WorkerName, Avg((DateDiff("n",([taskall].[dateordered] &
" "
&
[taskall].[timein]),([taskall].[datecompleted] & " " &
[taskall].[timeout]))/60)) AS [Task Hours]
FROM Worker RIGHT JOIN TaskAll ON Worker.WorkerID = TaskAll.WorkerID
WHERE (((TaskAll.DateOrdered) Between #8/28/2005# And #8/30/2005#))
GROUP BY Worker.WorkerName;

I get, in the second column, anything but the correct number. It
seems,
from the results, as if the system is dividing the total hours by a
multiple
of the days indicated (?). Anyone have any clues? Does the AVG
function
not
work so well with the DateDiff function?

Thanks very much,
Paul
Click to expand...
Click to expand...
Click to expand...
 
P

pvdalen

Why? Pretty much because I'm just this side of lazy, and a bad enough typist
that I built the query in design mode. :)

I do appreaciate your efforts, but changing the join didn't work, either.


Ken Snell said:
Why are you using a RIGHT JOIN in the query? That will return all records
from TASKALL table. Try this:

SELECT Worker.WorkerName, Avg((DateDiff("n",([taskall].[dateordered] +
[taskall].[timein]),([taskall].[datecompleted] + [taskall].[timeout]))/60))
AS [Task Hours]
FROM Worker INNER JOIN TaskAll ON Worker.WorkerID = TaskAll.WorkerID
WHERE (((TaskAll.DateOrdered) Between #8/28/2005# And #8/30/2005#))
GROUP BY Worker.WorkerName;

--

Ken Snell
<MS ACCESS MVP>

pvdalen said:
Hey Ken, thanks for the quick response.

I tried what you suggest, but got the same results, sorry.

Anything else come to mind?

Thanks again,
Paul


Ken Snell said:
Try adding the date and time fields, not concatenating them:

SELECT Worker.WorkerName, Avg((DateDiff("n",([taskall].[dateordered] +
[taskall].[timein]),([taskall].[datecompleted] +
[taskall].[timeout]))/60))
AS [Task Hours]
FROM Worker RIGHT JOIN TaskAll ON Worker.WorkerID = TaskAll.WorkerID
WHERE (((TaskAll.DateOrdered) Between #8/28/2005# And #8/30/2005#))
GROUP BY Worker.WorkerName;

--

Ken Snell
<MS ACCESS MVP>


Hi all,

This seems a little strange, but I have this query:

SELECT Worker.WorkerName, Sum((DateDiff("n",([taskall].[dateordered] &
" "
&
[taskall].[timein]),([taskall].[datecompleted] & " " &
[taskall].[timeout]))/60)) AS [Task Hours]
FROM Worker RIGHT JOIN TaskAll ON Worker.WorkerID = TaskAll.WorkerID
WHERE (((TaskAll.DateOrdered) Between #8/28/2005# And #8/30/2005#))
GROUP BY Worker.WorkerName;

which works just fine. The correct total of hours is shown in the
second
column as expected. However, if I try to get the daily average of
those
hours worked, changing only "Sum" to "Avg" in the query above:

SELECT Worker.WorkerName, Avg((DateDiff("n",([taskall].[dateordered] &
" "
&
[taskall].[timein]),([taskall].[datecompleted] & " " &
[taskall].[timeout]))/60)) AS [Task Hours]
FROM Worker RIGHT JOIN TaskAll ON Worker.WorkerID = TaskAll.WorkerID
WHERE (((TaskAll.DateOrdered) Between #8/28/2005# And #8/30/2005#))
GROUP BY Worker.WorkerName;

I get, in the second column, anything but the correct number. It
seems,
from the results, as if the system is dividing the total hours by a
multiple
of the days indicated (?). Anyone have any clues? Does the AVG
function
not
work so well with the DateDiff function?

Thanks very much,
Paul
Click to expand...
Click to expand...
Click to expand...
 
K

Ken Snell [MVP]

OK - then I would guess that your "dateordered" and "timein" and "timeout"
fields are not date/time fields? Are they text fields or other types of
fields?

--

Ken Snell
<MS ACCESS MVP>

pvdalen said:
Why? Pretty much because I'm just this side of lazy, and a bad enough
typist
that I built the query in design mode. :)

I do appreaciate your efforts, but changing the join didn't work, either.


Ken Snell said:
Why are you using a RIGHT JOIN in the query? That will return all records
from TASKALL table. Try this:

SELECT Worker.WorkerName, Avg((DateDiff("n",([taskall].[dateordered] +
[taskall].[timein]),([taskall].[datecompleted] +
[taskall].[timeout]))/60))
AS [Task Hours]
FROM Worker INNER JOIN TaskAll ON Worker.WorkerID = TaskAll.WorkerID
WHERE (((TaskAll.DateOrdered) Between #8/28/2005# And #8/30/2005#))
GROUP BY Worker.WorkerName;

--

Ken Snell
<MS ACCESS MVP>

pvdalen said:
Hey Ken, thanks for the quick response.

I tried what you suggest, but got the same results, sorry.

Anything else come to mind?

Thanks again,
Paul


:

Try adding the date and time fields, not concatenating them:

SELECT Worker.WorkerName, Avg((DateDiff("n",([taskall].[dateordered] +
[taskall].[timein]),([taskall].[datecompleted] +
[taskall].[timeout]))/60))
AS [Task Hours]
FROM Worker RIGHT JOIN TaskAll ON Worker.WorkerID = TaskAll.WorkerID
WHERE (((TaskAll.DateOrdered) Between #8/28/2005# And #8/30/2005#))
GROUP BY Worker.WorkerName;

--

Ken Snell
<MS ACCESS MVP>


Hi all,

This seems a little strange, but I have this query:

SELECT Worker.WorkerName, Sum((DateDiff("n",([taskall].[dateordered]
&
" "
&
[taskall].[timein]),([taskall].[datecompleted] & " " &
[taskall].[timeout]))/60)) AS [Task Hours]
FROM Worker RIGHT JOIN TaskAll ON Worker.WorkerID = TaskAll.WorkerID
WHERE (((TaskAll.DateOrdered) Between #8/28/2005# And #8/30/2005#))
GROUP BY Worker.WorkerName;

which works just fine. The correct total of hours is shown in the
second
column as expected. However, if I try to get the daily average of
those
hours worked, changing only "Sum" to "Avg" in the query above:

SELECT Worker.WorkerName, Avg((DateDiff("n",([taskall].[dateordered]
&
" "
&
[taskall].[timein]),([taskall].[datecompleted] & " " &
[taskall].[timeout]))/60)) AS [Task Hours]
FROM Worker RIGHT JOIN TaskAll ON Worker.WorkerID = TaskAll.WorkerID
WHERE (((TaskAll.DateOrdered) Between #8/28/2005# And #8/30/2005#))
GROUP BY Worker.WorkerName;

I get, in the second column, anything but the correct number. It
seems,
from the results, as if the system is dividing the total hours by a
multiple
of the days indicated (?). Anyone have any clues? Does the AVG
function
not
work so well with the DateDiff function?

Thanks very much,
Paul
Click to expand...
Click to expand...
Click to expand...
 
P

pvdalen

Top o' the morning, Ken,

I believe I figured out why the AVG function is not working as I'd hoped. I
noticed that the function was working for some workers, but not for others;
that's because the workers for whom the AVG function was working only had one
entry in the database for the day indicated.

For example, on a given day, Worker X had three job entries in the database
for three seperate jobs, which is correct. So, for that day, the AVG
function was taking the total hours for the day, say 24, and returning the
average across each database entry, 8. By now, I'm sure you're telling
yourself that this is exactly the intended functionality of the AVG function,
so I apologize if I wasted your time. I guess what I expected was the number
generated by the SUM function to be divided only by the number of days I
specified. Sorry for thinking the database was "magic"; funny thing is I've
been irritated for others where I work for thinking the same thing.

So, I just modified the query to divide the SUM function by the number of
days in the range in which I'm searching, which works just fine. Am I
missing something else that could get me what I want?

Thanks again,
Paul


Ken Snell said:
OK - then I would guess that your "dateordered" and "timein" and "timeout"
fields are not date/time fields? Are they text fields or other types of
fields?

--

Ken Snell
<MS ACCESS MVP>

pvdalen said:
Why? Pretty much because I'm just this side of lazy, and a bad enough
typist
that I built the query in design mode. :)

I do appreaciate your efforts, but changing the join didn't work, either.


Ken Snell said:
Why are you using a RIGHT JOIN in the query? That will return all records
from TASKALL table. Try this:

SELECT Worker.WorkerName, Avg((DateDiff("n",([taskall].[dateordered] +
[taskall].[timein]),([taskall].[datecompleted] +
[taskall].[timeout]))/60))
AS [Task Hours]
FROM Worker INNER JOIN TaskAll ON Worker.WorkerID = TaskAll.WorkerID
WHERE (((TaskAll.DateOrdered) Between #8/28/2005# And #8/30/2005#))
GROUP BY Worker.WorkerName;

--

Ken Snell
<MS ACCESS MVP>

Hey Ken, thanks for the quick response.

I tried what you suggest, but got the same results, sorry.

Anything else come to mind?

Thanks again,
Paul


:

Try adding the date and time fields, not concatenating them:

SELECT Worker.WorkerName, Avg((DateDiff("n",([taskall].[dateordered] +
[taskall].[timein]),([taskall].[datecompleted] +
[taskall].[timeout]))/60))
AS [Task Hours]
FROM Worker RIGHT JOIN TaskAll ON Worker.WorkerID = TaskAll.WorkerID
WHERE (((TaskAll.DateOrdered) Between #8/28/2005# And #8/30/2005#))
GROUP BY Worker.WorkerName;

--

Ken Snell
<MS ACCESS MVP>


Hi all,

This seems a little strange, but I have this query:

SELECT Worker.WorkerName, Sum((DateDiff("n",([taskall].[dateordered]
&
" "
&
[taskall].[timein]),([taskall].[datecompleted] & " " &
[taskall].[timeout]))/60)) AS [Task Hours]
FROM Worker RIGHT JOIN TaskAll ON Worker.WorkerID = TaskAll.WorkerID
WHERE (((TaskAll.DateOrdered) Between #8/28/2005# And #8/30/2005#))
GROUP BY Worker.WorkerName;

which works just fine. The correct total of hours is shown in the
second
column as expected. However, if I try to get the daily average of
those
hours worked, changing only "Sum" to "Avg" in the query above:

SELECT Worker.WorkerName, Avg((DateDiff("n",([taskall].[dateordered]
&
" "
&
[taskall].[timein]),([taskall].[datecompleted] & " " &
[taskall].[timeout]))/60)) AS [Task Hours]
FROM Worker RIGHT JOIN TaskAll ON Worker.WorkerID = TaskAll.WorkerID
WHERE (((TaskAll.DateOrdered) Between #8/28/2005# And #8/30/2005#))
GROUP BY Worker.WorkerName;

I get, in the second column, anything but the correct number. It
seems,
from the results, as if the system is dividing the total hours by a
multiple
of the days indicated (?). Anyone have any clues? Does the AVG
function
not
work so well with the DateDiff function?

Thanks very much,
Paul
Click to expand...
Click to expand...
Click to expand...
 
K

Ken Snell [MVP]

Dividing by the number of days is the correct approach, if I'm understanding
you correctly. You may be able to get that number of days by the Count
aggregate function, but I've not studied your query enough to be sure that I
"see" the data setup clearly.

Good luck!

--

Ken Snell
<MS ACCESS MVP>

pvdalen said:
Top o' the morning, Ken,

I believe I figured out why the AVG function is not working as I'd hoped.
I
noticed that the function was working for some workers, but not for
others;
that's because the workers for whom the AVG function was working only had
one
entry in the database for the day indicated.

For example, on a given day, Worker X had three job entries in the
database
for three seperate jobs, which is correct. So, for that day, the AVG
function was taking the total hours for the day, say 24, and returning the
average across each database entry, 8. By now, I'm sure you're telling
yourself that this is exactly the intended functionality of the AVG
function,
so I apologize if I wasted your time. I guess what I expected was the
number
generated by the SUM function to be divided only by the number of days I
specified. Sorry for thinking the database was "magic"; funny thing is
I've
been irritated for others where I work for thinking the same thing.

So, I just modified the query to divide the SUM function by the number of
days in the range in which I'm searching, which works just fine. Am I
missing something else that could get me what I want?

Thanks again,
Paul


Ken Snell said:
OK - then I would guess that your "dateordered" and "timein" and
"timeout"
fields are not date/time fields? Are they text fields or other types of
fields?

--

Ken Snell
<MS ACCESS MVP>

pvdalen said:
Why? Pretty much because I'm just this side of lazy, and a bad enough
typist
that I built the query in design mode. :)

I do appreaciate your efforts, but changing the join didn't work,
either.


:

Why are you using a RIGHT JOIN in the query? That will return all
records
from TASKALL table. Try this:

SELECT Worker.WorkerName, Avg((DateDiff("n",([taskall].[dateordered] +
[taskall].[timein]),([taskall].[datecompleted] +
[taskall].[timeout]))/60))
AS [Task Hours]
FROM Worker INNER JOIN TaskAll ON Worker.WorkerID = TaskAll.WorkerID
WHERE (((TaskAll.DateOrdered) Between #8/28/2005# And #8/30/2005#))
GROUP BY Worker.WorkerName;

--

Ken Snell
<MS ACCESS MVP>

Hey Ken, thanks for the quick response.

I tried what you suggest, but got the same results, sorry.

Anything else come to mind?

Thanks again,
Paul


:

Try adding the date and time fields, not concatenating them:

SELECT Worker.WorkerName,
Avg((DateDiff("n",([taskall].[dateordered] +
[taskall].[timein]),([taskall].[datecompleted] +
[taskall].[timeout]))/60))
AS [Task Hours]
FROM Worker RIGHT JOIN TaskAll ON Worker.WorkerID =
TaskAll.WorkerID
WHERE (((TaskAll.DateOrdered) Between #8/28/2005# And #8/30/2005#))
GROUP BY Worker.WorkerName;

--

Ken Snell
<MS ACCESS MVP>


Hi all,

This seems a little strange, but I have this query:

SELECT Worker.WorkerName,
Sum((DateDiff("n",([taskall].[dateordered]
&
" "
&
[taskall].[timein]),([taskall].[datecompleted] & " " &
[taskall].[timeout]))/60)) AS [Task Hours]
FROM Worker RIGHT JOIN TaskAll ON Worker.WorkerID =
TaskAll.WorkerID
WHERE (((TaskAll.DateOrdered) Between #8/28/2005# And
#8/30/2005#))
GROUP BY Worker.WorkerName;

which works just fine. The correct total of hours is shown in
the
second
column as expected. However, if I try to get the daily average
of
those
hours worked, changing only "Sum" to "Avg" in the query above:

SELECT Worker.WorkerName,
Avg((DateDiff("n",([taskall].[dateordered]
&
" "
&
[taskall].[timein]),([taskall].[datecompleted] & " " &
[taskall].[timeout]))/60)) AS [Task Hours]
FROM Worker RIGHT JOIN TaskAll ON Worker.WorkerID =
TaskAll.WorkerID
WHERE (((TaskAll.DateOrdered) Between #8/28/2005# And
#8/30/2005#))
GROUP BY Worker.WorkerName;

I get, in the second column, anything but the correct number. It
seems,
from the results, as if the system is dividing the total hours by
a
multiple
of the days indicated (?). Anyone have any clues? Does the AVG
function
not
work so well with the DateDiff function?

Thanks very much,
Paul
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

Goofy Query behavior 2
outer join not working 9
Sum and DateDiff 2
Add a field in a query. Easy. . .so I though. 5
Need the SUM TOTAL in Hours for Time Out-Time In 2
Calculations using time and currency 2
Time Conversions 2
Time Calculation thought process 8

Top