Sum(DateDiff

[Gaetanm via AccessMonster.com]

I"m trying to have a total hours form two Date/Time fields
that will come up as hh.nn total. I'm getting this error message
and unfortunately I don't understand it maybe someone can help?

Here is what I have so far:

SELECT Clock_Table.StartDate, Clock_Table.StopDate, SUM(DateDiff("n",
[Startdate],[StopDate]))/60 AS test
FROM Clock_Table;

Here is the error message:

'You tried to execute a query that does not include the specified expression
"Startdate" as part of an aggregate function'

I know the above code won't get me hh.nn but I need to get there.

Thanks
Gaetanm

 

[Duane Hookom]

Either remove the SUM() or make a totals query that groups by the other
fields:

SELECT Clock_Table.StartDate, Clock_Table.StopDate,
DateDiff("n",[Startdate],[StopDate])/60 AS test
FROM Clock_Table;

or

SELECT Clock_Table.StartDate, Clock_Table.StopDate, SUM(DateDiff("n",
[Startdate],[StopDate]))/60 AS test
FROM Clock_Table
GROUP BY Clock_Table.StartDate, Clock_Table.StopDate;

 

[Marshall Barton]

I"m trying to have a total hours form two Date/Time fields
that will come up as hh.nn total. I'm getting this error message
and unfortunately I don't understand it maybe someone can help?

Here is what I have so far:

SELECT Clock_Table.StartDate, Clock_Table.StopDate, SUM(DateDiff("n",
[Startdate],[StopDate]))/60 AS test
FROM Clock_Table;

Here is the error message:

'You tried to execute a query that does not include the specified expression
"Startdate" as part of an aggregate function'

When you use an aggregate function, you need to use the
GROUP BY clause to specify the field(s) that are used to
group the aggregation. In this case the start and stop
dates are definitely not candidates for that purpose. For
example, if you want to sum the time spent on each project,
you would use:

SELECT projectID,
SUM(DateDiff("n", [Startdate],[StopDate]))/60 AS test
FROM Clock_Table
GROUP BY projectID

To get hh:nn from the total number of minutes (however
calculated), use this kind of expression:

TotalMin \ 60 & Format(TotalMin Mod 60, "\:00")

 

[Gaetanm via AccessMonster.com]

I"m trying to have a total hours form two Date/Time fields
that will come up as hh.nn total. I'm getting this error message

[quoted text clipped - 10 lines]

'You tried to execute a query that does not include the specified expression
"Startdate" as part of an aggregate function'

When you use an aggregate function, you need to use the
GROUP BY clause to specify the field(s) that are used to
group the aggregation. In this case the start and stop
dates are definitely not candidates for that purpose. For
example, if you want to sum the time spent on each project,
you would use:

SELECT projectID,
SUM(DateDiff("n", [Startdate],[StopDate]))/60 AS test
FROM Clock_Table
GROUP BY projectID

To get hh:nn from the total number of minutes (however
calculated), use this kind of expression:

TotalMin \ 60 & Format(TotalMin Mod 60, "\:00")

Hi Marshall And Duane

I tried what you suggested but to no avail. Its as if the sumfuction is not
executing. All I get is the DateDiff. Below I posted what you seggested.
Below that I hae two queries that get the result other that hh.nn ( i didn't
get there yet) this gives me my total time.
Question is there a way to make these two qweries into one?

SELECT Clock_Table.StartDate, Clock_Table.StopDate, SUM(DateDiff("n",
Startdate,StopDate))/60 AS test
FROM Clock_Table
GROUP by Clock_Table.StartDate, Clock_Table.StopDate;



SELECT Clock_table.EmployeeID, Clock_table.StartDate, Clock_table.StopDate,
DateDiff("n",[Startdate],[Stopdate])\60 & Format(DateDiff("n",[Startdate],
[Stopdate]) Mod 60,"\:00") AS SumOfTotalHoursMinutes
FROM Clock_table
WHERE (((Clock_table.StopDate)>Date()-Weekday(Date()+1)) AND ((Clock_table.
EmployeeID)=[Forms]![frmClock_Start_Table]![cboEmployeeId]));


SELECT [SumOfTotalHoursMinutes]/60 AS Hours_Worked
FROM [total minutes];

Thanks

Gaetanm

 

[Marshall Barton]

I"m trying to have a total hours form two Date/Time fields
that will come up as hh.nn total. I'm getting this error message

[quoted text clipped - 10 lines]

'You tried to execute a query that does not include the specified expression
"Startdate" as part of an aggregate function'

When you use an aggregate function, you need to use the
GROUP BY clause to specify the field(s) that are used to
group the aggregation. In this case the start and stop
dates are definitely not candidates for that purpose. For
example, if you want to sum the time spent on each project,
you would use:

SELECT projectID,
SUM(DateDiff("n", [Startdate],[StopDate]))/60 AS test
FROM Clock_Table
GROUP BY projectID

To get hh:nn from the total number of minutes (however
calculated), use this kind of expression:

TotalMin \ 60 & Format(TotalMin Mod 60, "\:00")

Hi Marshall And Duane

I tried what you suggested but to no avail. Its as if the sumfuction is not
executing. All I get is the DateDiff. Below I posted what you seggested.
Below that I hae two queries that get the result other that hh.nn ( i didn't
get there yet) this gives me my total time.
Question is there a way to make these two qweries into one?

SELECT Clock_Table.StartDate, Clock_Table.StopDate, SUM(DateDiff("n",
Startdate,StopDate))/60 AS test
FROM Clock_Table
GROUP by Clock_Table.StartDate, Clock_Table.StopDate;

SELECT Clock_table.EmployeeID, Clock_table.StartDate, Clock_table.StopDate,
DateDiff("n",[Startdate],[Stopdate])\60 & Format(DateDiff("n",[Startdate],
[Stopdate]) Mod 60,"\:00") AS SumOfTotalHoursMinutes
FROM Clock_table
WHERE (((Clock_table.StopDate)>Date()-Weekday(Date()+1)) AND ((Clock_table.
EmployeeID)=[Forms]![frmClock_Start_Table]![cboEmployeeId]));


SELECT [SumOfTotalHoursMinutes]/60 AS Hours_Worked
FROM [total minutes];

That combination of queries doesn't make sense. Then first
on can not do anything useful as I tried to explain earlier.
I don't understand the third query, unless it is based on
the first one, which won't work any way.

The second one is closer to what I think you want, but it is
not summing anything.

Let's try again:

SELECT EmployeeID,
Sum(DateDiff("n",[Startdate],[Stopdate])) As TotalMin,
FROM Clock_table
WHERE (StopDate > Date()-Weekday(Date()+1))
AND (EmployeeID =
[Forms]![frmClock_Start_Table]![cboEmployeeId])
GROUP BY EmployeeID

Then use a text box with an expression in the report to
display the formatted hours/minutes:

=TotalMin \ 60 & Format(TotalMin Mod 60,"\:00")