count() function works but not sum()

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.

All Forums

Other Forums

MS Access

count() function works but not sum()

Author

Topic

Gabo
Starting Member

1 Post

Posted - 2009-05-15 : 15:09:04

I'm working with a linked table in MS ACCESS 2003. It has three fields: InterviewerID, LogOn, LogOff. The data for LogOn and LogOff are in the format: MM/DD/YYYY HH:MM:SS AM/PM

My first query, which I called LogIntTime is:

SEL InterviewerID, (LogOff-LogOn)*24 AS HRS
FROM DBALOG;

I multiplied the difference by 24 so that my reults are in decimal format, which is what I want. The query produces something like this:

InterviewerID | HRS
365 | 2.4302021301
365 | 1.1833333123
425 | 1.5036123123
425 | 2.2515653892
121 | 1.1231562378
250 | 2.0251564898

I then created a second query to figure out the sum for HRS, like this:

SEL SUM(HRS)
FROM LogIntTime;

But I keep getting a result of 0. If I do a COUNT(HRS) I get the correct count. I also get a result of 0 if I do a MAX(HRS) or MIN(HRS).

Is this a formatting issue? Why does COUNT() work but not SUM()?

Thanks!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-24 : 04:27:06

The HRS columns does not exists in the table?

E 12°55'05.63"
N 56°04'39.26"

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-05-24 : 07:50:56

I'm not an access guy so I don't know why your count, min, and max works but the sum doesn't. But I could suggest trying to change your first query to a create table query and query the new table rather than query your first query. at least it would be interesting to see what the resulting datatype is for your expression. Alternatively maybe this would work all in one query: max((LogOff-LogOn)*24)

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources