By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,693 Members | 1,822 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,693 IT Pros & Developers. It's quick & easy.

How to sum column times (hh:nn:ss) in a crosstab query

100+
P: 547
I use a crosstab query and it works fine except it does not sum the "racetime" columns in the hh:nn:ss column.
I attach 2 pics to show the crosstab query and the result if i use "max" instead of "sum" that i need to use.
If i use sum when totaling, it just shows an error - datatype mismatch in criteria expression. Been trying a solid day now.
please assist
Attached Images
File Type: jpg Crosstab query 1.jpg (28.9 KB, 141 views)
File Type: jpg CT query with max used in total column.jpg (36.6 KB, 153 views)
Jun 7 '14 #1

✓ answered by jimatqsi

You cannot sum times. Try summing minutes instead.
Expand|Select|Wrap|Line Numbers
  1. (Hour(thetime)*60)+Minute(thetime)
something like that.

Jim

Share this Question
Share on Google+
3 Replies


Expert 100+
P: 1,240
You cannot sum times. Try summing minutes instead.
Expand|Select|Wrap|Line Numbers
  1. (Hour(thetime)*60)+Minute(thetime)
something like that.

Jim
Jun 7 '14 #2

100+
P: 547
Jim that works 100% but to take it to seconds? It will be more accurate for me.
Jun 8 '14 #3

Expert 100+
P: 1,240
Just extend the same logic ...
Expand|Select|Wrap|Line Numbers
  1. (Hour(thetime)*60*60)+(Minute(thetime)*60)+Second(thetime)
Jun 8 '14 #4

Post your reply

Sign in to post your reply or Sign up for a free account.