Queries :: Time Sheet / Payroll Database - Calculating Total Daily Hours
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 Replies
ADVERTISEMENT
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
Mar 8, 2008
Hi,
1) I am pretty newbie to this access programming, do forgive me if my questions sounds stupid.
2) Basically I create an application in access capturing or production information for my company. now the top management suddenly wanted whats their main concern:- Total Daily/Monthly, Quarterly, Annual Sales (By Model If possible)
3) I start with daily (Lets don't be too overly ambitious).
4) I try to let user select dates from my calender control and reflect daily sales (in Total & By Model break down) insert into my form.
5) Understand someone told me from my previous post in Calender control I can achieve it either through forms or queries, which is a better way. (in terms of flexibility to change for program maintenance/ scalibility) wise ?
PS: Please forgive my ignorance :o:(
Thanks (In advance) & God Bless.
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
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
Jan 21, 2015
I don't use Access too often but I'm trying to connect a table to some business intelligence software I use.
A. Date B. Price C.Ydayprice
01/01/2015 101.45
02/01/2015 104.70
03/01/2015 103.00
Simple stuff. Once I've connected to the table, I can easily do what I want with Field 'Price' and 'Ydayprice'. However, I can't calculate column C. All I want is row 2 to say 101.45, R3 to say 104.7 etc. I've tried various things but I don't have the knowledge to write the action I require. I don't want to calculate the change in Access either.
View 3 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
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
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 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
Jun 5, 2013
Query of daily activities spent hours
1) to be group as weekly total
2) then need to transpose it for reporting.
My table fields are Date, SpentHrs and Code (activity code description).
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
May 21, 2012
I am building an attendance database. The attendance data will be inputted at a daily level. That is working fine and the hours are calculating correctly. The next thing I need to do is create a Table (maybe) that will show the total hours worked in a week. The purpose of this, and the reason I don't want a report, is that the employee will accumulate vacation based upon the numbers of hours worked per week. I will want to be able to show a running total on the vacation, and will eventually create something to allow them to deduct from those vacation hours when they are used.
View 2 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 16, 2014
I am using the following UNION QUERY to total up equipment tested for a report.
SELECT "Laptops Tested" AS PCEQUIP, Count(*) AS RECORDS
FROM [LAPTOPS]
WHERE (((Date)Between [Enter Start Date] AND [Enter End Date]));
UNION
SELECT "Workstations Tested" AS PCEQUIP, Count(*) AS RECORDS
FROM [WORKSTATION]
[code]...
I have created a report using ACCESS 2000 for this union query and it satisfies the requirement. I am trying to add the proper code and syntax in this query to total the number of all of this equipment tested.In this case the total would be 86. Is this possible?
View 2 Replies
View Related
Aug 5, 2014
I am trying to create a running total query that aggregates project funding by fiscal year. The fiscal year is calculated based on a date time field that is never null. The totals field comes from 2 different number fields that are either 0 or > 0. The query is going to be linked to by Excel, so I have to do the running total in the query itself, vs. a report.It is close to working, except that it is not totalling the first fiscal year. The output surrently looks like this:
FYear BudgetedCostIndCont Commitment
2010
2011 8585643 4742000 3843643
2012 2297116511432165 11539000
2013 3618726216963282 19223980
2014 4457769020706644 23871046
2015 4963815023206644 26431506
As you can see, the first row for FY 2010 is blank. I know there is data there, as this query is fed by a subquery that selects these rows based on contract signed date. Below is the SQL of each query:
Code:
SELECT Year([DateContractSigned])-IIf([DateContractSigned]<DateSerial(Year([DateContractSigned]),4,1),1,0)+1
AS FYearExport
FROM tblProject
GROUP BY Year([DateContractSigned])-IIf([DateContractSigned]<DateSerial(Year([DateContractSigned]),4,1),1,0)+1, tblProject.ProjID, tblProject.FPAccepted
HAVING (((tblProject.FPAccepted)=True));
and the Aggregate query:
Code:
SELECT qryDashboardChart1.FYearExport,
DSum("[BudgetedCost]","tblProject","Year([DateContractSigned])<=" & [FyearExport]-1 & "")
AS RunTotBudgetedCost, ([RunTotBudgetedCost]-[RunTotTECTERRACommitment])
[code]....
I should also mention that I cannot implement the NZ() function, as Excel balks at this when trying to link to Access queries.
View 8 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
Jul 7, 2006
Hello all. I hope someone can lend a helping hand here. I am trying to create a query that will calculate a 24 hour value for every day of a month. For a month a go out a read meters. I need to get the difference of two readings by subtracting the first day from the second day and so on until I get to the end of the month. So, Day2-Day1=24 hour value, Day3-Day2, Day4- Day3... I have this in a spreadsheet but can't seem to grasp it for a query in a database. I would appreciate any help. Thanks.:confused: :D
View 1 Replies
View Related
Sep 7, 2006
Hi,
I have a database, in which I need to add up the total number of entries made at any point of the day. I have started by creating a query with the entry's SerialID (unique) and the date it was entered, however I am stuck already.
Any help would be appreciated,
Ben
View 12 Replies
View Related
Jan 3, 2007
I have the following feilds in a query: Timein Timeout
I need a subtotal feild that totals the two across midnight
I also need a Lunch feild
Then I need a total feild with the subtotal and total subtracted
I tried using this expression please help anyone if you can
Format([Timein] -1 -[Timeout], "Short Time") this created my subtotal feild
then I had a made lunch feild with 30 mins of time into it
then i tried Format([subtotal] -1 -[lunch], "Short Time") and called this one my total box.
The first one worked the second reported an error. Please any suggestions??
View 3 Replies
View Related
Dec 14, 2013
I run a Pass through query against a SQL-Server from Access 2010.I have a field TimeWorked which has values like 1899-12-30 03:30:00.000, for 3:30 hours and a field PaymentPerHour with values like 10.50 (for EUR 10,50).The result schould be 36,75 (36.75).
I tried to calculate like this: Round(convert(varchar,[TimeWorked],104)*1440*[PaymentPerHour ]/60,2)AS TtlPayment
In an Access query it works with the connected table in the accb, but not against the server.I get a lot different errors when trying to convert into int, datetime, etc. but could not solve my problem.
View 3 Replies
View Related
Jan 8, 2015
I am using access 2013 and have an issue creating a query with employee hours for a two week period.
For starters I have a table with the following:
Employee Number, Date, Time in, Time out, Description, Rate Code, Week end, Pay Period. (there are a couple others but they are not needed for the query)
My table is set up this way so that each employee can clock in and out 1-4 times a day based on what they are doing. For example I may work from 6-8 in the office then 8-10 in the field. The office has one rate code and the field has a different one so i cant just be clocked in from 6-10.
At the end of the pay period I create a report and send it to an accouting firm who does the payroll. Right now I have to manually enter in each employees time. I was hoping that by using this table I could create a query that will give me the calculations I need in order to create the report.
In my query (to Start) I need to have the following with it set up per pay period.
Employee Number Rate Code Regular Hours Overtime Hours
How to set it up so that it calculates the hours based per rate code with the given pay period.
View 11 Replies
View Related
Jun 22, 2006
I have four date/time fields and I need to determine the number of days and the number of hours between them based on report date range. The Days need to be in one field and the hours in another field. I have tried DateDiff function with an IIF condition and get the following negative value in Expr1. If I remove the IIF condition then I get a positive value.
If the date in field 1 is less than field 3, the days and hours need to be calculated field 2- field 3 as long as field 2 is less than or equal to field 4. If field 2 is null and if the date in field 1 is less than field 3, the days and hours need to be calculated field 2-3 but if field 1 is less than field 3 the calculation would be field 4- field 1. When both dates are within the report range then DateDiff works fine.
Adm Date [/U](f1) D/C Date/Time (f2) Beg Report (f3) End Report (f4) Expr1
I could e-mail the table with sample dates if this would help
Can anyone assist me with this formula?????:mad:
View 2 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
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
Mar 11, 2008
Hi All,
me again, now that I've got over the working hours hurdle I'd like to sum by week.
I'm quite new to access so please excuse my simple questions.
My fields are:
LogID {EmployeeID&Now()}
EmployeeName
StartTime {date&time}
FinishTime {date&time}
I have created a query to give me the total hours worked per day. Is there anyway I can total this by week?:confused:
Once again your help is greatly appreciated
View 9 Replies
View Related
