Grabbing info for a particular time interval

[radguy]

How can I create a query to only gather information from a particular time interval? Example... I would like to create a query that pulls information from 6 am to 2 pm, from Monday to Friday.

[June7]

Applying filter for time period can be tricky. Will period cross midnight? Do you want to limit the records to a specific week/month/year?

[radguy]

I am working on a project where I need to accurately track the time (in hours) it takes someone to perform a particular task. They will only be performing the tasks from 6 am to 2 pm, Monday through Friday. The problem that I am coming across is what if they task is started one day but do not finish it that same day. Ex. They start performing the task at 1:30 pm and they cannot finish it before 2 pm. They need to wait until the next day to finish the task. They next day can be a weekday or a Monday after a weekend. They also will not start the tasks right away at 6 am once they come in. They exact time they start the task again varies on the day to day basis but I would like to have a way to accurately track when they are starting and how long it takes to perform a task

So far I have 2 fields. One that tracks the time they have started the activity and another for the time they have completed the activity. But it does not account for the activity being partially complete.

I am open to ANY suggestions! Is there a better way to approach this? I am really stuck on how to do this in Microsoft Access.

Attached are 2 photos of the current tables I have to do this?

[azhar2006]

using the DateDiff() function to calculate "elapsed" time between two time values and the ending time value spans past midnight .
http://office.microsoft.com/en-us/ac...811.aspx?CTT=1

Use something like this
Int(Sum(DateDiff("n",[startTime],[endtime])/60)) & ":" & Format(Sum(DateDiff("n",[startTime],[endtime])) Mod 60,"00")
I think the professor (June7 )
Will comment on the subject more accurately

[John_G]

Hi -

I think you may need another table here, to record each interval of work on a task. The total time spent on a task would be the sum of all of the intervals for that task. The way you have it now, I don't see a way of getting what you need. The start time and Completed time can be kept as they are important, but you will need another field to indicate how many hours were actually spent on the task.

The tricky part will be in capturing the data accurately - you need to ensure that the individual Start and End times are entered properly, but how you do that depends on how your database and business process works.

HTH

John

[June7]

With this structure, an activity can have more than one start/end record. Need some way to associate the records. This means each of the related records must have a common identifier that can be used to group them for summation. One option is with two tables. A 'parent' table for the activity the user is conducting and then a related 'child' table for the time records. Use form/subform arrangement for data entry. The parent table/form will generate the unique identifier (autonumber type field as primary key) that will be saved as foreign key in the child table/subform.

[radguy]

How do I find the difference (in Hours) between the start and end time? Is there a way to do that in access?

Also, Is it possible to create an If statement that says:

IF(cell with total time in it>8hrs, then (cell with total time in it-8hrs), else, cell with total time in its)

Can I grab this info with a query or would I have to do some coding?

[June7]

azhar has shown you an expression in post 4 that will calculate the difference between two time values and display the result as MM:SS. However, I suggest you should not display as MM:SS as that is text and cannot sum text. Calculate the difference in decimal minutes so the results can be summed. Search Access help on DateDiff() and other date/time manipulation functions.

Use expression in a query. Access help has info on building queries and using expressions in query to construct fields. This is really basic Access functionality.

Again, your data structure will not facilitate summing related records.