> I've built an application for a Fire/Rescue service to log their calls,
> meeting attendance, etc. (geared to small, volunteer agencies). One of the
> things that's very important for them is a sum of the hours spent
> on calls.
> Each call record has a uniquely generated call number that is the year
> followed by a hyphen and a three digit incrementing number. Each
> record also has times for various events from dispatch to return to
> quarters. The times are in short time (17:34) format. It's an Access
> database, it's all they can afford/support right now.
>
> I need to get a sum of the difference between dispatch time and return to
> quarters time for all calls in a given year.
>
> I've tried what seems to me to be the obvious:
>
> SELECT SUM(DateDiff("h",DISPATCH_TIME,RTN_QTRS)) AS SUMHRS
> FROM t_Calls
> WHERE CALL_NO LIKE '2001-*-';
>
> But that hasn't worked. I've played around with a number of other options
> but haven't gotten anything to work. I'd appreciate any help
> anyone here can provide.
OK, since you're using an aggrigate function (SUM), don't use WHERE - use
HAVING
Give that a try - it should work...
Also, your LIKE assumes that CALL_NO ends with a "-", does it? If no, get
rid of the last one
Philip Arnold
Director
Certified ColdFusion Developer
ASP Multimedia Limited
T: +44 (0)20 8680 1133
"Websites for the real world"
**********************************************************************
This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom they
are addressed. If you have received this email in error please notify
the system manager.
**********************************************************************
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
