Access time expression

Question

I need help writing an expression in Microsoft access. One column if hours (amount of hours/minutes used in the labor, they are in decimal format {example 2.15}). The other column is the number of people it took to complete the task. The goal is having a running time it took to do a task. If I could get one that in hours times amount of people plus the previous entry in the query that would be wonderful, but I am not sure that is possible. Thank you.

Answer 1

You can return the time spent to date over the whole of the query's result table with a query like this:

SELECT Status, [Release Date], Ticket, [Qty Scrap], (Qty Good], Labor, [# of People],

    (SELECT SUM(Labor*[# of People])

     FROM [Tracking Log Query] AS Q2

     WHERE Q2.[Release Date] <= Q1.[Release Date]) AS TimeSpentToDate

FROM [Tracking Log Query] AS Q1

ORDER BY [Release Date];

This would compute the time spent to date in Release Date order, but as your query returns the rows in Ticket order, you might want it to return the running sum of time spent in that order in which case the query would be:

SELECT Status, [Release Date], Ticket, [Qty Scrap], (Qty Good], Labor, [# of People],

    (SELECT SUM(Labor*[# of People])

     FROM [Tracking Log Query] AS Q2

     WHERE Q2.Ticket <= Q1.Ticket) AS TimeSpentToTicket

FROM [Tracking Log Query] AS Q1

ORDER BY Ticket;

However, I would normally have expected a query like yours to include a JobID volume or similar to identify each subset of rows per job, which would enable the subquery to be further correlated with the outer query, and this return the time to date spent per job.

There are other ways to return balances (running sums), examples of which you'll find in Balances.zip in my public databases folder at:

https://1drv.ms/f/c/44cc60d7fea42912/EhIppP7XYMwggESpAAAAAAABaDKZCllSuweYBPJ5zKa3cg

This little demo file illustrates a number of queries to return balances in different contexts.

You might also like to take a look at TimeArithmetic.zip in the same OneDrive folder.  This illustrates the use of a number of functions by which arithmetic can be done on values of Date/Time data type rather than hours.

Answer 2

Yes, to both. I am trying to get a running total of how long it takes to do rework. So, it is supposed to be the number of people multiplied by the length of time it took. Some jobs take 15 minutes to 13 hours.

For example, one job takes a group of 2 people 15 minutes to complete so it needs to reflect 30 minutes.

Another job could take 1 associate 1 hour to complete so that would need to reflect just an hour.

If I could get it to be a running total to where it continuously added the column with the 30 minutes plus the 1 and so on, that would be wonderful.

If I cannot achieve that, then having it just do the multiplication would be sufficient.

Thank you

Answer 3

I am hesitant on sharing the file since it is work related. however, I can share snips of what I have. Hopefully, that will help.

The part I am trying to get a running total of is highlighted.

The labor hours times the number of people.

Answer 4

Some records with significant data and expected output would be nice. Please include table and field names.

It sound like you want to create a running sum in a query. Have you search for a solution on the web?

Answer 5

If you post the file via a data sharing site, like FileTransfer, it could be seen what you have done because from what you say, one column ... the other column, it does not seem to me that it has entered the concept of database but remained in Excel.

What you ask should not be difficult to do via a query.

2.15 means two hours and a quarter?