Time Keeping DB - Best Field Format For Working Out Hours Worked
Jun 18, 2006
Hi, I am creating a time keeping db.
Before I jump in and start creating a way to record times worked I thought it best to ask for advice.
The db is created and the last part is to capture by member:
Basically I have the following fields:
MemberID
Job Type
Date of Job
Time Started
Time Finished
From the data captured I need to create a report to show month and ytd hours worked.
It would be easy to ask the user to calculate the hours worked and input the number of hours, however I would like to capture start and finish times.
Can anyone give me any pointers on the best field types and field formats for capturing times which would then make it easier to work out hours worked.
Thanks in advance
Darrell.....
View Replies
ADVERTISEMENT
Jan 22, 2006
Hi Guys
I need to create a method of calculating -week number,hours worked per week and a leiu hours total.
Explanation
Users enter their worked hours per day. I need to calculate the total hours worked per user per week and if this is greater or less than their contracted hours a record of this figure over a continual time period (or year).
Variables :- Time worked,Holiday hours,Leiu hours taken,Sick leave, Other authorised leave.
I realise that i'm asking on a lot but working for a charity has financial limits.
Attached the basic database-so far
Thanks in advance for your help
Row
View 4 Replies
View Related
Aug 1, 2013
I have a field in my report [Idle]
This has a time in a HH:MM:SS format (so 01:38:23)
What I need to be able to do is sum this greater than 24 hours and keep the format of HH:MM:SS - ideally I need to do this in the form field that I'll be using to sum it.
View 1 Replies
View Related
Mar 25, 2008
I've run into a bug, I think. I have a time field with it set to show "Medium Time" which is "hh:mm(AM/PM)", however, when I combine the information with another field in a query (=[OfficeDay]&" - "&[POfficeTime]), the time comes out as "Long Time" or "hh:mm:ss(AM/PM)". End result is Wednesday - 10:30:00AM when it should be Wednesday - 10:30AMDoes anyone know a way around this?
View 1 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
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
Sep 1, 2013
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).
View 5 Replies
View Related
Nov 13, 2007
I am trying to subtract 12 hours from a time-date field when the payperiod does not equal 01.
in a query I have:
work date: iif([payperiod]="01",[StartTime],[starttime]-#12:00:00#) - Access added a PM before the second hash so it looks like this:
iif([payperiod]="01",[StartTime],[starttime]-#12:00:00 PM#)
When I run the query I get a message box: "syntax error (missing operator) in query expression '00:00:PM#,2))". I am unable to open the query to correct the error. I can cop the unaltered query from a back up database.
My question is how do I subtract the 12 hours.
Thanks
Steve
View 3 Replies
View Related
Feb 17, 2014
I wanted to assign the field "Number of magazine" with special format based on date/time format but showing only year and month in the format: "yyyy-mm".
So in property of this field in format I put yyyy-mm and in input mask I type 0000-00;;-
I also created the form based on the table containing above field and I defined format and input mask for corresponding formant in the same way like at the table.
But if I try to type date for example 2014-01 in text box of the form it comes up with the full date 2014-01-01. Why does it do like this? What do I do incorrectly?
View 2 Replies
View Related
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
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
May 5, 2006
Hi,
I have a form where you can enter the work you did in a day,
There are many users, each entering data for a single date.
What I want my form to do is, when a date and name is picked (from 2 boxes on the form) it will bring up the record relating to that person for the day.
If there is no record for that person on the day, this will be the first data the person enters.
So for example the form Hours:-
name mcbiyms2 (combo box) date 05/05/06 (text box:- =Date() )
Work Type 1 [ ] Time [ ]
Work Type 2 [ ] Time [ ]
So if somebody has already enetered some data for that day, it will bring this up to add too, if not it will be a new record to be entered.
Does anyone have any ideas?
View 1 Replies
View Related
Jan 21, 2015
how can i get Access to display a value as hours:minutes:seconds in a query when is a 24 hours or greater value, for example i have the value 1.00826388888889 if i were using Excel i would format it as [h]:mm:ss and it would output 24:11:54.
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
Oct 31, 2007
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.
View 2 Replies
View Related
Mar 16, 2008
Any ideas on calculating future dates based on # of working hours. For example... If a task is suppossed to be completed in 32 business hours, when would that be? Considerations include standard working hours M-F, no working hours on weekend. I have to believe that someone has written this before and I don't need to reinvent the wheel. ANy help is much appreciated!
View 5 Replies
View Related
Feb 2, 2014
I need to alter filed 24-gen-64, in 24/01/64... how to?
View 5 Replies
View Related
Feb 17, 2015
I have recently upgraded my Access 2003 application to Access 2010. I am running into a problem when formatting a textbox to 'Long Time' that is linked to a Double type field. The error that I get is 'The value you entered isn't valid for this field. You may have entered text in a numeric field or a number that is larger than the FieldSize setting permits.'
The textbox is linked to a field (double data type) and displays as the time in long format (4:06:17 PM). When I try to change to 4:07:17 PM I get the error. I tried removing the 'PM' and the colons but the error still occurs. The only way that I can get it to work is if I use a double value (0.735405092592593). In Access 2003 the time value would display properly, allow me to change the time and then save the time as a double to the database.
View 3 Replies
View Related
Feb 6, 2013
I have a series of fairly large csv files which I need to consolidate by importing them to a single Access database. There are of the order of 50-60,000 'lines' per csv and about a dozen different files (all comma de-limited and text-qualified with inverted commas).The problem I have is, there are several date fields within the csv which are not formatted to Access' liking. Namely <Mmm_dd_yyyy_hh:ssAM> with underscores used here to represent spaces.
So for example, today's date appears thus :
Feb 6 2013 12:00AM (i.e. two spaces between 'Feb' and '6')
And this day next week would appear thus :
Feb 13 2013 12:00AM (i.e. one space between 'Feb' and '13')
I will obviously need this field recognised as Date/Time so I can query appropriately but I have no control over the source data and need to find a way of converting the format.One option would be to run a script on the csv's themselves prior to importing but I'm not sure how to do that.import this field as Text and then edit the contents after import (VBA?), such that the field can be redesignated with a data type of Date/Time.
View 3 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
Apr 10, 2006
Looking to have a time field that a user can enter how long they worked an issue. Format should be ##:##, but I'm afraid that if I use text, calculations will be impossible.
How does someone do this if it's expected that the value will frequently go over 24 hours?
View 5 Replies
View Related
Mar 27, 2006
Any Help!
I have fields that are time formated 99/99/99" "00:00;0;_ and I want to subtract them.
I need to get the results in two forms; first in Days and second in Hours. I have used the obvious subtract one from the other and it gives me days in a rounded numbeer, but the hours is not working out.:confused:
DB
View 4 Replies
View Related
Jun 27, 2006
I want to create a query that will calculate the following:
When a user enters a start time and an end time in 2 seperate fields (eg. 06:00 and 08:00) I want to automatically return how many hours fall between 00:00 to 01:00, 01:00 to 02:00, 02:00 to 03:00 etc through the 24 hour clock.
To give you a background why I want this, I'm creating a labour planning database that calculates the nuber of people available each hour in a warehouse. So when a user inputs 06:00 to 08:00 for overtime work I need the database to tell me 1 hour falls between 06:00 to 07:00 and 1 hour between 07:00 to 08:00.
I'm really struggling to come up with a solution to this one, any help would be appreciated.
View 1 Replies
View Related
Mar 13, 2006
I created a time clock module where you input the time in and time out and it takes the difference of the two to give total time worked. However the boss wants the time worked represented in hours and tenths of hours not minutes. How can I take the time, seperate the hours...find out how many tenths of an hour worked and then add the hours and tenths together.
View 2 Replies
View Related
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
