I have a table wherein the time worked by 25 employees are recorded. This table has the start time, endtime, break time and late times. The Late Time is the number of minutes that the employee is late to work. I am required to do a query of the team's (all 25 staff) late times per month. I have done a query which shows the late times of the individual on the dates that they were late to work and created a crosstab query for that.
I am going around in circles. How can I have a total of the team's late times in a query? Please, could someone please point me in the right direction?
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.
Basically I have a table setup where Time Fields are stored. What I need to do is in a Query, have the difference of two Time Fields calculated, and then displayed on a Report. Here's what I have so far...
All that works fine and shows up on the report, except for the fact that the time is showed in straight minutes, so for a difference of 1:30 minutes, it just shows 90 minutes. I need to make it so that on the report in the Difference Time Text Box, it's showed as 1:30 instead of 90.
I tried putting the following in the Control Source for the Difference Time Text Box on the report, but it returns an error.
=[Difference Time] 60 & Format([Difference Time] Mod 60, ":00")
Hello everyone, I am new to this forum, this is my first time. I hope someone can help me with my problem. I am creating a small database using Access at my work place. I came forward with 2 problems. 1. I need to create a field (Down Time) in a form that can store hours and minutes like 40:30. If I format the field as Short Date it wont allow me value higher than 23:59. Also, let you know that I am using the field (Down Time) in a calculation.i.e. Run Time - Down Time = Actual Time. 2.I also need a help on for the following calculation. Unit per Minute =Quantity(number field)/Actual Time. Your help is greatly appriciated.
Hi, I have a bit of a problem. I am making a program in access 2002. In my program you should punch in hours:minutes, this is for keeping track of how long a job has taken to perform. The problem is that it could exceed 23:59, it could take up to a week or even more. In my database we have already put in a lot of info where it doesn't exceed the 23:59, I would like to keep this posts and just to put in new ones that I could use further on when I am about to take out reports, where I have to calculate time. Is there someone who knows what to do ??
I have a query that uses DateDiff to give me Minutes of an in and out time.Online I found the following to use in a textbox control source on a report to convert the minutes to Hours:Minutes.
=[Minutes] 60 & Format([Minutes] Mod 60, ":00")
But when I enter this it gives me an error. I am not sure how to fix it. I tried writing it like this with RegHours being the source of that column
=[RegHours]60& Format([RegHours] mod 60, ":00")
but when I go back to report view that text box then says #Type!
I have a tracking database I have been working on and so close of being done just held up on this last part. What I am trying to do is have the totals = Ex. 1.25 + 3.50 would give Grand total of 5.15 not 4.75. The table has a field called time spent which stores the time. I have attached an example database. So whenever the minutes = more then 60 for it to add 1 to the hour.
I have a table of timecards each recording minutes spent on each task for each client and I have summed those minutes by client in a query.I have then in the same query converted each sum of minutes to hrs:mins format using this calculated field:
HrsMins: [SumOfMinTime]60 & Format([SumOfMinTime] Mod 60,":00")
So this turns e.g. 261 minutes into 4:21..Based on that query I have a report showing total hrs:mins spent on each client, e.g.
Smith 4:21 Jones 5:32 James 1:23
Now I want to show a total at the bottom, i.e. in the above example it would be 11:16.So for the control source for a total field on the report I tried:
=Sum([HrsMins])
But when I do that I get an error message: "Data type mismatch in critieria expression"I may be because I am trying to sum calculated fields amounts that are calculated using a function.Or it may be that the format is not being recognised as hours and minutes and thus cannot be added up.way in which I could get a total here in hours and minutes format (hrs:mins).
I am using the calender control 11.0. By this calender I can only set the date year, month and day. I can not set the clock time:Hours, minutes and seconds. Is there a calender that enables me to set the date in the format:dd-mm-yyyy HH:MM:SS, means day,month,year Hour:Minutes:Seconds.
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").
I need to create a derived attributes (total hours left, total hours used).
In order for the calculation I have days_allocated in the employee table (this is converted into hours by multiplying this by 24).
How can I create a field in a query that works out the amount of hours used and left?
Here is a typical holiday record (screenshot of my query that I am trying to do)
http://img3.freeimagehosting.net/uploads/th.8af9f3cd25.jpg (http://img3.freeimagehosting.net/image.php?8af9f3cd25.jpg) Can someone please help me create a formula.
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".
Hello, i have a table with the fields: "employee_number", "shiftdate" "department" "basic_hours", and "over_time"
And a query named "employee_hrs" looks at the fields in this table, i would like a new field in the query to be able to calculate the total hours for all employees together but for each department.
eg the total of: basic hours + overtime for department A) the total of: basic hours + overtime for department B) and so on for each department
would only like to show one instance of a department (distinct) with the total hours done by all employees for that department?, if any one could please help me out that would be great!?
I have a problem with Microsoft access 2003. I want to design a Check in/Check out Time Data base for my university final term project. In this data base I need to show the total of the working hours of each personnel in a month.( for instance: 124 hours and 55 minutes in one month). But adding up the hours, when the time variable reaches 24 then time automatically goes back to 01:00. As a result, the correct total hours cannot be calculated....
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;
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.
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).
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..
Can anyone tell me if there is a way of displaying a number, which represents the number of minutes of elapsed time, into the format days:hours:minutes elsapsed?
For instance the number (in minutes) 1530 would display as = 01:01:30. 1 day, 1 hour & 30 minutes of elapsed time.
