Sum(DateDiff

  • Thread starter Gaetanm via AccessMonster.com
  • Start date
G

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
 
D

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;
 
M

Marshall Barton

Gaetanm said:
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")
 
G

Gaetanm via AccessMonster.com

Marshall said:
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
 
M

Marshall Barton

Gaetanm said:
Marshall said:
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")
 

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

Data Type Mismatch in Criteria expression 6
Cartesian Help Please 5
Weekday 0
Query and time 0
Unique value in query 12
Dlookup fails with 2 compo boxes 2
Percentages on time calculations 3
MOD 60 8

Top