Difference between sum and groupby in query time

[trevorC via AccessMonster.com]

Can you help with this one?

SUM fails - Data type mismatch in criteria expretion

SELECT [Time Keeping Base Report].[First Name], [Time Keeping Base Report].
[Last Name], Sum([Time Keeping Base Report].[Total Hours]) AS [SumOfTotal
Hours]
FROM [Time Keeping Base Report]
GROUP BY [Time Keeping Base Report].[First Name], [Time Keeping Base Report].
[Last Name];

works ok
SELECT [Time Keeping Base Report].[First Name], [Time Keeping Base Report].
[Last Name], [Time Keeping Base Report].[Total Hours]
FROM [Time Keeping Base Report]
GROUP BY [Time Keeping Base Report].[First Name], [Time Keeping Base Report].
[Last Name], [Time Keeping Base Report].[Total Hours];

 

[kc-mass]

Hi Trevor

My guess is that the field [Total Hours] is storing the numeric expression
of the hours as a string.

Check the table specs under design mode.

Also try:

SELECT [Time Keeping Base Report].[First Name], [Time Keeping Base Report].
[Last Name], Sum(CDbl([Time Keeping Base Report].[Total Hours])) AS
[SumOfTotal
Hours]
FROM [Time Keeping Base Report]
GROUP BY [Time Keeping Base Report].[First Name], [Time Keeping Base
Report].
[Last Name];

Also since in typical data sets there is far less repetition of last names
than first names, the group by should first group on last name.

Regards

Kevin

 

[Duane Hookom]

Perhaps you should provide the data type of [Total Hours]. When you view this
in datasheet, it the column left or right-aligned?

Is [Time Keeping Base Report] a table or query? I can't tell from the name.

 

[John Spencer]

Your earlier posting tells me that Total Hours is a TEXT field (column). You
need to the TOTAL Minutes field that you are generating in the base query.

SUM([Total Minutes])\60 as TotalHours
SUM([Total Minutes]) Mod 60 as RemainingMinutes

IF you wanted that all as hours and minutes in a string you could use the
following expression to generate something like "69:12" - a string indicating
69 hours and 12 minutes.

SUM([Total Minutes])\60 & Format(SUM([Total Minutes]) Mod 60,"\:00")


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County