Adding Time

[jlclark4]

Hey, I've been stuck on this for days!


I have a formula set up in text boxes:

=Format([w_start]-1-[w_end],"Short Time")

To determine total time worked.

I need those to be able to add or subtract those text boxes from each other. I would like to have a query that will just calculates the total paid time.

Work Time (above equation) - Lunch - Break 1 - Break 2

Is this possible?

Please note that when I use the equation, the format is 00:00 which is where I am running into a issue...

[jzwp11]

Access stores dates/times as a number with the integer portion representing the number of days since 12/31/1899 and the decimal portion representing the fraction of a day. Basically you have to do all of the calculations before formatting. But looking at your previous post that included an attached database, you have a normalization issue that needs to be corrected first. Since a worker can have many start and end date/times event (whether it is for starting work, taking a break, taking a lunch etc.) that describes a one-to-many relationship. It would be best to handle each time transaction as a record in a related table


tblEmployee
-pkEmpID primary key autonumber
-txtFName
-txtLName


tblEmployeeTimeTransactions
-pkTransID primary key, autonumber
-fkEmpID foreign key to tblEmployee
-dteTrans (date/time of transaction)
-fkTransEventID a field to indicate what the transaction is for such as clocking in for the work day, clocking out for lunch etc.; foreign key to tblTransEvents


tblTransEvents
-pkTransEventID primary key, autonumber
-txtTransEvent

[jlclark4]

I'm not sure I understand completely?

Can you provide me with an example?

[jzwp11]

I've attached a sample database that illustrates the structure I proposed. I did add 1 additional field to the events type table that distinguishes a clock IN event from a clock OUT event. I included a base form/subform for data entry as well as a series of queries that shows how to bring the start and stop times together. qryStartTimes shows all of the applicable start times while qryEndTimes show all of the applicable end times. I then bring those queries together in qryHoursWorkedDetail to show how the time worked for each interval is calculated. I also show the time worked in the 00:00 format. I then provided a summary of time worked for each employee for each day (qrySumOfHoursWorkedByDayForEachEmployee) and another summary query showing the 00:00 format.

[jlclark4]

Thanks for the sample. I am more over a visual person. Do you know how to take the scroll function off the mouse in the databases? I want to incorperate a search function by Employee ID but I do not want people to be able to scroll into other records even if I remove the naviagation bar...

[jzwp11]

One easy way is to use an unbound form where you input the employeeID and then open the form I provided early to that specific record. I've modified the early database with this new form & attached it. I put 1 textbox control (for the ID) and then used the command button wizard to open the frmEmployee to the specific record that had the same ID. With this approach, the user will only see the record for the particular employeeID that they entered into the textbox.

As to stopping the scrolling with the mouse. That is not something with which I am familiar, sorry.

[jlclark4]

This sample works for this project. But I will continue to research the scrolling function for other projects I have. The only tips/hints ive gotten deals with computer settings which I do not want to mess with. If I figure it out, I'll create a new post and post the solution!

Thanks for the help.

[jzwp11]

Sounds like a good plan and thank you!