MS Access question

Hiroyuki Tamura
Hiroyuki Tamura used Ask the Experts™
on
Sum fuction, could i use nn format? i need to convert it in minutes
Screenshot1.jpg
SELECT [8 qMonthlyCallDurationInboundDetail].Date, Sum([8 qMonthlyCallDurationInboundDetail].Expr1) AS SumOfExpr1, Sum([8 qMonthlyCallDurationInboundDetail].Expr2) AS SumOfExpr2
FROM [8 qMonthlyCallDurationInboundDetail]
GROUP BY [8 qMonthlyCallDurationInboundDetail].Date;

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2016

Commented:
try multiplying the result with 24 * 8

SELECT [8 qMonthlyCallDurationInboundDetail].Date
, (Sum([8 qMonthlyCallDurationInboundDetail].Expr1)) * 24 * 60 AS SumOfExpr1
, (Sum([8 qMonthlyCallDurationInboundDetail].Expr2)) * 24 * 60 AS SumOfExpr2
FROM [8 qMonthlyCallDurationInboundDetail]
GROUP BY [8 qMonthlyCallDurationInboundDetail].Date;
Hiroyuki TamuraField Engineer

Author

Commented:
not getting the number
Screenshot1.jpg
Hiroyuki TamuraField Engineer

Author

Commented:
i don't understand nn format.
nn  Minute in two digits (00 to 59).
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Top Expert 2016

Commented:
ok, we can't keep on guessing here. we need more information

how did you create the query [8 qMonthlyCallDurationInboundDetail]

or better upload a copy of your db
Top Expert 2016

Commented:
here is link that computes date/time
https://support.microsoft.com/en-us/kb/210604

look for  GetElapsedTime function in the page..
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
If your Duration field is a time value, you can just sum these value with no conversion at all, then format as you like.
If the sum may exceed 24 hours, use a function like this to obtain a proper format:
Public Function FormatHourMinute( _
  ByVal datTime As Date, _
  Optional ByVal strSeparator As String = ":") _
  As String
  
' Returns count of days, hours and minutes of datTime
' converted to hours and minutes as a formatted string
' with an optional choice of time separator.
'
' Example:
'   datTime: #10:03# + #20:01#
'   returns: 30:04
'
' 2005-02-05. Cactus Data ApS, CPH.

  Dim strHour       As String
  Dim strMinute     As String
  Dim strHourMinute As String
  
  strHour = CStr(Fix(datTime) * 24 + Hour(datTime))
  ' Add leading zero to minute count when needed.
  strMinute = Right("0" & CStr(Minute(datTime)), 2)
  strHourMinute = strHour & strSeparator & strMinute
  
  FormatHourMinute = strHourMinute
  
End Function

Open in new window

/gustav
Hiroyuki TamuraField Engineer

Author

Commented:
sorry, here is the sample
Helpdesk1revA.zip
Top Expert 2016
Commented:
here test this, see queries Q1 and Q2
Helpdesk1revA.accdb
Hiroyuki TamuraField Engineer

Author

Commented:
thank you.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial