Queries :: Number Of Calls During Business Hours
Oct 28, 2014
I have to create a query that will reflect the number of calls and messages a person has either taken during business hours and if a message was left how long did it take them to return the message. We are open from 6:00AM - 4:30PM so my boss wants to only see how many calls a person took during business hours. now the users have to run this report daily and monthly. and have to make it accessible through a report so they can send it to the supervisors.
The two fields I have to make the calculations on is the [Date & Time Left] the time the call was made [Date Returned] if they left a message the date it was returned. Also if the call was returned in one day she wants it broken down into minutes (as in how many hours and mins was the call returned)
This is what I have so far: I noticed there was something in Excel called NETWORKDAYS?
Code:
SELECT [Message Tracking].[Date & Time Left], [Message Tracking].[Date Returned], DateDiff("h",[Date & Time Left],[Date Returned]) AS TotalHours, [Message Tracking].ID, [Message Tracking].[User Returning]
FROM [Message Tracking]
GROUP BY [Message Tracking].[Date & Time Left], [Message Tracking].[Date Returned], [Message Tracking].ID, [Message Tracking].[User Returning]
View Replies
ADVERTISEMENT
Oct 27, 2006
I need to calculate the difference between to datetime fields. However, i need to exclude weekends, holidays and non-business hours. I also need to return the result in hours, minutes, seconds.
can this be done. if so, does anyone know how to do this, or is there code that i can implement and save for future uses.
thx,
john
View 1 Replies
View Related
Sep 13, 2013
What I am trying to do is execute a function M-F every 2 hours between 4am and 6pm. The 2 hour part was easy, I just created a form with a timer, but the other part isn't working so well, here is my latest attempt.
Code:
StrTime = Time()
If Not ((Weekday(Now()) = vbSaturday) Or (Weekday(Now()) = vbSunday)) And (StrTime > "4:00" Or StrTime < "18:00") Then
View 3 Replies
View Related
Jun 23, 2014
I am starting to create a resourcing database that needs to be able to work with dates and times and perform calculations on them.
I need to work out the number of hours. I have two date/time fields Day_Start_Time and Day_End_Time. I want to calculate the difference between the two in hours and then multiply by the number of working days.
I tried this: Number_Of_Hours: DateDiff("hh",[Day_Start_Time],[Day_End_Time])*[Number_of_days]
But get #Func! when I run the query.
View 3 Replies
View Related
Jul 7, 2014
I have a report that counts the number of monthly calls. What I would like is an average of the monthly calls in the report footer.
My total for a particular month is =Count([Date]) and I named the unbound control MonthlyTotal.
This is in the DateFooter section of the report.
I then put an unbound control in the report footer and used the expression = Avg([MonthlyTotal]).
Of course when I changed from design view to report view, it asked me for an input of [MonthlyTotal].
I then tried =Avg([Reports]![qryLetterWritersbyDate]![MonthlyTotal]) and while it didn't ask me for an input, there was nothing in the ubound control in the report footer.
I have search for an answer, but all I find is using a query. Is what I am attempting to do possible? If so, how?
View 5 Replies
View Related
Nov 29, 2014
I want to subtract 8 hours of work of the Result of employee work hours/
There is attachment...
View 3 Replies
View Related
Aug 7, 2014
There are three tables. An [Action Register] table, a [Calls] table and a [tblContacts] table. The Contacts are common to both.
The Calls table records calls to customer by date
The Action Register table records issues that Customers send in by Open date.
I am trying to make a query where we see the latest date the customer was contacted regardless of which table.
I created two queries.
qryLastCallDate finds the max date from the Call table:
SELECT Max(Calls.CallDate) AS MaxOfCallDate, tblContacts.ContactName
FROM Calls LEFT JOIN tblContacts ON Calls.ContactID = tblContacts.ContactID
GROUP BY tblContacts.ContactName
ORDER BY Max(Calls.CallDate);
qryLastIssueDate finds the max date from the Action Register table:
SELECT Max([Action Register].Open) AS MaxOfOpen, tblContacts.ContactName
FROM tblContacts RIGHT JOIN [Action Register] ON tblContacts.ContactID = [Action Register].Contact
GROUP BY tblContacts.ContactName
ORDER BY Max([Action Register].Open);
The problem I am having is that if I use Left Join I can see all the records from the Calls table but not all from the Action Register table. And vis versa if I use Right Join. This is because sometimes we have calls but no issues in the Action Register table and sometimes issues with no calls.
This is my Left Join query using a Min Max Module I found here: [URL] ....
SELECT qryLastCallDate.ContactName, qryLastCallDate.MaxOfCallDate, qryLastIssueDate.MaxOfOpen, qryLastIssueDate.ContactName, DateValue(MaxOfList([MaxOfCallDate],[MaxOfOpen])) AS [Last Contact], ([Last Contact]+21) AS NextCall
FROM qryLastCallDate LEFT JOIN qryLastIssueDate ON qryLastCallDate.ContactName = qryLastIssueDate.ContactName;
How do I get to see ALL the records from both queries.
View 3 Replies
View Related
Dec 8, 2007
Hello all,
I have 2 tables:
- Employee
- PackageTracker (Contains PackageNumber, date received, date sent, status)
I would like to query:
- which employee has the package
- How long have we had the package (calculate only business days)
- What is the status
I would like the things mentioned above to be ran in one query.
I am stuck with the calculations. I found a macro code to calculate the business days but I am stuck.
Please advise.
Thank you,
John
View 14 Replies
View Related
Aug 10, 2014
I have a table with a list of records, each record has a timeFrom this I have a query, what I would like the query to do, is group the records in the query by hours.
View 1 Replies
View Related
Oct 14, 2013
I need to run a query in Access 2007 to find any records that have been entered in the last 24 hours.
My field is of type datetime and the name in the database is "EntryDate"
How would I check which records had been input in the last 24 hours?
View 2 Replies
View Related
Nov 4, 2014
I am trying to build a query to show the total sum of hours a staff member has done in a certain department and the amount of sleep ins they have done. Each staff member has a unique ID. In the query I can get all the information but it shows on two lines dependent on department. I want this to show on a single line. Each employee will never have more than two departments. The query is called "QryForForum".
View 2 Replies
View Related
Jun 13, 2014
I created this Vehicle Maintenance and Parts database. In the 'Vehicle Parts Maintenance' button in the frmMenu form, you are supposed to choose the vehicle on the left side then with the drop down on the top right pick what maintenance type you want. Right now when you pick 250 hours it shows all parts attached to the vehicles not only the parts that are supposed to be changed at that maintenance cycle.
The whole database is attached.
View 4 Replies
View Related
Jul 9, 2014
I have designed an access application that logs emails that come into a department. However, the team leader has asked me to create a report to work out if emails were answered within a 24 hour period.
Is there a method to do this. It is not something I have attempted to do within access before.
View 6 Replies
View Related
Jun 26, 2014
How to build a query in Access 2010/13 that will not display any records that are older than 3 hours? See below for more specifics.
The criteria would be based on a field that houses the time (End Time) and also a field that houses a number (Status). So, only if the field in the record says 3 (based on the Status field) then it should not display the record after 3 hours (based on the End Time field) of being changed to a 3. Is it possible to do that?
I have attached a screenshot of what I am referring to.
View 5 Replies
View Related
Jun 14, 2014
I would like to make an action list based on tasks. I try to make a query that does the following things:
-Taken.Priority=1
-show maxium 5 tasks sorted on make date(taken.ingevoerd)
-OR with a total duration of maxium 480 minutes (Taken.[Geschatte duur (min))
The last one is to hard for me to make. For example if the duration of 4 tasks is 420 minutes, the fifth task (100 min) should not be shown, because the criteria of maximum amout of minutes is reached.This is what I have made so far:
SELECT DISTINCT TOP 5 Taken.ID, Taken.Omschrijving, Taken.Ingevoerd, Taken.Deadline, Taken.Priority, Taken.[Geschatte duur (min)]
FROM Taken
GROUP BY Taken.ID, Taken.Omschrijving, Taken.Ingevoerd, Taken.Deadline, Taken.Priority, Taken.[Geschatte duur (min)]
HAVING (((Taken.Priority)=1))
ORDER BY Taken.Ingevoerd DESC;
View 2 Replies
View Related
Feb 12, 2014
This crosstab query has the employee's name as a row heading, each calendar day as a column heading and the sum of Hours worked as the value.
TRANSFORM Sum(tbl_Tracker.Hours) AS SumOfHours
SELECT tbl_Employee_Master.Full_Name
FROM tbl_Employee_Master INNER JOIN tbl_Tracker ON tbl_Employee_Master.user_no = tbl_Tracker.User_Id
GROUP BY tbl_Employee_Master.Full_Name
ORDER BY tbl_Employee_Master.Full_Name, tbl_Tracker.TheDate
PIVOT tbl_Tracker.TheDate;
It returns data that looks like this:
FullName 2/3/2014 2/4/2013 2/5/2014
John Smith 8 0 8
Joe Jones 0 8 0
Cathy Wise 8 8 8
I want the data returned to look like this:
2/3/2014 2/4/2014 2/5/2014
John Smith Joe Jones John Smith
Cathy Wise Cathy Wise Cathy Wise
The hours worked are not important - only the names associated with the dates.
View 2 Replies
View Related
Jun 12, 2013
I did a calculation in a query that gives me how many minutes/hours it took to process records. Now what I need to do is show how many records took only 0-2 hours to process. 2-4 hours to process. 4-6 hours to process etc.
View 2 Replies
View Related
Apr 3, 2013
We are trying to find a way to have a query figure out when the hours of an employee reach 40 hours (Overtime) and figure the hours correctly. The Data Entry table contains the basic fields:
[Workdate].....The date the work was done
[WEDate].......The Week Ending Date. Always on Sunday, and calculated like this "[WorkDate]-Weekday([WorkDate]-1)+7"
[Employee].....Employee Name
[Qty].............# of hours worked that day
[RegHours].....Blank, to be updated by query
[OTHours]......Blank, to be updated by query
The problem we are running into is getting it to update the daily running totals (using the [WEDate] & [employee] as the "Group Bys"). We'd like use an update query to fill in the to blank fields with the correct calculations.
Example - Working 6 day week, at 9 Hours per day in the [Qty] field
Day.............[RegHours]......[OTHours]
Monday..............9..................0
Tuesday............18..................0
Wednesday........27..................0
Thursday...........36..................0
Friday...............40..................5
Saturday...........40.................14
Sunday.............40.................23
We have tried several things to get it to compute this field and haven't had much luck.
View 1 Replies
View Related
Apr 9, 2013
I have a form called Contacts that has two tabs. One is called General adn the other Calls.
In the General tab I have a bound field called CustomerIDdet which can be numeric or alphanumeric. I need to disable edits in the General tab but not in the Calls tab if CustomerIDdet is numeric (and it follows certain additional rules)
I have placed this code in the Onload even for the form:
Code:
If Len(CustomerIDdet) = 9 Then
If IsNumeric(CustomerIDdet) Then
If Right(CustomerIDdet, 4) = 9090 Then
If Left(CustomerIDdet, 1) = 0 Then
'disable editing in the General tab
End If
End If
End If
End If
I know how to disable edits control by control, but can I disable edits in all controls in that tab at once?
View 2 Replies
View Related
Dec 23, 2014
I have a query result for example = 2.19 days worked. The result of cumulated hours divided by 8 (hours in the workday). What I want to do is take the .19 and multiply it by 8 (hours in the workday) and I would have the result in actual hours which is what I need. Or of course if there is a way to display that into 2 days and 1.52 hours Rounded up to quarters would be even better. But with spending hours looking through forums I didn't find a way to just work with the decimals right of the dot. Trim, format, Len didn't work for me, they all give me funny result.
View 7 Replies
View Related
Dec 22, 2014
I want to calculate decimal hours duration taken for a job starting before and finishing after midnight.
I have something that works but it seems cumbersome.
Hour(Format([Job]![Start_time]-1-[Job]![Finish_Time],"Short Time"))
+ (Minute(Format([Job]![Start_time]-1-[Job]![Finish_Time],"Short Time"))/60)
View 11 Replies
View Related
Dec 5, 2014
I have a table of hours that have been worked by employees for each day of the week
[moh] (Monday's hours),[tuh],[weh],[thh],[frh],[sah],[suh]
data eg (this is how I would like it to be inputted into the table)
7:24:00,7:24:00,7:24:00,7:24:00,7:22:00,0:00:00,0: 00:00
This equates to 36:58:00
I have tried
Total Hours: [moh]+[tuh]+[weh]+[thh]+[frh]+[sah]+[suh]
but I am struggling to get what I want in the right format.
How to record the initial data or a formula to format the end result.
Excel just does it !!!!!
View 1 Replies
View Related
Apr 11, 2014
I have two forms:
frmOpeartions
frmManagers
frmOperations allows the user to assign a manager to an operation by selecting the manager record from a combobox. Occasionally the user may need to setup a new Manager record if one hasn't been setup already. In this case there is a "New" "button" (it's actually a label with an on click event) that the user can click to open frmManagers and add the new manager record.
The code to open frmManagers is:
Private Sub lblNewManager_Click()
DoCmd.OpenForm "frmManagers", acNormal, , , acFormAdd, acDialog
Forms!frmManagers!cboMoveTo.Visible = False
Forms!frmManagers!lblManagers.Visible = True
End Sub
Once frmManagers is open the user creates the new Manager record and then closes the form using a similar label with an on click event:
Code:
Private Sub lblClose_Click()
DoCmd.Close acForm, "frmManagers", acSaveNo
End Sub
frmMangers also has an OnClose event that will refresh any comboboxes on other forms that refer to tblManagers to make sure that new Manager records will be available immediately for the user to choose from:
Code:
Private Sub Form_Close()
If CurrentProject.AllForms("frmPlants").IsLoaded Then
Forms!frmPlants!cboPlantManager.Requery
Forms!frmPlants!cboQCManager.Requery
[Code] .....
So the problem comes when the user clicks the Close label (acting like a button) on the frmManagers. The code successfully closes the form and the on close event successfully refreshes any comboboxes on forms that may be open, but then for some reason it attempts to run again or perhaps continue running the onClick event that opens frmManagers. Since the form is already closed it gets hung up on trying to change the visible properties of the controls and the code fails.
View 6 Replies
View Related
Apr 23, 2015
I'm working on a utilization database and pulling Excel data from Cognos 8. I use Access formulas to separate hours and minutes because I want to use time (.30, .45 (minutes)) instead of quarterly increments (.25, .50). I've successfully segregated hours, because I also have to turn days into hours. When I try to calculate out a column just for minutes, my formula keeps the number of days and I don't know how to fix it so the column just shows the minutes.
An example is a job has a decimal time of 2.02083333, which when done correctly is 48 hours and 30 minutes. I get the 48 hours correct, but the minutes shows as 2.30 instead of 0.30. That inflates the total job time to 50.30. This is the calculation currently in my database to pull out minutes:
Minutes: Int(([Min]60)24) & "." & Format([Min] Mod 60,"00").
How do I get rid of the '2' in the 2.30?
Access 2010
View 2 Replies
View Related
Feb 18, 2014
Access Query. I am creating a time sheet / pay roll database and I want to be able to get a total of the daily hours in a query.
For example I have 'Mon Start' and 'Mon Finish' for Mondays in/out times and I have a 'Mon Total' which gives me the total hours worked for Monday.
The problem I have is that Mon Total only works if the hours are say between 07:00 and 17:00, anything after midnight (00:00) like 21:00 to 07:00 and 'Mon Total' goes crazy !!
At the moment 'Mon Total' is the result of CDate 'Mon Finish' - 'Mon Start' (bit rough I know).
View 3 Replies
View Related
Jul 5, 2013
I have been tasked with importing data from a clocking on system to analyse worked hours.
There is no option to amend the format of the export which is not great
surnameforenameField2Field3xxx
xxx
11/06/201319.18
xxx
xxx
11/06/20135.37+
xxx
xxx
12/06/20135.41+
xxx
xxx
12/06/201319.21
The plus indicates a night shift worker
eg started at 19.18 and finished at 5.37 on the 12th
so for every record I have staff id , date, and clock time ( I have stripped out the + ) and created a yes/no field to identify the records where field2 should actually be field2+1
I have sat in query design screen for ages and cant think how on earth I am going to calculate hours worked for a given staff member and date combination.
for every combination of staff id and date there should be 2 records - a clock in and a clock out
I thought about creating new fields clockin and clockout but struggling to see how I can link the 2 "paired" records together
vb script with dlookup for every record ?
View 10 Replies
View Related
