Working Out Total Time Spent (In A Query)

Apr 8, 2008

In My Db I have TblSupportTickets and a Table which records support ticket activity. When adding a new activity detail to the Db it automatically records when the FrmActivityDetail opens and on closing, it records the time the Form was closed. So in TblSupportTickets which relates by ID to TblSupportTicketActivity on a one-to-many relationship.

I then created a query with SupportTicketID, TimeOpen, Time Closed. SupportTicketID criteria is Forms.FrmSupportTicket.txtSupportTicketID

This lists all the time open's and timeclosed's for that support ticket.

how can I work out the total time spent on that support ticket?

View Replies


ADVERTISEMENT

General :: How To Get Total Spent Hours In Office For The Employee

Jan 28, 2014

I am facing one problem while calculating the number of hours an employee spent in the office. Each employee will have a swipe card and the swipe data is stored in an .mdb file. Some employees will have different shifts and may come after 6pm and will go next day 5 am , while calculating their spent hours getting the problem. The swipe data is stored in IOData table , I wrote a simple query below to get the data., it is calculating correctly for the employees who come in between 11AM to 8PM but who are coming after 6PM and going next day , facing problem. Time is storing in 24 hour format in table with Status as Entry,Exit . There may be any no.of exits and entries , so i took min and max of the time in the below query.

I am attaching the mdb file data in an excel sheet(Master_Data.xls) and also the data of the below query(Swipe_Data.xls) and highlighting the employee for which we got problem.

SELECT HD.JobTitle, HD.HolderName, IO.IODate,IO.IOStatus, min(IO.IOTime), max(IO.IOTime), DateDiff("n", min(IO.IOTime), max(IO.IOTime)) AS Minutes, [Minutes] 60 & Format([Minutes] Mod 60, ":00")
FROM HolderData AS HD, IOData AS IO
WHERE HD.HolderNo = IO.HolderNo and
HD.DepartmentNo IN ('0008', '0009') and
IO.IODate between #01/20/2014# and #01/24/2014#
GROUP BY HD.JobTitle, HD.HolderName, IO.IODate,IO.IOStatus;

View 3 Replies View Related

Record Time Spent Task?

Jul 9, 2014

I am using the Issues Template Database and would like to add a function to it.

I would like to record each time spent task/ action when working on an issue.

For example:
Tasks
- Email
- Phone Call
- Report

So, say I wanted to add one Report- time taken 30mins and record that then on an another day, Email- 10mins So record each task and time taken.

View 1 Replies View Related

Queries :: Tracking Time Spent On Customers Records Based On Date Of Entry

Apr 17, 2014

I have two tables, One table containes customer name, etc., tblcustomers the other table contains the tbltimelog, log of activity start time end time. No issues. I want to generate a form based on the dateofentry for the time record. I am not able to get a summary lets say I spent 20 hours on ABC and 20 hours on CBS. If I spent 3 different days on ABC and 6 different days on CBS it will not add them together it will list it ABC 3 times and CBS 6 times. I have tried the distinct function but it doesn't work because the date is distinct. Is there a way to do this as an SQL or whatever. Thinking I could just create a new table and calculating the totals but that seems to be a waste.

View 3 Replies View Related

Queries :: Make Table Query Not Working Because Of Running Total?

Nov 12, 2014

I have a running total query that seems to run but when I try to total the query results then Access will be "Not Responding". I tried to change it to a Make Table query because I need to use the running total result in another query. So I created a table but when I try to run the make table query it just says "Run Query" at the bottom. Here is the query:

SELECT [OTMissing].[Employee], [OTMissing].[AsOf], [OTMissing].[HRsEarn], (SELECT Sum(OT1.[HRsEarn]) FROM [OTMissing] As OT1
WHERE OT1.[Employee]=[OTMissing].[Employee] AND OT1.[AsOf] <=[OTMissing].[AsOf]) AS RunningTotal, [OTMissing].[RemainPP] INTO OTGenerated
FROM [OTMissing]
ORDER BY [OTMissing].[Employee], [OTMissing].AsOf;

My OTMissing query is 47061 rows. Does that have something to do with it? The only other thing it might be is that most of the records have 0 although I'm not sure why it would be a problem I thought I would at least mention it.

View 2 Replies View Related

Problem With Query And Total Time

Mar 6, 2007

hi all,
i am trying to create a report based on my query, and would like to split the total time up by 2 diff tasks. admin and investigative. problem i am running into is when using the total time functino i have and setting the criteria to match the task ID i am getting an error saying it is too complex. any ideas?
IT goes something like this

AdminTime: NZ(IIf([StartTime]<[EndTime],DateDiff("n",[StartTime],[EndTime]),1440-DateDiff("n",[EndTime],[StartTime]))/60)+(Nz([ExpenseHour]))+(Nz([ExpenseMinute]/60))
WHERE TaskID = 2

can this not be done within a single query?

View 7 Replies View Related

Total Time Query Displays Incorrect Value

Feb 16, 2007

Hi,
I am using

Total_Time: Format([totaltime_mins]/60,"00") & ":" & Format([Totaltime_mins] Mod 60,"00")

To display the time as hours and minutes but this formula doesn't always work correctly.

for instance if the totaltime_mins = 90 then total_time is displayed as 2:30

also if totaltime_mins = 40 then thotal_time is displayed as 1:40

does anybody know why this is not working correctly ?


kind regards,
jamie

View 2 Replies View Related

Short Time Not Working In Make Table Query

Mar 8, 2006

I've set up a query to calculate the difference between 2 time fields. I set the property to short time.

The thing is - when I run the query in a Select query, the times in the column which contains the difference, appear correctly : ie hh:mm, but when I run the same query in as a make table query, the time difference field (although I have set the property to short time) appear something like this:

0.664464564646

Can somebody please tell me how to make it that the time difference results field in my make table query appears as hh:mm??

Thanks in advance - nelld

View 1 Replies View Related

Calculating Total Time But Excluding Overlapping Time

Aug 22, 2012

I have a database consisting of two tables. One is "articles" and the other is "tasks". To put it simply, I would like to find how much time the article spends in tasks, but one article can have many tasks, and they often (but not always) overlap. Tasks have a start and end date field.

View 8 Replies View Related

Total Working Hours

Mar 10, 2008

Hi ther,

I wonder if someone could help me with what I am sure is simply code, I just can't seem to get the response I want. :confused:

I am working out each member of staffs working hours for the day.

[Start Time]
[Finish Time]
minus a 30 min lunch.

I would like to display the total hours worked for the day in an hour:minute format.

I've tried a few diferent things and still not got the answer I was after.

Your help with this is greatly appreciated.

View 7 Replies View Related

Queries :: Total Max Working - How To Get Other Data

Dec 11, 2014

I know a fair bit about access 2010. I have the following table

------------------------------------------------------------------------------
- product no ----- Branch ID --- POS Description --last sold date --
------------------------------------------------------------------------------
- 123 ------- 1 ----- fly rod ---- 19/01/2014 ----
- 123 ------- 2 ----- fly rod ---- 19/03/2014 ----
- 123 ------- 3 ----- fly rod ---- 19/05/2014 ----
- 123 ------- 4 ----- fly rod ---- 19/06/2014 ----
- 123 ------- 5 ----- fly rod ---- 19/09/2014 ----
- 633 ------- 1 ----- fly reel ---- 11/01/2014 ----
- 633 ------- 2 ----- fly reel ---- 11/03/2014 ----
- 633 ------- 3 ----- fly reel ---- 11/05/2014 ----
- 633 ------- 4 ----- fly reel ---- 11/06/2014 ----
- 633 ------- 5 ----- fly reel ---- 11/09/2014 ----
------------------------------------------------------------------------------

I need it to give me back, one row per product (product no) - the newest date (Date Last Sold)

So i am trying to find out the last time each product sold, regardless of where

Which i can do with MAX date, group by product num

SELECT SMKPLUDetail.[Product No], Last(SMKPLUDetail.[Branch ID]) AS [LastOfBranch ID], Last(SMKPLUDetail.[POS Description]) AS [LastOfPOS Description], Max(SMKPLUDetail.[Date Last Sold]) AS [MaxOfDate Last Sold]
FROM SMKPLUDetail
GROUP BY SMKPLUDetail.[Product No];

------------------------------------------------------------------------------
- product no ----- Branch ID --- POS Description --last sold date --
------------------------------------------------------------------------------
- 123 ------- 5 ----- fly rod ---- 19/09/2014 ----
- 633 ------- 5 ----- fly reel ---- 11/09/2014 ----
------------------------------------------------------------------------------

This is great, but ofcourse (last of is fine for description) as is does not change

But last of, always returns branch 11.. and i infact want the branch which is correct, the branch which the date last sold is from, the current record really

but i do not seem to be able to get it to show me, any other fields from the querry current row.

View 3 Replies View Related

Modules & VBA :: Count Total Working Days Of Month

Mar 5, 2014

I have a table with two fields named as "Month" and "Bank Holidays". I have put names of the month in this table for 2014 and the corresponding bank holidays of each month. Now I want to write down the piece of code that will execute when a button is pressed and check the current month of the year and then display the total working days ( After subtracting bank holidays of that month and weekends of that month from the total working days of that month).

View 2 Replies View Related

Queries :: Self-Referencing Running Total Used To Calculate Next Total In A Query

Jul 23, 2015

I am trying to create a query that has a self referencing running total based on the values (point totals) of itself (running total of values in the running total column that have already been calculated for all previous records) plus the total of new points being added in the current record, less the total of points being removed in the current record. This running total can never go below 0, if it does, the running total should restart at zero and add in only new points and begin the process again with the next records

I am able to do this in Excel in less than two seconds so I know there has to be a way to port this into a query. I've attached an excel example of what I am exactly trying to do

If it takes multiple queries to complete the required output I am ok with it. In my previous outtakes I have had up to 8 queries but just couldn't seem to do it..

View 9 Replies View Related

Help!-Trying To Total Elapsed Time

Jul 19, 2006

I have been trying to help a user calculate elapsed time. I referenced the GetElapsedTime from the following Microsoft article

http://support.microsoft.com/kb/210604/

It displays ok on a form or inserting on the detail in a report like it suggests
but the user would also like to total up the elapsed time on the report. Not sure how to do that with the way this function is built.

View 4 Replies View Related

Total Time Question

Nov 8, 2006

Can anyone advise:

I have two time/date fields, have subtracted one from the other, and get the number of hours and minutes. From there the data is totaled in a report.

The problem is when the total hours goes beyond 24 hours, the additional time is not added. (eg. if you have 23:30 and you added 5 more hours it does not come out to 28:30):mad: :confused:

Dan

View 7 Replies View Related

Running Time Total

Oct 13, 2004

I am trying to put together this report where I can see how many hours the printers were running in a month's time. The sum property for this is working up until it reaches 24 hours then it starts all over again. How can I get it to keep adding hours when it gets to 24?

For example: 20:35 + 6:25 = 3:00

learnasugo

View 3 Replies View Related

Budget Less Spent

Dec 9, 2005

I am using Access 2003 and am in the process of building a database for the process of recording invoices there are three properties one of which has three schedules.

I have created a query that has budget amounts.

Another query that brings up the amount spent by code.

What I want to do is have the budget figures with the amount spent by code beside it, I can do this but it does not include any items which have zero spent in them so far.

Any pointers regardless of how simple they sound would be gratefully recieved I am newish to building databases.

Thanks

View 2 Replies View Related

Total Time On Hours And Minutes

Jan 29, 2007

Hi,
I am trying to show the time difference between 2 times but the calculation is not working correctly.

First I work out the totaltime on mins between 2 dates using datediff, then I am trying to convert to hours and minutes like :
Total_Time: Format([Sumoftotaltime_mins]/60,"00") & ":" & Format([SumofTotaltime_mins] Mod 60,"00")

This is working until I have something like :

03:00
15:45
25:20

this should work out as 44 hours and 5 mins but for some reason it is showing as 43 hours and 5 mins.

why would this be ?

View 4 Replies View Related

Calculating Total Time Worked

Sep 11, 2014

I have a table in access that has 1 ) persons ID and ) log date/time 3) direction in or out

I need to calculate the amount of time spent by the person in the office. e.g User with ID 1 will come in at 8 in the morning and go out after an hour then come in again and go out. Till he leaves out for the day. I need to find the first time he came in and last time he went out and find the difference for the hours he worked

The data is in Device logs table.

View 3 Replies View Related

Table With Four Fields - Calculation Of Total Time

Feb 21, 2013

I have created a table with four fields. The names are:1stBusOn, 1stBusOff, 2ndBusOn, 2ndBusOff.

I want the time for the first two fields and the time for the second two fields to end up in a field called TotalRideTime.

Also, if only one set of the fields are completed I still want that total time to in up in the the TotalRideTime field.

View 1 Replies View Related

General :: Finding Customer Who Spent The Most

Sep 2, 2014

I am working on a database at the moment to try and find the customer that has spent the most money. At the moment i can only get the customers that have placed the most orders. I have a dispatch table that consists of all the orders and a customer table that are linked using a Customer Number. Each order has a dispatch number because one customer can buy more than one product per order. Like i said at the start I'm trying to find the customer that has spent the most money but the top 10 would be better.

View 7 Replies View Related

Working With Time Fields

Mar 15, 2006

Can you calculated time in a query?
Such as hours during any given day?

View 2 Replies View Related

Working With Time Variables

Feb 21, 2008

I need to have my form give he user a Start time and then an End Time.

How do I store the variables (stirng, integer?)

How do I subtract the End Time from the Start Time to get a total time??

I then need to Multiple the resulting time frame by a Rate to calculate total value...

Any thoughts?

THanks

View 3 Replies View Related

Queries :: Calculate Difference Between Two Date Fields To Get Total Time

Oct 26, 2014

I have to create a query in access that will calculate two Date and time fields [Date & Time Left]/ [Date Returned], need to figure out between the two fields. Trying to identify when the rep returned the call and the number of business hours (6:00am - 4:30pm) it takes to return a message in Ms Access 2010.

The only issue is the calculation has to be done by time and so I have to calculate what time they left the message(so the difference between [Date & Time Left] and [Date Returned) [Date & Time Left] and when the rep returned the message which is suppose to be [Date Returned] but the problem with this field is the data entry is in date format (10/9/2014, 00/00/0000) of Date and not Date and Time like the [Date & Time Left] field, so I don't know what to do now. Not sure what to do now not a database that create or have allot of control over.

View 4 Replies View Related

Reports :: Summing Information On A Report - Total Percentage Of Time?

Apr 19, 2013

I have a question about summing information on a report. I am developing a report to see have percentage of time Staff work within a certain area. On the data base the time percentages entered as ranges e.g. 75-100%, or 11-25%. We needed to figure out the total percentage of time so I created a report to add the total time. I used a text box which it titled FTE_Total if embedded iif functions to display the total time. Here is the function I used:

=IIf([FTE_Percentage]="76-100%",1,IIf([FTE_Percentage]="51-75%",0.75,IIf([FTE_Percentage]
="26-50%",0.5,IIf([FTE_Percentage]="11-25%",0.25,IIf([FTE_Percentage]="1-10%",0.1,0))))).

This worked perfectly however I cannot get the FTE_Total fields to sum. I tried the following function in the group footer: =Sum([FTE_Total]) but everytime I try to look at the report in report view I get a message asking for the FTE_Total Parameter.

View 1 Replies View Related

Tables :: Date / Time Fields In Table - Subtract To Get Total

Oct 31, 2012

Any way of setting up a table containing the following date/time fields.

StartDate
StartTime
EndDate
EndTime

Ultimately I will need to be able to subtract these date/times to get a total time between the two. Should I combine the start date and times in one cell or keep them separate.

In either scenerio, how do I subtract the two in a query for a report?

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved