Access query and Sum Total of Time in Decimal. How to change to Time Format?

[pedie]

Please help me achieve this.
When i run query i get Total Time in Decimals... HOw can i get it in Time Format as in HH:MM:SS.

Thanks in advance.

[/FONT]
[FONT=Courier New]SELECT Table1.[Login Date], Sum(Table1.[Total Time]) AS [SumOfTotal Time]
FROM Table1
GROUP BY Table1.[Login Date];

Current:
<TABLE cellSpacing=0 bgColor=#ffffff border=1><CAPTION>Query1</CAPTION><THEAD><TR><TH borderColor=#000000 bgColor=#c0c0c0>Login Date</TH><TH borderColor=#000000 bgColor=#c0c0c0>SumOfTotal Time</TH></TR></THEAD><TBODY><TR vAlign=top><TD borderColor=#d0d7e5>10Jan12</TD><TD borderColor=#d0d7e5 align=right>1.04166666666667E-03</TD></TR><TR vAlign=top><TD borderColor=#d0d7e5>30Dec11</TD><TD borderColor=#d0d7e5 align=right>0.337465277777778</TD></TR><TR vAlign=top><TD borderColor=#d0d7e5>31Dec11</TD><TD borderColor=#d0d7e5 align=right>4.93287037037037E-02</TD></TR></TBODY><TFOOT></TFOOT></TABLE>

Result Expected:
<TABLE style="WIDTH: 109pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=145 border=0><COLGROUP><COL style="WIDTH: 42pt; mso-width-source: userset; mso-width-alt: 2048" width=56><COL style="WIDTH: 67pt; mso-width-source: userset; mso-width-alt: 3254" width=89><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl64 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; WIDTH: 42pt; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: silver" width=56 height=17>Login Date</TD><TD class=xl66 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black; WIDTH: 67pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: silver" width=89>SumOfTotal Time</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl65 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver 0.5pt solid; BORDER-LEFT: silver 0.5pt solid; WIDTH: 42pt; BORDER-BOTTOM: silver 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=56 height=17>10Jan12</TD><TD class=xl67 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver 0.5pt solid; BORDER-LEFT: silver; WIDTH: 67pt; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent" width=89>0:01:30</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl65 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 0.5pt solid; WIDTH: 42pt; BORDER-BOTTOM: silver 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=56 height=17>30Dec11</TD><TD class=xl67 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; WIDTH: 67pt; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent" width=89>8:05:57</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl65 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 0.5pt solid; WIDTH: 42pt; BORDER-BOTTOM: silver 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=56 height=17>31Dec11</TD><TD class=xl67 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; WIDTH: 67pt; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent" width=89>1:11:02</TD></TR></TBODY></TABLE>

 

[gsrikanth]

Please help me achieve this.
When i run query i get Total Time in Decimals... HOw can i get it in Time Format as in HH:MM:SS.

Thanks in advance.

[FONT=Courier New]SELECT Table1.[Login Date], Sum(Table1.[Total Time]) AS [SumOfTotal Time]
FROM Table1
GROUP BY Table1.[Login Date];
[/FONT]

Current:
<table bgcolor="#ffffff" border="1" cellspacing="0"><caption>Query1</caption><thead><tr><th bordercolor="#000000" bgcolor="#c0c0c0">Login Date</th><th bordercolor="#000000" bgcolor="#c0c0c0">SumOfTotal Time</th></tr></thead><tbody><tr valign="top"><td bordercolor="#d0d7e5">10Jan12</td><td bordercolor="#d0d7e5" align="right">1.04166666666667E-03</td></tr><tr valign="top"><td bordercolor="#d0d7e5">30Dec11</td><td bordercolor="#d0d7e5" align="right">0.337465277777778</td></tr><tr valign="top"><td bordercolor="#d0d7e5">31Dec11</td><td bordercolor="#d0d7e5" align="right">4.93287037037037E-02</td></tr></tbody><tfoot></tfoot></table>

Result Expected:
<table style="WIDTH: 109pt; BORDER-COLLAPSE: collapse" border="0" cellpadding="0" cellspacing="0" width="145"><colgroup></colgroup><colgroup><col style="WIDTH: 42pt; mso-width-source: userset; mso-width-alt: 2048" width="56"><col style="WIDTH: 67pt; mso-width-source: userset; mso-width-alt: 3254" width="89"></colgroup><tbody><tr style="HEIGHT: 12.75pt" height="17"><td class="xl64" style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; WIDTH: 42pt; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: silver" height="17" width="56">Login Date</td><td class="xl66" style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black; WIDTH: 67pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: silver" width="89">SumOfTotal Time</td></tr><tr style="HEIGHT: 12.75pt" height="17"><td class="xl65" style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver 0.5pt solid; BORDER-LEFT: silver 0.5pt solid; WIDTH: 42pt; BORDER-BOTTOM: silver 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height="17" width="56">10Jan12</td><td class="xl67" style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver 0.5pt solid; BORDER-LEFT: silver; WIDTH: 67pt; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent" width="89">0:01:30</td></tr><tr style="HEIGHT: 12.75pt" height="17"><td class="xl65" style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 0.5pt solid; WIDTH: 42pt; BORDER-BOTTOM: silver 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height="17" width="56">30Dec11</td><td class="xl67" style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; WIDTH: 67pt; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent" width="89">8:05:57</td></tr><tr style="HEIGHT: 12.75pt" height="17"><td class="xl65" style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 0.5pt solid; WIDTH: 42pt; BORDER-BOTTOM: silver 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height="17" width="56">31Dec11</td><td class="xl67" style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; WIDTH: 67pt; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent" width="89">1:11:02</td></tr></tbody></table>

decimal part multiple 4 becasue 1 hr 60 min
100 60 = 1.66667
100 40 = 2.5
100 20 = 5
100 50 = 2

 

[pedie]

gsrikanth, thanks for quick answer....
but i am getting it...

decimal part multiple 4 becasue 1 hr 60 min

100 60 = 1.66667
100 40 = 2.5
100 20 = 5
100 50 = 2

 

[gsrikanth]

gsrikanth, thanks for quick answer....
but i am getting it...

how in which format is that

 

[Norie]

Use Format.

SELECT Table1.[Login Date], Format(Sum(Table1.[Total Time]), "HH:MM:SS") AS [SumOfTotal Time]
FROM Table1
GROUP BY Table1.[Login Date];

 

[pedie]

Norie, thank you....works perfect!
I didnt know we cant enter a format in the query..

 

[james_lankford]

I don't know if what you're doing is a good idea.
What if the total time is greater than 24 hours ?
I don't know what will happen, but its something you should check.

I think decimal format is a better way to go

 

[pedie]

Thats is correct and thanks for pointing that out. it only works till 24...but for now it wont exceed 24..

 

[pedie]

I don't know if what you're doing is a good idea.

What if the total time is greater than 24 hours ?
I don't know what will happen, but its something you should check.

I think decimal format is a better way to go

For now i have come up with one solution to save the time difference in mins and then show/format in hh:mm just for displaying purposes....
Is there better idea around it?

 

[gsrikanth]

For now i have come up with one solution to save the time difference in mins and then show/format in hh:mm just for displaying purposes....
Is there better idea around it?

excel data is importing in msaccess
external data + excel
when 1 row data inserted in the database that time i get in the format hh:mm:ss.000.
second consist of 1000 number of nano second
if one record is inserted it will tag 8:25:05.100
and then second record it will fallen time like 8:25:05.102 like so all all the record get time. is it possible. suggest me