Average Time In Query
Dec 6, 2005
I have a query with the fields dtDate (Date/Time in Short Date format), NC "Number of Chats" (Long Integer), and TCT "Total Chat Time"(Date/Time in hh:nn:ss format) from tblChats. Each date will have multiple NC and TCT values. This query is totaling them by date with the following SQL.
SELECT DISTINCTROW tblChat.dtDate, Format$([tblChat].[dtDate],'Short Date') AS ChatDate, Sum(tblChat.TCT) AS SumOfTCT, Sum(tblChat.NC) AS SumOfNC
FROM tblChat
GROUP BY tblChat.dtDate, Format$([tblChat].[dtDate],'Short Date')
HAVING (((tblChat.dtDate)>=Date()-7))
ORDER BY tblChat.dtDate;
The query works fine. Now my question.
I would like the query to figure the average time per chat for each day.
Could this be done without VBA? Any ideas??
Thanks.
View Replies
ADVERTISEMENT
May 21, 2007
i have a date field with time and date each record is entered. results look like this
Date_Complete
4/9/2007 8:26:11 AM
4/9/2007 8:31:25 AM
4/9/2007 8:34:14 AM
4/9/2007 8:34:21 AM
4/9/2007 8:34:29 AM
4/9/2007 8:34:36 AM
4/9/2007 8:34:49 AM
4/9/2007 8:41:27 AM
4/9/2007 8:41:49 AM
4/9/2007 8:42:32 AM
4/9/2007 8:42:39 AM
4/9/2007 8:42:49 AM
4/9/2007 8:43:36 AM
4/9/2007 8:44:21 AM
4/9/2007 8:45:48 AM
I want a query or report to give me the average entry time per record. Something like: Average time between orders 1:25 (one minute twenty five seconds)
View 2 Replies
View Related
Jan 16, 2007
Hi,
I need to calculate an average waiting time for patients waiting to be seen in an A&E dept between two dates.
I have calculated the total waiting time for each patient with the following formula
Total Waiting Time: Format([Time Arrived]-1-[Time Left Department],"Short Time").
Any help much Appreciated
View 10 Replies
View Related
Jun 20, 2007
I hv a access mdb which contains one column as "duration", data format as hh:mm:ss, e.g. 00:02:20 - 2 mins 20secs.
How could I calulate the average of the duration?
like sum of all duration fields / the ttl no of records.
View 1 Replies
View Related
Apr 8, 2008
We sell products out of vending machines and once in every few days (time span between readings may be different every time) read data from the machines using flash drive. Each reading from all machines produces an Excel file:
Reading_Date | Machine_ID | Product_ID | Amount_sold
2008-04-08 | 1 | 1 | 26
The only date that we have is the day on which the data has been read and this tells how many sausages were sold from the date of last reading till the date of current reading (after each reading counters are set to zero).
The problem is to get average amount of product X sold from machine Y on every calendar day. For a total rookie like me it seems like creating another column that would divide the Amount_sold by the number of days between two consecutive readings and put this average amount for every calendar day between these two readings. May it be done this way? How? Another way? How? I would be very grateful if anyone could help me and explain the solution in an accessible way.
View 4 Replies
View Related
Feb 7, 2014
Basically, I get daily reports from the client and the AHT and ACW values come in seconds. I've recently added code to the query changing the data to nn:ss which works perfectly. Below is some info:
tblTelephony
[AgentName]
[Calls] (number of calls answered)
[AHT] (in seconds)
[ACW] (in seconds)
[Code] ....
The above code works very well, where I'm stuck is in the attempt to average the fields [AHTMinSec] & [ACWMinSec] by Agent in a report.
Using [AHTMinSec] as an example:
I have the daily values for each agent [AHTMinSec] in the detail, Avg([AHTMinSec]) in the Agent group footer showing each agent's average, Avg([AHTMinSec]) in the Team group footer showing team averages and Avg([AHTMinSec]) in the report footer showing the campaign average including all teams. When I run the report the details are hidden providing each agent's average, the team average, and the campaign average.
The report worked just fine until I converted to minutes with the above code. Is there a reason that I'm getting an error stating that the calculation is too complex? I've done enough research to determine that the db can't Sum in order to average...
View 12 Replies
View Related
May 3, 2006
Hi,
I'm trying to create a query that returns 10-min average wind speed.
I have the logging date,time and the wind speed per second in the wind log table.
Date and Time Wind Speed(mph)
28/04/2006 2:17:01 PM 10.5
28/04/2006 2:17:02 PM 10.6
28/04/2006 2:17:03 PM 10
...
And I would like something like this from the query:
Date and Time Wind Speed Ave
28/04/2006 2:17:00 PM 10
28/04/2006 2:18:00 PM 7
28/04/2006 2:19:00 PM 5
......
Thx,
1.8T
View 5 Replies
View Related
Jun 3, 2005
I apologize, I know this has been covered. But I just spent half an hour reading old posts and still can't quite decide how to apply it to what I'm doing.
I have a db that logs surgeries and all their details. One of the new things they want to do is be able to run a list of average cost for a certain surgery, since patients are always asking ahead of time how much it will cost. I have a query (and report that runs from it) that will list all the surgeries and total charges for individual ones for a date range the user specifies. But I can't figure out how to make it calculate an average charge for each surgery. I could if there were always a certain number to divide by, but of course there could be 2 of this type of surgery and 57 of that type.
The query I currently have set up is:
Field: MR#
Table: SurgeryLog
Field: Wname: [LastName] & ", " & [FirstName] & " " & [Initial]
Table:
Sort: Ascending
Field: OperationDate
Table: Surgery Log
Criteria: >=[Enter Start Date]
Field: PatientType
Table: SurgeryLog
Criteria: "SDS"
Field: TotalCharges
Table: SurgeryLog
Criteria: >0
Field: Operations: [Operation1Performed] & Chr(13) & Chr(10) & [Operation2Performed] & Chr(13) & Chr(10) & [Operation3Performed]
Thanks much!
View 1 Replies
View Related
Sep 22, 2015
Right now, I have an output that looks like this:
Code:
AvgCost CompanyID Policy#
25 22 12
28 23 12
35 24 12
21 25 12
20 22 20
15 24 20
13 23 21
43 24 21
Etc.
I want to know if it is possible to get an output that adds a ranks the CompanyIds by average cost per each policy #.how companyID24 ranks, and I have the query set up where it only outputs Policy#'s that company 24 is ranked in (has sold product in).
Code:
AvgCost CompanyID Policy# Rank
25 22 12 2
28 23 12 3
35 24 12 4
21 25 12 1
[code]....
View 11 Replies
View Related
Aug 12, 2005
I have a form that I want to use in order to work out a global figure for average travel time for my engineers (data coming from another database).
I have a form with two text boxes, which allow the user to define a date range. This links into my query which works fine and returns all records from that date range.
Now comes the troubling part!
On the click of a command button I want the label to change and display the average for my work time column form my query (which by the way is in decimal format).
Can anyone help me? I have tried searcing but to no avail
View 14 Replies
View Related
Jul 5, 2013
I am currently designing a dashboard for my team which works on projects, I have a main table which has main fields which i use for my dashboard
Project Type,Project ID (Primary Key), Submitted by, Project Start date, Project end date, Status
The tricky part is that the raw data has projects which got started couple of years back some got closed in the previous fiscal year but some are still being worked upon..I was able to put in a where clause to only display projects whose close dates either have null value ( projects which are still being worked) and where the close dates are >= 1/1/2013. My Dashboard needs to have the below fields
Project Type, Count of Projects, Count of Closed Projects, Average days to complete, Count of Active Projects & Average Active days
I was able to get it correct except the last field Average Active days as it is displaying Average no's even against the project type which doesn't have any active projects or where the active project count is zero.
SELECT TblWO.[Operational* Categorization Tier 2], Count([TblWO]![Operational* Categorization Tier 2]) AS [Count], Avg([TblWO]![SLA in Days]) AS [Internal SLO in Days], Count([TblWO]![Closedate]) AS [No Closed Projects], Round(Avg([TblWO]![Closedate]-[TblWO]![Work Order Submit Date Time]),2) AS [Avg Close Days], [Count]-[No Closed Projects] AS [No Active Projects], Round(Avg(Date()-[TblWO]![Work Order Submit Date Time]),2) AS [Active Avg Days]
FROM TblWO
WHERE ((([TblWO]![Closedate]) Is Null Or ([TblWO]![Closedate])>=#10/1/2012#) AND (([TblWO]![Status])<>"Cancelled" And ([TblWO]![Status])<>"Rejected"))
GROUP BY TblWO.[Operational* Categorization Tier 2]
HAVING (((TblWO.[Operational* Categorization Tier 2])<>" "));
View 3 Replies
View Related
May 21, 2013
I made a query to calculate the average of a column, suppose that I have 5 records in that column (46,35,0,19.3,12), when the query calculate the average it sums the total of the column and divide by 5 (that's 112/5 = 22.4), what I need the query to do is to divide by 4 because one record is zero (that's 112/4=28). I put in the criteria the following (Not Is Null And <>0), yes this will not show the column that has zero but it still divide by 5.
View 6 Replies
View Related
Feb 17, 2008
Can someone help me please???:rolleyes: I have to query for the avg balance from customer table, custbal field from a certain state... I've tried everything and cannot seem to get the expression correct. How do I do this???? any help is appricated :D
View 2 Replies
View Related
Feb 16, 2007
Hi guys!!!!
I try to find an answer in the forum about "Average Fields",but ican't
I am confused:(
I wan't to export Avg Of the fields like in the panel below:
View 2 Replies
View Related
Oct 3, 2007
i have a quick question... I am having a slight problem with a query that i am using in my database... i am trying to calculate miles per gallon. The query i have set up looks at the previous records odometer value and subtracts the current odometer value to get the miles driven since the previous fill... Here is my sql code.
SELECT fuel_use_tbl.unit_ID, fuel_use_tbl.fuel_use_ID, fuel_use_tbl.fuel_date, fuel_use_tbl.gallons, fuel_use_tbl.odometer, (SELECT TOP 1 Dupe.odometer FROM fuel_use_tbl AS Dupe
WHERE Dupe.unit_ID = fuel_use_tbl.unit_ID
AND Dupe.fuel_date < fuel_use_tbl.fuel_date
ORDER BY Dupe.fuel_date DESC, Dupe.fuel_use_ID) AS PriorValue, [odometer]-[PriorValue] AS [Miles Driven], [Miles Driven]/[gallons] AS Expr1
FROM fuel_use_tbl;
The problem with this code is this... lets say i drive a brand new vehicle (its first record and it has no prior odometer value... the PriorValue reading shows up as nothing (NULL) when i then try to take [odometer]-[PriorValue] it shows up as nothing (NULL) while it should just treat it as [odometer]-0... i need help so that it shows an average mpg for every fill... Thanks for your help.
For a better example this is exactly what it looks like
unit_IDfuel_use_IDfuel_dategallonsodometerPriorValueMiles DrivenMPG
11328/11/200700.00
11269/11/200726400.00040015.3846153846154
11279/17/200722700.0040030013.6363636363636
113110/16/2007231,131.0070043118.7391304347826
101289/11/2007220.00
101299/17/200721500.00050023.8095238095238
101309/18/200728800.0050030010.7142857142857
Fuel Use ID is my primary Key and it is autoindexed... ive just been messing around with fake data and have changed the dates... thats why it looks a little strange
Greeny
View 6 Replies
View Related
Apr 9, 2014
I obtained data for earnings by industry for men and women over time (5 years). I developed crosstab query that showed the average earnings for men and women for the 5 years.
Now I want within this query to calculate the differential between men and womens average earnings and calculate a percentage within this crosstab query. I tried to use Expressionbuilder with little luck.
View 6 Replies
View Related
Jun 5, 2014
I am trying to get Average If function to access sql. I have columns Period and Costs_Per_Capita, result should be like like this:
Costs_Per_Capita Period CALCULATED_Period_Avg_Costs
15,505 1 15976.27582
16,368 1 15976.27582
16,037 1 15976.27582
15,995 1 15976.27582
15,000 2 16000
17,000 2 16000
I tried:
SELECT Costs.Costs_Per_Capita, Costs.Period
IIF (Period = 1, (Select AVG(Costs_Per_Capita) From Costs Where Period = 1),
(Select AVG(Costs_Per_Capita) From Costs Where Period = 2)
AS result
FROM Costs;
But get "syntax error (missing operator) in a query expression ..."
View 8 Replies
View Related
Aug 16, 2015
I found the attached example a while back (can't find the site again though ) and it calculates a moving average. I've hacked out the parts I need for my own work and I can create my moving average query without an issue.
However, I need to extract the MA data into a table so planned on using append. I kept getting type errors so I tried make table to see what type it was creating and it appears to be Short Text rather than a number.
I've added an extra button and Make Table query to the example.
As far as I can tell from the code, the moving average value when calculated is a Single. However, when I write it to the table, its a Short Text.
How do I make the created Table use Number Type for my calculated moving average?
Using Access 2013
View 2 Replies
View Related
Jun 3, 2014
I am trying to construct a crosstab that averages a calculated field from a previous query. It is returning a "Data Type Mismatch" message.
The field I am trying to average is a subtraction of dates to find total days. I assume my field is not a number so I have tried to wrap it in CDbl() to change the type.
The formula is
Code:
CASE_DAYS: CDbl(IIf([Actual Close Date]-[Creation Date]>=0,[Actual Close Date]-[Creation Date],""))
View 5 Replies
View Related
Nov 3, 2014
I have finger print machine and i already connect to it and get all log.then i tray to get data and here is the code that im using
SELECT Format(CHECKINOUT.CHECKTIME,"dd/mm/yyyy") AS CDate, IIf([CHECKINOUT.CHECKTYPE]=I,Format(CHECKINOUT.CHECKTIME,"hh:nn:ss ampm")) AS StartTime, Format(CHECKINOUT.CHECKTIME,"hh:nn:ss ampm") AS EndTime, USERINFO.USERID, USERINFO.Name, CHECKINOUT.CHECKTYPE
FROM CHECKINOUT INNER JOIN USERINFO ON CHECKINOUT.USERID=USERINFO.USERID
WHERE (((Format([CHECKINOUT].[CHECKTIME],"dd/mm/yyyy"))='10/04/2014'));
how i get end time due to CHECKTYPE]=O
View 1 Replies
View Related
Aug 17, 2006
Hello buddies :D, do you have any idea how to make this work?
To select data that falls within this criteria of date range between cboDate and cboDate2 (fields on my form). The date in [tblJobDetails]![timeIn] come in this format "08/17/06 10:24 AM", but the cboDate/cboDate2 (takes in date only e.g 08/17/06) what i am after is to evaluate specific hard coded time in addition to the date entered, i.e. even tho, i haven't entered time on the cboDate/cboDate2, I want specific time hard coded where e.g If i select a date range of 08/17/06 and 08/18/06 on my cboDate and cboDate2 it should really be evaluating: 08/17/06 8:00 AM to 08/18/06 8:00 AM.
This is the criteria i curentlly have on my query in design view tha works perfect in selecting date only.
([tblJobDetails]![timeIn]>=[Forms]![frmPendingJobs]![cboDate] Or [tblJobDetails]![timeIn]>=[Forms]![frmPendingJobs]![cboDate] Is Null) And ([tblJobDetails]![timeIn]<=[Forms]![frmPendingJobs]![cboDate2] Or [tblJobDetails]![timeIn]<=[Forms]![frmPendingJobs]![cboDate2] Is Null)
How can I incorporate 8:00am to 8:00am into my cboDate and cboDate2. What can i do to make this happen? Your kindness will be greatly appreciated http://www.naijaryders.com/forums/images/smilies/thankyou.gif
View 10 Replies
View Related
Jun 7, 2006
I have a link table with a field in GMT time. I want to convert the times to local time in a query. I know that I should use DateAdd, but I am just wondering how to know summer and winter time. On the summer our local time is +2 GMT and on winter +1.
View 14 Replies
View Related
May 23, 2006
I have an application with a backend db on PC 1, and the same application on PC 2 linked to the backend db on PC 1 via the network.
The link works fine but the time formats are different.
On PC 1 the time format is shortime and displays as it should i.e. in 24 hour clock format
However on PC 2, opening the same database via the network, the time format is still shorttime but when you read the time within the code it comes out in AM/PM format. Also, when the defaul tiem should be #20:00:00# but this changes to #8:00:00 PM#
Weirdly though, when you just open the table, the times are in the correct shorttime format.
Guessing, it must be a setting within the main core of Access 2003 that is different between the 2 instances?
Any ideas?
This screen shot may help:
View 2 Replies
View Related
May 23, 2006
I have an application with a backend db on PC 1, and the same application on PC 2 linked to the backend db on PC 1 via the network.
The link works fine but the time formats are different.
On PC 1 the time format is shortime and displays as it should i.e. in 24 hour clock format
However on PC 2, opening the same database via the network, the time format is still short time but the format is in AM/PM or medium time.
Guessing, it must be a setting within the main core of Access 2003 that is different between the 2 instances?
Any ideas?
View 1 Replies
View Related
Jan 11, 2008
I have a query that finds an average. How can I get the average to only show two numbers after the decimal?
View 4 Replies
View Related
Dec 10, 2005
I apologise for my ignorance, but I’m very new to Access.
I have a database of dates, that I need to analyse.
I have created a Form called "DateRange" with 2 date fields;
Text1 = Date From
Text2 = Date to
Command1 = Preview Report
My Query has 2 fields;
Slotdate = all the dates (show as 20051210)
Actdur = Actual Duration (show as numbers 1 or 12 or -3 etc)
The SQL View is;
SELECT slotapp.slotdate, slotapp.actdur
FROM slotapp
WHERE (((slotapp.slotdate) Between [Forms]![DateRange]![Text1] And [Forms]![DateRange]![Text2]));
I just want to calculate an average of Actual Duration
So that my report displays the average duration between the date ranges.
Any assistance in this matter would be greatly appreciated
View 1 Replies
View Related