Time and Calculated Query - Help

  • Thread starter Thread starter jj
  • Start date Start date
J

jj

Hi,

I've got a table that tells me how long I've been on the phone. I want to
have some output (a query? a report?) which lists each person I called only
once, and gives me a total of the amount of time that I'm on the phone with
them.

Here's the definition

Name: 10 Chars Text
Length: Date/Time



Here are some sample records:

Name Length

John 1:20:20
John 0:45:45
Mike 0:03:03


The output that I want is the following:

John 2:06:05
Mike 0:03:03

I've tried a calculated query, but the sum function comes up with nonsense.

Any suggestions would be appreciated.

Thanks,

JJ
 
Check Access Help on Totals Query.

The SQL String should be something like:

SELECT [Name], Sum([Length]) As SumOfTime
FROM [Your Table]
GROUP BY [Name]

(assuming the "date" component of your Field "Length" is zero.)

"Name" is an inbuilt Property for (virtually) every object in Access. Using
"Name" as a Field name will be very confusing your database later. Suggest
you change the Field name. In fact, I wouldn't use "Length" either.

HTH
Van T. Dinh
MVP (Access)
 
Hi,

I had something like that originally. The problem is that John doesn't end
up with 2:06:05,
instead he ends up with 0.0875578703703704

So, I get that the sum is expressed as a fraction of a day, but how do I go
about changing it back into
hours, minutes and seconds?

Thanks and Regards,

-Rob



Van T. Dinh said:
Check Access Help on Totals Query.

The SQL String should be something like:

SELECT [Name], Sum([Length]) As SumOfTime
FROM [Your Table]
GROUP BY [Name]

(assuming the "date" component of your Field "Length" is zero.)

"Name" is an inbuilt Property for (virtually) every object in Access. Using
"Name" as a Field name will be very confusing your database later. Suggest
you change the Field name. In fact, I wouldn't use "Length" either.

HTH
Van T. Dinh
MVP (Access)



jj said:
Hi,

I've got a table that tells me how long I've been on the phone. I want to
have some output (a query? a report?) which lists each person I called only
once, and gives me a total of the amount of time that I'm on the phone with
them.

Here's the definition

Name: 10 Chars Text
Length: Date/Time



Here are some sample records:

Name Length

John 1:20:20
John 0:45:45
Mike 0:03:03


The output that I want is the following:

John 2:06:05
Mike 0:03:03

I've tried a calculated query, but the sum function comes up with nonsense.

Any suggestions would be appreciated.

Thanks,

JJ
Click to expand...
Click to expand...
 
You can format it as hh:nn:ss

?Format(0.0875578703703704, "hh:nn:ss")
02:06:05

Alternatively, multiply it by 86400 (the number of seconds in a day) to get
a total in seconds:

?0.0875578703703704 * 86400&
7565


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



jj said:
Hi,

I had something like that originally. The problem is that John doesn't end
up with 2:06:05,
instead he ends up with 0.0875578703703704

So, I get that the sum is expressed as a fraction of a day, but how do I go
about changing it back into
hours, minutes and seconds?

Thanks and Regards,

-Rob



Van T. Dinh said:
Check Access Help on Totals Query.

The SQL String should be something like:

SELECT [Name], Sum([Length]) As SumOfTime
FROM [Your Table]
GROUP BY [Name]

(assuming the "date" component of your Field "Length" is zero.)

"Name" is an inbuilt Property for (virtually) every object in Access. Using
"Name" as a Field name will be very confusing your database later. Suggest
you change the Field name. In fact, I wouldn't use "Length" either.

HTH
Van T. Dinh
MVP (Access)



jj said:
Hi,

I've got a table that tells me how long I've been on the phone. I want to
have some output (a query? a report?) which lists each person I called only
once, and gives me a total of the amount of time that I'm on the phone with
them.

Here's the definition

Name: 10 Chars Text
Length: Date/Time



Here are some sample records:

Name Length

John 1:20:20
John 0:45:45
Mike 0:03:03


The output that I want is the following:

John 2:06:05
Mike 0:03:03

I've tried a calculated query, but the sum function comes up with nonsense.

Any suggestions would be appreciated.

Thanks,

JJ
Click to expand...
Click to expand...
Click to expand...
 
hi,

what you are trying to do will always return a Junk value.
Instead convert the time to Minutes format..and then
divide it by 60.... using mid function check for the
minutes part and if it is great than 0.60, then multiple
it by 60. this will return you the HH:MM format.

TI
 
Hi,

Okay, I like the first answer better, but... what's the ? about before
Format?

And where do I put that? In the query or does it only work on a report?

Thanks again & sorry for the dumb question!

-JJ


Douglas J. Steele said:
You can format it as hh:nn:ss

?Format(0.0875578703703704, "hh:nn:ss")
02:06:05

Alternatively, multiply it by 86400 (the number of seconds in a day) to get
a total in seconds:

?0.0875578703703704 * 86400&
7565


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



jj said:
Hi,

I had something like that originally. The problem is that John doesn't end
up with 2:06:05,
instead he ends up with 0.0875578703703704

So, I get that the sum is expressed as a fraction of a day, but how do I go
about changing it back into
hours, minutes and seconds?

Thanks and Regards,

-Rob



Van T. Dinh said:
Check Access Help on Totals Query.

The SQL String should be something like:

SELECT [Name], Sum([Length]) As SumOfTime
FROM [Your Table]
GROUP BY [Name]

(assuming the "date" component of your Field "Length" is zero.)

"Name" is an inbuilt Property for (virtually) every object in Access. Using
"Name" as a Field name will be very confusing your database later. Suggest
you change the Field name. In fact, I wouldn't use "Length" either.

HTH
Van T. Dinh
MVP (Access)



Hi,

I've got a table that tells me how long I've been on the phone. I
Click to expand...
want
to
have some output (a query? a report?) which lists each person I called
only
once, and gives me a total of the amount of time that I'm on the phone
with
them.

Here's the definition

Name: 10 Chars Text
Length: Date/Time



Here are some sample records:

Name Length

John 1:20:20
John 0:45:45
Mike 0:03:03


The output that I want is the following:

John 2:06:05
Mike 0:03:03

I've tried a calculated query, but the sum function comes up with
nonsense.

Any suggestions would be appreciated.

Thanks,

JJ
Click to expand...
Click to expand...
Click to expand...
 
Sorry: The ? is because I was typing that into the Immediate Window, just to
show what value would be returned.

You're probably best off simply setting the Format property of whatever
textbox control you're using on the report.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



jj said:
Hi,

Okay, I like the first answer better, but... what's the ? about before
Format?

And where do I put that? In the query or does it only work on a report?

Thanks again & sorry for the dumb question!

-JJ


Douglas J. Steele said:
You can format it as hh:nn:ss

?Format(0.0875578703703704, "hh:nn:ss")
02:06:05

Alternatively, multiply it by 86400 (the number of seconds in a day) to get
a total in seconds:

?0.0875578703703704 * 86400&
7565


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



jj said:
Hi,

I had something like that originally. The problem is that John doesn't end
up with 2:06:05,
instead he ends up with 0.0875578703703704

So, I get that the sum is expressed as a fraction of a day, but how do
Click to expand...
I
go
about changing it back into
hours, minutes and seconds?

Thanks and Regards,

-Rob



Check Access Help on Totals Query.

The SQL String should be something like:

SELECT [Name], Sum([Length]) As SumOfTime
FROM [Your Table]
GROUP BY [Name]

(assuming the "date" component of your Field "Length" is zero.)

"Name" is an inbuilt Property for (virtually) every object in Access.
Using
"Name" as a Field name will be very confusing your database later.
Suggest
you change the Field name. In fact, I wouldn't use "Length" either.

HTH
Van T. Dinh
MVP (Access)



Hi,

I've got a table that tells me how long I've been on the phone. I want
to
have some output (a query? a report?) which lists each person I called
only
once, and gives me a total of the amount of time that I'm on the phone
with
them.

Here's the definition

Name: 10 Chars Text
Length: Date/Time



Here are some sample records:

Name Length

John 1:20:20
John 0:45:45
Mike 0:03:03


The output that I want is the following:

John 2:06:05
Mike 0:03:03

I've tried a calculated query, but the sum function comes up with
nonsense.

Any suggestions would be appreciated.

Thanks,

JJ
Click to expand...
Click to expand...
Click to expand...
 
Back
Top