Record to record calculations

[Dave S]

I am creating a small database to collect labor information on a job by job
basis. I will have the operators logging information into the database using
a barcode scanner. The information collected will be: Operator ID, Job
Number, Status (i.e., start or end). The input form will automatically add
the date/time that the information was entered into a seperate field. That
way it will be like a starting and ending "punch" on a time clock.

I can easily select records from a specific Job Number in a query. What I
am having trouble with is how to calculate an elapsed time based on the time
stamps in the filed of different records in the database.

In it's most simple form a job would have only two "punches" which would
create two records in the database. These two records would be the time that
work on the job was started and the time that work on the job was ended. I
need to be able to calculate the amount of time spent on that job.

The only variation from this would be if multiple "punches" would take place
on any one job. This is a very likely situation because of break times,
lunches, and jobs that may span several days or have multiple operators
working on the same job.

Can someone help me figure out how to do these calculations please??

Thanks

 

[Allen Browne]

Use a subquery to get the previous matching value for punchouts.

1. Create a query that returns all the end types, i.e. add the Critiera
under the Status field for end only.

2. In a fresh column in the Field row, type a subquery to get the matching
start time like this:
StartDateTime: CVDate((SELECT TOP 1 [PunchDateTime]
FROM [Table1] AS Dupe
WHERE ((Dupe.[Operator ID] = [Table1].[Operator ID])
AND (Dupe.[Job Number] = [Table1].[Job Number)
AND (Dupe.[Status] = "start")
AND (Dupe.[PunchDateTime] < [Table1].[PunchDateTime]))
ORDER BY Table1.[PunchDateTime] DESC, Table1.[ID]))

Replace Table1 with the name of your table, and PunchDateTime with the name
of your date/time field. Since we are using 2 copies of the same table, we
used the alias Dupe: you don't need to change that name. You don't add a 2nd
copy of the table to the query.

Once you have the matching date/time from the starting record, it's then
straightforward to calculate the difference using DateDiff(). For help here,
see:
Calculating elapsed time
at:
http://allenbrowne.com/casu-13.html

If subqueries are new, see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066

 

[Dave S]

Allen,

This works fine if there are only two records in the database for each work
order (i.e., one start and one end). However, most of the time there will be
a fair number of entries for each work order. This indicates one or multiple
operators "punching in" and "punching out" from a work order throughout the
day. The sub-query approach that you show below produces an error message if
you try to apply it on a query that returns more than two records from the
database. Any further suggestions???

Thanks,
Dave

Use a subquery to get the previous matching value for punchouts.

1. Create a query that returns all the end types, i.e. add the Critiera
under the Status field for end only.

2. In a fresh column in the Field row, type a subquery to get the matching
start time like this:
StartDateTime: CVDate((SELECT TOP 1 [PunchDateTime]
FROM [Table1] AS Dupe
WHERE ((Dupe.[Operator ID] = [Table1].[Operator ID])
AND (Dupe.[Job Number] = [Table1].[Job Number)
AND (Dupe.[Status] = "start")
AND (Dupe.[PunchDateTime] < [Table1].[PunchDateTime]))
ORDER BY Table1.[PunchDateTime] DESC, Table1.[ID]))

Replace Table1 with the name of your table, and PunchDateTime with the name
of your date/time field. Since we are using 2 copies of the same table, we
used the alias Dupe: you don't need to change that name. You don't add a 2nd
copy of the table to the query.

Once you have the matching date/time from the starting record, it's then
straightforward to calculate the difference using DateDiff(). For help here,
see:
Calculating elapsed time
at:
http://allenbrowne.com/casu-13.html

If subqueries are new, see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I am creating a small database to collect labor information on a job by job
basis. I will have the operators logging information into the database
using
a barcode scanner. The information collected will be: Operator ID, Job
Number, Status (i.e., start or end). The input form will automatically
add
the date/time that the information was entered into a seperate field.
That
way it will be like a starting and ending "punch" on a time clock.

I can easily select records from a specific Job Number in a query. What I
am having trouble with is how to calculate an elapsed time based on the
time
stamps in the filed of different records in the database.

In it's most simple form a job would have only two "punches" which would
create two records in the database. These two records would be the time
that
work on the job was started and the time that work on the job was ended.
I
need to be able to calculate the amount of time spent on that job.

The only variation from this would be if multiple "punches" would take
place
on any one job. This is a very likely situation because of break times,
lunches, and jobs that may span several days or have multiple operators
working on the same job.

Can someone help me figure out how to do these calculations please??

Thanks

 

[Allen Browne]

The subquery uses TOP 1, so that it only generates a single record.

In Access, a TOP 1 record can still give you duplicates if there is a tie.
To avoid that you must give Access a way to choose between equals. One way
to do that is to add the primary key of the table to the ORDER BY clause of
the subquery. Since the primary key is unique, this guarantees the subquery
does not generate the "At most, one record can be returned" error.

That's why the ORDER BY clause in the example includes the primary key
field.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Allen,

This works fine if there are only two records in the database for each
work
order (i.e., one start and one end). However, most of the time there will
be
a fair number of entries for each work order. This indicates one or
multiple
operators "punching in" and "punching out" from a work order throughout
the
day. The sub-query approach that you show below produces an error message
if
you try to apply it on a query that returns more than two records from the
database. Any further suggestions???

Thanks,
Dave

Use a subquery to get the previous matching value for punchouts.

1. Create a query that returns all the end types, i.e. add the Critiera
under the Status field for end only.

2. In a fresh column in the Field row, type a subquery to get the
matching
start time like this:
StartDateTime: CVDate((SELECT TOP 1 [PunchDateTime]
FROM [Table1] AS Dupe
WHERE ((Dupe.[Operator ID] = [Table1].[Operator ID])
AND (Dupe.[Job Number] = [Table1].[Job Number)
AND (Dupe.[Status] = "start")
AND (Dupe.[PunchDateTime] < [Table1].[PunchDateTime]))
ORDER BY Table1.[PunchDateTime] DESC, Table1.[ID]))

Replace Table1 with the name of your table, and PunchDateTime with the
name
of your date/time field. Since we are using 2 copies of the same table,
we
used the alias Dupe: you don't need to change that name. You don't add a
2nd
copy of the table to the query.

Once you have the matching date/time from the starting record, it's then
straightforward to calculate the difference using DateDiff(). For help
here,
see:
Calculating elapsed time
at:
http://allenbrowne.com/casu-13.html

If subqueries are new, see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066

I am creating a small database to collect labor information on a job by
job
basis. I will have the operators logging information into the database
using
a barcode scanner. The information collected will be: Operator ID,
Job
Number, Status (i.e., start or end). The input form will automatically
add
the date/time that the information was entered into a seperate field.
That
way it will be like a starting and ending "punch" on a time clock.

I can easily select records from a specific Job Number in a query.
What I
am having trouble with is how to calculate an elapsed time based on the
time
stamps in the filed of different records in the database.

In it's most simple form a job would have only two "punches" which
would
create two records in the database. These two records would be the
time
that
work on the job was started and the time that work on the job was
ended.
I
need to be able to calculate the amount of time spent on that job.

The only variation from this would be if multiple "punches" would take
place
on any one job. This is a very likely situation because of break
times,
lunches, and jobs that may span several days or have multiple operators
working on the same job.

 

[Allen Browne]

Dave, there's more details in these new article explaining subqueries.

The first one explains the basics:
http://allenbrowne.com/subquery-01.html

The second one give trouble-shooting tips:
http://allenbrowne.com/subquery-02.html