B
brfender
Hi,
I am using Access 2003.
I have a table that includes completion dates and times (separate
fields) for steps in a job. Here is an example of a table (please
excuse if there is a line wrap):
JobID Area CurrLine Cmplt_DT Cmplt_TM PrevLine NextLine
1 ABC 1 8/1/2006 08:42 0 2
1 DEF 2 8/2/2006 15:36 1 3
1 DEF 3 8/5/2006 00:59 2 4
1 ABC 4 8/9/2006 13:24 3 0
2 XYZ 1 8/2/2006 10:09 0 2
2 XYZ 2 8/4/2006 10:15 1 3
2 XYZ 3 8/4/2006 15:25 2 0
As you can see, there are different areas for each JobID. The Jobs are
also separated into actual line items (CurrLine). In the table with
the Job information, there is a create date for the entire job.
What I am trying to do is figure the amount of time each area is in
work for each job. There are no start times for each line, so I would
have to use the previous lines completion information as the start.
For JobID 1, the first line is Line 1 (noted because the PrevLine is
0). I would calculate the amount of time for Area ABC from the JobID
start to the Line 1 Cmplt_DT and Cmplt_TM. The next Area DEF would
calculate from the Cmplt_DT and Cmplt_TM of the PrevLine (Line 1) to
the last consecutive line for that Area (Line 3). Finally, calculate
the time for Area ABC again in Line 4 based on the completion of Line 3
as the start and Cmplt_DT and Cmplt_TM of Line 4 as the finish. Line 4
is the last line of the Job based on 0 being the NextLine.
The time the Job used Area ABC is calculated 2 times because it was not
consecutive. The time for Area DEF is calculate 1 time because the
line items were consecutively in that Area.
I'm sure I've muddied the water on this, but I would be willing to
explain it more if someone would like to give it a shot.
TIA,
Ben
I am using Access 2003.
I have a table that includes completion dates and times (separate
fields) for steps in a job. Here is an example of a table (please
excuse if there is a line wrap):
JobID Area CurrLine Cmplt_DT Cmplt_TM PrevLine NextLine
1 ABC 1 8/1/2006 08:42 0 2
1 DEF 2 8/2/2006 15:36 1 3
1 DEF 3 8/5/2006 00:59 2 4
1 ABC 4 8/9/2006 13:24 3 0
2 XYZ 1 8/2/2006 10:09 0 2
2 XYZ 2 8/4/2006 10:15 1 3
2 XYZ 3 8/4/2006 15:25 2 0
As you can see, there are different areas for each JobID. The Jobs are
also separated into actual line items (CurrLine). In the table with
the Job information, there is a create date for the entire job.
What I am trying to do is figure the amount of time each area is in
work for each job. There are no start times for each line, so I would
have to use the previous lines completion information as the start.
For JobID 1, the first line is Line 1 (noted because the PrevLine is
0). I would calculate the amount of time for Area ABC from the JobID
start to the Line 1 Cmplt_DT and Cmplt_TM. The next Area DEF would
calculate from the Cmplt_DT and Cmplt_TM of the PrevLine (Line 1) to
the last consecutive line for that Area (Line 3). Finally, calculate
the time for Area ABC again in Line 4 based on the completion of Line 3
as the start and Cmplt_DT and Cmplt_TM of Line 4 as the finish. Line 4
is the last line of the Job based on 0 being the NextLine.
The time the Job used Area ABC is calculated 2 times because it was not
consecutive. The time for Area DEF is calculate 1 time because the
line items were consecutively in that Area.
I'm sure I've muddied the water on this, but I would be willing to
explain it more if someone would like to give it a shot.
TIA,
Ben