I am trying to bring together two pieces of information, about the response time for a group of work orders.
In one query, I have a list of work orders, each record of which has a Creation date/time and a Start date/time. Each record also has a total number of minutes between from the Creation to the Start ("TTSMinutes").
I want to strip out non-working minutes that are available between the two dates.
I have already figured out how to calculate the non-working minutes for the specific days the work orders were created, and started. However, I can't get the number of minutes to strip out during the *intervening* days.
I imagine I need some sort of loop, or counter, that (1) looks at all records of a calendar table (which I have -- it has the total minutes for each day of the year that can be stripped out, "StripOutMinutes"); then (2) starts at the day after the Creation date, and continues on until it finds the day before the Start date; then (3) sums all the "StripOutMinutes" for those dates; and finally (4) drops the calculated sum into one cell in the original work order record.
Ugh. That description must be impossible to understand. If anyone has directions for an Access simpleton on using a counter code, that'd be great.
Thanks,
jh
In one query, I have a list of work orders, each record of which has a Creation date/time and a Start date/time. Each record also has a total number of minutes between from the Creation to the Start ("TTSMinutes").
I want to strip out non-working minutes that are available between the two dates.
I have already figured out how to calculate the non-working minutes for the specific days the work orders were created, and started. However, I can't get the number of minutes to strip out during the *intervening* days.
I imagine I need some sort of loop, or counter, that (1) looks at all records of a calendar table (which I have -- it has the total minutes for each day of the year that can be stripped out, "StripOutMinutes"); then (2) starts at the day after the Creation date, and continues on until it finds the day before the Start date; then (3) sums all the "StripOutMinutes" for those dates; and finally (4) drops the calculated sum into one cell in the original work order record.
Ugh. That description must be impossible to understand. If anyone has directions for an Access simpleton on using a counter code, that'd be great.
Thanks,
jh
