Query Help

[dmarsh61]

I have a monthly report that I receive in Excel. It is a cellular phone bill that contains a list of every call, text and data activity on phones. I have imported this into Access and want to create a query where I can get a list of phone numbers with the number of minutes that they have been on the phone between a range of times. (Say 08:00 and 17:00). The problem that I am having is that the duration of the call are in text format that shows "mm:ss". How can I convert the text field that appears as hh:ss to something so that I can get a total?
Thanks

 

[jdraw]

You could add up the minutes and seconds.

minutes/60 === hours
seconds/60 === minutes

 

[plog]

...the duration of the call are in text format that shows "mm:ss". How can I convert the text field that appears as hh:ss to something so that I can get a total?

If the data is always in that format, you can use the Mid function (https://www.techonthenet.com/access/functions/string/mid.php) to extract the minute characters, multiple that by 60 and then use the Mid function to add the seconds to it. Then do the appropriate math to get the data into your hh:ss format (which seems an odd format-hopefully a typo).

Granted of course, this doesn't really help with what you initially said you wanted ("want to create a query where I can get a list of phone numbers with the number of minutes that they have been on the phone between a range of times"). That's going to involve storing the Date/Time of when a call ended/started and the DateDiff function (https://www.techonthenet.com/access/functions/date/datediff.php) inside some custom VBA probably.

 

[dmarsh61]

Sorry, I may not have explained the my situation and the data properly.
A record that is being imported has many fields / columns, but the ones that I am interested in are the following:
Phone Number Start Time Duration
9871234567 8:43 3:00
9871234567 9:15 15:00
9871234567 18:43 8:00
9875661234 10:15 9:00
9875661234 12:15 19:00

The "Start Time" data type is Date/Time "h:nn" and the Duration field data type is Text.
I want to create a query that would sum the duration for each phone number between 8:00 and 16:00. In the example above, the report would look like this:

Phone Number Total
9871234567 18
9875661234 28
(The duration is always rounded to a minute)

I know that in Excel you can extract everything in a string up to a particular symbol, in this case the ":". Is there a way to do this in Access and then convert it to a data type that would be able to be summed?

Thanks

 

[hardy1976]

You could do something like this maybe...
a = number
b = time
c = duration

SELECT Table1.a, Sum(Left([c],InStr(1,[c],":")-1)) AS Expr2
FROM Table1
WHERE (((Left(,InStr(1,,":")-1)) Between 8 And 16))
GROUP BY Table1.a;

 

[plog]

My initial post was correct--use Mid and math.


with that said, you haven't entirely explained what you want to do. Suppose the call starts at 15:50 and the duration is 30 minutes. How many minutes of that call would be added to the Sum?