Time Deducted From matching time frame???

  • Thread starter Thread starter njhildebrand
  • Start date Start date
N

njhildebrand

HELP!
(Please)
First, let me explain. I need to enter 3 time ranges that make up a
work schedule, StartupTime, productionTime, ReconcileTime. (Lets call
this worktime for clarity)
During one of the above time ranges, I could have up to 10 instances
where I can not work, (ex: break, lunch, product problem, etc) Those
times are logged (By Code#)throughout any given work schedule. (Lets
call this non-working time exceptionTime)
How can I deduct each Exceptiontime from the matching workTime range??

Is this possible?

I already have the code that converts the start and stop time into
hours and minutes, I just need to deduct any exception time that
happened during StartupTime, ProductionTime or ReconcileTime.
 
Do you have a column in your lookup table of "By Code#" that tells you which
codes to sum and which codes to ignore?
 
Duane,
I do have a lookup table that shows the different codes that are
considered exceptions. The issue is having the time range associated
with the codes deduct time from the time range associated with the
actual startup, production or reconcile time.
For each production record, there is only one startup, production and
reconcile time, but there could be many exceptions. So I have a table
to capture just the exceptions which should be deducted from the
production time. (Make sense?)

Norma
Struggling DBA
 
You don't say how you "enter time ranges". Do you have some table and field
names as well as sample records and desired calculations?
 
I have three tables.
One called Exceptions with 2 fields, [codeNumber] and [Description];

One called ProductionRun with three six time fields: [StartUpBeg],
[StartupEnd], [ProductionStart], [ProductionEnd],[ReconcileStart] &
[ReconcileEnd]; and various production data fields such as [Date],
[Line#], [LineLeader], [ProductID], & [LotCode]

One called ExceptionData with [ExceptionCode](lookup from
ExceptionTable), [ExcepStartTime], [ExcepEndTime], [LineLeader],
[LotCode], & [ProductID]

I have the ExceptionData table a subform in the ProductionRun with
master and child fields being [LineLeader], [ProductID], & [LotCode]

I want all exception time entries to be deducted from the matching
ProductionRun time.

Does that help understand what I need?

Thanks,

Norma
 
You used a subform for exception records so use a subreport for the same
data. Use [LineLeader], [ProductID], & [LotCode] as the Link Master/Child
properties for the subreport.
 
My problem is:
I am not sure how to deduct an Exception Time range from one of three
ranges between six time fields: [StartUpBeg],
[StartupEnd], [ProductionStart], [ProductionEnd],[ReconcileStar­t] &
[ReconcileEnd];
Also:
There could be many exception times deducted from one of the three
ranges for a given production run. Am I making sense?
I am thinking this is done with a query with multiple "or" criterias
but I am not quite sure how to structure it.

Thanks,
Norma
 
I would first normalize your table
[Date], [Line#], [LineLeader], [ProductID], [LotCode],
[StartUpBeg] as BegTime ,[StartupEnd] as EndTime, "StartUp" As Event
FROM ProductionRun
UNION ALL
[Date], [Line#], [LineLeader], [ProductID], [LotCode],
[ProductionStart] ,[ProductionEnd], "Production"
FROM ProductionRun
UNION ALL
[Date], [Line#], [LineLeader], [ProductID], [LotCode],
[ReconcileStart] ,[ReconcileEnd], "Reconcile"
FROM ProductionRun;

You can then use the exception start and end times to see which "event" it
occurs during.
--
Duane Hookom
MS Access MVP


My problem is:
I am not sure how to deduct an Exception Time range from one of three
ranges between six time fields: [StartUpBeg],
[StartupEnd], [ProductionStart], [ProductionEnd],[ReconcileStar­t] &
[ReconcileEnd];
Also:
There could be many exception times deducted from one of the three
ranges for a given production run. Am I making sense?
I am thinking this is done with a query with multiple "or" criterias
but I am not quite sure how to structure it.

Thanks,
Norma
 
ok, this is what I have in my Union Query:
SELECT [Date],[Line#],[LineLeader],[UPC],[Lot#],[StartupStartTime] as
StartBegTime,[StartupEndTime] as StartEndTime, "StartUp" as event
FROM DailyProduction WHERE [StartupStartTime] is Not Null
UNION ALL
Select [Date],[Line#],[LineLeader],[UPC],[Lot#],[ProdStartTime] as
ProdStart,[ProdEndTime] as ProducEndTime, "Production" as event
FROM DailyProduction WHERE [ProdStartTime] is Not Null
UNION ALL Select
[Date],[Line#],[LineLeader],[UPC],[Lot#],[ReconStartTime] as
RecBegTime,[ReconEndTime] as RecEndTime, "Reconcile" as event
FROM DailyProduction
WHERE [ReconStartTime] is Not Null;

The reasoning for the Not null is because we have 2 years of production
records and the start and end times for each is new last month.
When I run this query, I get records that shows the event column with
either "startup" "production" or "Reconcile". I can only see the
startupBeg and startupEnd that all have a time in it but what is
telling it to put one of the above named events in the event column?
Also, Any of the three time fields can be null, Ex: someone just
reconciled a line.
How do I go about comparing an exception occurance in what event?

You have helped out a lot so far, I just need spoon feeding it seems...
 
First of all, your union query only needs the " As ...." in the first
SELECT. You missed this from my previous posting.

Next question, do your time ranges from begin to end in a record ever
overlap any other record? For instance on a particular date and line, will
you have on event start before another event has finished?

Also, are there any gaps between times that might have an except occur
during that gap?
 
I corrected the 2 other "As" from my code.
No - to the question regarding any overlap in times. The entire time
that the line is running (if all three line tasks are completed) is
from StartUpStartTime to ReconEndTime.
But any or all of the three line tasks could occur. Such as a Line was
just Reconciled, or Started up for the next shift.

No - to the question about gaps.

Just as an FYI, each production run has 3 fields that make it unique,
LineLeader, UPC, & Line#.

Thanks,
Norma
 
You should be able to add the union query and your exception table and join
the appropriate 3 fields. Then set a criteria under the start and end time
of the exception to the values of the start and end times of the union
query.
 
Thanks you very much for all of you expertise and help. It works great.
Now in the final query, I want to show the Exception code only if the
Exception minute field is >0
This is what I have but I get a #Error where the code should appear.
EX: IIf([ExceptionTime]>0,[ExceptionCode],"")

What am I doing wrong??

This is probably a very simple problem.

Thanks,
Norma
 
I can't see your data so it is a bit difficult to determine. Is
ExceptionTime possibly Null? Is exception time a field value or is it
calculated?
 
First question, yes, exception time can be null, and yes, the exception
time is a calculated field

Here is the SQL for the query that I am using to pull exception data
and data that has no exceptions

SELECT qryUnionWithExceptionTable.Date,
qryUnionWithExceptionTable.[Lot#],
qryUnionWithExceptionTable.LineLeader, qryUnionWithExceptionTable.UPC,
qryUnionWithExceptionTable.event,
qryUnionWithExceptionTable.ExceptionCode,
qryUnionWithExceptionTable.ActualTimeNOexcep,
qryUnionWithExceptionTable.ProductiveTime,
qryUnionWithExceptionTable.ExcepStartTime,
qryUnionWithExceptionTable.ExcepEndTime,
IIf([ActualTimeNOexcep]>[ProductiveTime],hoursandminutes([ExcepEndTime]-[ExcepStartTime]),"")
AS ExcepTime
FROM qryUnionWithExceptionTable;

I would like to pull this into a report that will show total of
production time, minus exception time with the exception time grouped
by code.
In plain language, I want the line leader as the main grouping,
followed by the three possible work fields, startup, production and
reconcile. Each with total worked hours and minutes, then showing total
exception time.

a bit complicated
 
You stated "EX: IIf([ExceptionTime]>0,[ExceptionCode],"")" and then later
use "ExcepTime". I don't know if this is a typo or what. Also you have to
account for replacing a null value with a number.
 
Back
Top