473,503 Members | 2,166 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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

547 Contributor
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, 159 views)
File Type: jpg CT query with max used in total column.jpg (36.6 KB, 176 views)
Jun 7 '14 #1
3 1441
jimatqsi
1,271 Recognized Expert Top Contributor
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
neelsfer
547 Contributor
Jim that works 100% but to take it to seconds? It will be more accurate for me.
Jun 8 '14 #3
jimatqsi
1,271 Recognized Expert Top Contributor
Just extend the same logic ...
Expand|Select|Wrap|Line Numbers
  1. (Hour(thetime)*60*60)+(Minute(thetime)*60)+Second(thetime)
Jun 8 '14 #4

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

Similar topics

8
7538
by: Donna Sabol | last post by:
First, I should start by saying I am creating a database to be used by some very impatient, non-computer literate people. It needs to be seameless in it's operation from their point of view. I...
1
10653
by: krish_4u_only | last post by:
Hi, I have designed a subreport - record source is on crosstab query. crosstab query is designed on union query. The sub report is working fine when it is opened individually. but if i place it on...
3
9221
by: Lee | last post by:
I have a crosstab query that lists the number of patients(DECnum)by County. County is my Row Heading. Currently, the Column heading is Age in months, which is set at one month intervals. I would...
4
8088
by: Judy | last post by:
I'm using Access 2003 and was wondering if it is possible to have a paramater selection within a crosstab query so that I wouldn't need to build a new table. I have a select query that I'm using...
2
5945
by: Claus Haslauer | last post by:
Hi, I want to create a crosstab query that looks like this Date | Elevation 1 | Elevation 2 | ... ______________________________________________________________________ Date 1 | xx.y | xx.y...
2
2923
by: Nenad Markovic | last post by:
Hi everybody, When executing a Crosstab Query I see only rows (defined in a row heading) that have values (defined in value field) in at least one column (defined as column headings). How can...
4
6458
by: m.wanstall | last post by:
I have a crosstab query that compiles data for Months of the year. I have a stacked select query on top of that crosstab query that uses the latest 2 months data and exports it to a fixed length...
1
1780
by: juls | last post by:
Hi experts: I want to seek an advice from all Access experts to give me an idea or sample program in vba or how to make crosstab query that can be printed in the report of which the outfield may...
2
5615
by: hwalker | last post by:
Hello all. Long time reader, first time poster :) I'm creating a repot that shows "the last two weeks of data the next 6 weeks of data, week over week". So, I have a calculated field called...
8
5898
by: Paul H | last post by:
I want to base a form on a crosstab query. The query shows statistics for a user defined period. The column headings will look something like this: ClientID Month01 Month02 Month03 etc.. ...
0
7205
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
7093
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
1
7008
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
0
7467
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
0
5594
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
0
4688
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and...
0
3177
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The...
0
1521
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated ...
0
399
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.