Total short time fields in query

Ecohouse

I'm working on a project that was dropped in my lap. It is in Access
2003. There are short time fields which get calculated values (eg.
1:30). But I will need to total up those values for reports in
qiueries. When I tried to drop one of the fields in the query builder
and used the total function the totals was way off.

So I was wondering what would be the best way to do this? Thanks in
advance for the help.

Jun 27 '08 #1

KC-Mass

I think generally that you do not want to total times. What would the total
of 18:00 and 14:30 and 4:00 mean or be useful for? Perhaps the result of
subtracting one time from another, yielding elapsed times can be added
usefully?

Regards

Kevin

"Ecohouse" <ec*********@yahoo.comwrote in message
news:34**********************************@k37g2000 hsf.googlegroups.com...

I'm working on a project that was dropped in my lap. It is in Access
2003. There are short time fields which get calculated values (eg.
1:30). But I will need to total up those values for reports in
qiueries. When I tried to drop one of the fields in the query builder
and used the total function the totals was way off.

So I was wondering what would be the best way to do this? Thanks in
advance for the help.

Jun 27 '08 #2

Ecohouse

I normally don't do it this way either. In the end they want reports
that will show totals and that is what I'm working on. I'm trying to
figure out the best way to deal with this issue.

Any other ideas?

On May 4, 6:01*pm, "KC-Mass" <connearneyATcomcastDOTnetwrote:

I think generally that you do not want to total times. *What would the total
of 18:00 and 14:30 and 4:00 mean or be useful for? *Perhaps the result of
subtracting one time from another, yielding elapsed times can be added
usefully?

Regards

Kevin

"Ecohouse" <ecovinda...@yahoo.comwrote in message

news:34**********************************@k37g2000 hsf.googlegroups.com...

I'm working on a project that was dropped in my lap. *It is in Access
2003. *There are short time fields which get calculated values (eg.
1:30). *But I will need to total up those values for reports in
qiueries. *When I tried to drop one of the fields in the query builder
and used the total function the totals was way off.

So I was wondering what would be the best way to do this? *Thanks in
advance for the help.- Hide quoted text -

- Show quoted text -

Jun 27 '08 #3

Fred Zuckerman

"Ecohouse" <ec*********@yahoo.comwrote in message
news:34**********************************@k37g2000 hsf.googlegroups.com...

I'm working on a project that was dropped in my lap. It is in Access
2003. There are short time fields which get calculated values (eg.
1:30). But I will need to total up those values for reports in
qiueries. When I tried to drop one of the fields in the query builder
and used the total function the totals was way off.

So I was wondering what would be the best way to do this? Thanks in
advance for the help.

I'm confused, when you say "there are short time fields" do you mean a field
containing values like 3:30pm, 4:00pm, 12:14am, etc? Or do you mean a field
containing values of elapsed time like 1.20 secs, 5.50 hrs, etc. The former
is formatted as date/time, the latter is a number. A summation of the former
doesn't make sense, but the summation of the latter does.
Fred Zuckerman

Jun 27 '08 #4

Ecohouse

A field would have the value of 1:30. This would represent one hour
and thirty minutes. I didn't set this up and now I'm stuck with it
and trying to figure out a way to deal with this.

So I have to create reports where I will be grouping departments and I
need the total hours and minutes. I'm trying to do this using the
query builder. Any ideas?

On May 5, 4:25*am, "Fred Zuckerman" <Zuckerm...@sbcglobal.netwrote:

"Ecohouse" <ecovinda...@yahoo.comwrote in message

news:34**********************************@k37g2000 hsf.googlegroups.com...

I'm working on a project that was dropped in my lap. *It is in Access
2003. *There are short time fields which get calculated values (eg.
1:30). *But I will need to total up those values for reports in
qiueries. *When I tried to drop one of the fields in the query builder
and used the total function the totals was way off.

So I was wondering what would be the best way to do this? *Thanks in
advance for the help.

I'm confused, when you say "there are short time fields" do you mean a field
containing values like 3:30pm, 4:00pm, 12:14am, etc? Or do you mean a field
containing values of elapsed time like 1.20 secs, 5.50 hrs, etc. The former
is formatted as date/time, the latter is a number. A summation of the former
doesn't make sense, but the summation of the latter does.
Fred Zuckerman

Jun 27 '08 #5

KC-Mass

I think the basic problem is that your predecessor used the wrong data type.
The data should have been stored as a number.

Try converting it to a number in a query. Put an Expression in the query
that
multiplies the timefield by 1440 (giving minutes) and divide that by 60
(giving hours).

A "Time" of 3:30 will give you 3.50. You can the sum those values.

The SQL would be something like:
"SELECT Sum([timeforprocess]*1440/60) AS NumbersFROM tblTime; "

I am guessing this is what you need.

Regards
Kevin


"Ecohouse" <ec*********@yahoo.comwrote in message
news:34**********************************@k37g2000 hsf.googlegroups.com...

I'm working on a project that was dropped in my lap. It is in Access
2003. There are short time fields which get calculated values (eg.
1:30). But I will need to total up those values for reports in
qiueries. When I tried to drop one of the fields in the query builder
and used the total function the totals was way off.

So I was wondering what would be the best way to do this? Thanks in
advance for the help.

Jun 27 '08 #6

Bob Quintal

Ecohouse <ec*********@yahoo.comwrote in
news:3d824645-d110-47a3-958d-170c4afa394a@
24g2000hsh.googlegroups.com
:

A field would have the value of 1:30. This would represent one
hour and thirty minutes. I didn't set this up and now I'm stuck
with it and trying to figure out a way to deal with this.

So I have to create reports where I will be grouping departments
and I need the total hours and minutes. I'm trying to do this
using the query builder. Any ideas?

So you are talking of durations and not times. Access stores the
date and time as the number of dats since Dec 31, 1899 and the
fraction of the day reresents the time. e.g. noon is 0,5, 6:00 pm is
0.75.

So first you need to determine if there is a hidden date in the
field, then subtract that from the shown value, and total the
fractions, then redisplay as as a duration.

If instead the time is stored in a text field, not date-time, you
will need to write a function in Visual basic code to split the
parts into a fraction of a day, then sum and reformat as a duration.

until anyone can give you specific instructions, we'll need to know
1) the field type (open the table in design mode),
2) the field format,
3) if a datetime field, whether the dates are all 12-31-1899 or
various recent dates, build a query and format the field as general
date.

Q

On May 5, 4:25*am, "Fred Zuckerman" <Zuckerm...@sbcglobal.net>
wrote:

>"Ecohouse" <ecovinda...@yahoo.comwrote in message

news:3476a795-6e48-4aad-8f80-

c3**********@k37g2000hsf.googlegroups

>.com...

I'm working on a project that was dropped in my lap. *It is in
Access 2003. *There are short time fields which get calculated
values (eg. 1:30). *But I will need to total up those values
for reports in qiueries. *When I tried to drop one of the
fields in the query builder

and used the total function the totals was way off.

So I was wondering what would be the best way to do this?
*Thanks in advance for the help.

I'm confused, when you say "there are short time fields" do you
mean a fie

ld

>containing values like 3:30pm, 4:00pm, 12:14am, etc? Or do you
mean a fiel

d

>containing values of elapsed time like 1.20 secs, 5.50 hrs, etc.
The forme

r

>is formatted as date/time, the latter is a number. A summation of
the form

er

>doesn't make sense, but the summation of the latter does.
Fred Zuckerman

--
Bob Quintal

PA is y I've altered my email address.
** Posted from http://www.teranews.com **

Jun 27 '08 #7

Ecohouse

I was able to search around on the internet and find out what I
needed. Thanks for all the help.

On May 5, 5:57*pm, Bob Quintal <rquin...@sPAmpatico.cawrote:

Ecohouse <ecovinda...@yahoo.comwrote in
news:3d824645-d110-47a3-958d-170c4afa394a@
24g2000hsh.googlegroups.com
:

A field would have the value of 1:30. *This would represent one
hour and thirty minutes. *I didn't set this up and now I'm stuck
with it and trying to figure out a way to deal with this.

So I have to create reports where I will be grouping departments
and I need the total hours and minutes. *I'm trying to do this
using the query builder. *Any ideas?

So you are talking of durations and not times. Access stores the
date and time as the number of dats since Dec 31, 1899 and the
fraction of the day reresents the time. e.g. noon is 0,5, 6:00 pm is
0.75.

So first you need to determine if there is a hidden date in the
field, then subtract that from the shown value, and total the
fractions, then redisplay as as a duration.

If instead the time is stored in a text field, not date-time, you
will need to write a function in Visual basic code to split the
parts into a fraction of a day, then sum and reformat as a duration.

until anyone can give you specific instructions, we'll need to know
1) the field type (open the table in design mode),
2) the field format,
3) if a datetime field, whether the dates are all 12-31-1899 or
various recent dates, build a query and format the field as general
date.

Q

On May 5, 4:25*am, "Fred Zuckerman" <Zuckerm...@sbcglobal.net>
wrote:

"Ecohouse" <ecovinda...@yahoo.comwrote in message

news:3476a795-6e48-4aad-8f80-

c34a494b6...@k37g2000hsf.googlegroups

.com...

I'm working on a project that was dropped in my lap. *It is in
Access 2003. *There are short time fields which get calculated
values (eg. 1:30). *But I will need to total up those values
for reports in qiueries. *When I tried to drop one of the
fields in the query builder

and used the total function the totals was way off.

So I was wondering what would be the best way to do this?
*Thanks in advance for the help.

I'm confused, when you say "there are short time fields" do you
mean a fie

ld

containing values like 3:30pm, 4:00pm, 12:14am, etc? Or do you
mean a fiel

d

containing values of elapsed time like 1.20 secs, 5.50 hrs, etc.
The forme

r

is formatted as date/time, the latter is a number. A summation of
the form

er

doesn't make sense, but the summation of the latter does.
Fred Zuckerman

--
Bob Quintal

PA is y I've altered my email address.
** Posted fromhttp://www.teranews.com**- Hide quoted text -

- Show quoted text -

Jun 27 '08 #8