How to do effective Sum function in VBA coding 1

[Brianus]

I am trying to perform a sum on a field in my table.
Table layout:
=============
Projid monhrs tueshrs wedhrs thurhrs frihrs totalhrs
------ ------ ------- ------ ------- ------ --------
05528 5 10 1 2 3
05528 1 2 3 0 5
11547 2 5 10 2 6
----------------------------------------------------

This is my code:
================
Set rsin = dbin.OpenRecordset("SELECT Projid, " _
& monhrs, tueshrs, wedhrs, thurhrs, frihrs, " _
From Timesheettbl " _
& "WHERE IsNull(Transferstatus) "_
& "GROUP BY Projid ;")

Since the projid has multiple entries of the same projid, my objective is to be able to sum the tothrs field in the table, grouped by the projid field. Please how can I get this accomplished?
I want to do the sum during execution, not in the table.

Totalhrs = Sum(rsin![Monhrs] + rsin![Tueshrs] + rsin![Wedhrs] + rsin![Thurhrs] + rsin![Frihrs])

The above sum is not working for me.

Any help will be appreciated.
Brianus.

 

[Golom]

Set rsin = dbin.OpenRecordset( _
"SELECT Projid " & _  
"     , SUM(monhrs)  As [Mon Hours] " & _
"     , SUM(tueshrs) As [Tue Hours] " & _
"     , SUM(wedhrs)  As [Wed Hours] " & _
"     , SUM(thurhrs) As [Thu Hours] " & _
"     , SUM(frihrs)  As [Fri Hours] " & _
"     , (SUM(monhrs) + SUM(tueshrs) + SUM(wedhrs) + " _
"        SUM(thurhrs) + SUM(frihrs)) As [Total Hours] " & _
From Timesheettbl " _
& "WHERE IsNull(Transferstatus) "_
& "GROUP BY Projid ;")

 

[SkipVought]

Hi,

first, what a horrible Non-Normalized table design.

second, you are correct by wanting the aggregation to be performed at run-time and not stored in the table, BUT TotalHrs should NOT be a field in your table

("SELECT Projid, " _  
& SUM(monhrs+tueshrs+wedhrs+thurhrs+frihrs) As TotHrs " _
From Timesheettbl " _
& "WHERE IsNull(Transferstatus) "_
& "GROUP BY Projid ;"

Skip,

_[red][/red]