Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
connelvalentine
Starting Member
2 Posts |
 Posted - 2003-12-09 : 04:48:50
|
| Hi,I'm having a problem with a recordset. I want to perform this calculation:Sum[Normal Hours] / Sum[Available Hours]group by Month([Job Date])The problem is, the sum(Normal Hours) is WHERE the [Job Type]='E', but the sum([Available Hours]) is the sum of the whole month, regardless of job type.i tried using Dsum([Normal Hours]), setting a criteria to the Type of Job, but Dsum doesn't take into account GROUP BY. Its sums up the entire year, and gives the same value for every month.Can some one please help me!!!!!!Thanks! |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-12-09 : 05:26:50
|
| Don't know if you can do this in access but trySum(iif([Job Type]='E',[Normal Hours],0)) / Sum([Available Hours])group by Month([Job Date])==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2003-12-09 : 08:39:59
|
| what Nigel posted should work fine; watch out for division by 0 errors, though .... can Sum(AvailableHours) ever be 0 for a month?- Jeff |
|
|
|
connelvalentine
Starting Member
2 Posts |
Posted - 2003-12-10 : 00:23:05
|
| Yes it can,What's the best way to tackle division by 0 errors? |
|
|
|
|
|
|
GroupBy and DSUM