smart way of slit

[Roy]

hi,
my query as two fields:
description minutes need
where description is the name of my product and 'minutes need' is the
minutes i'm gonna need to finish the job.
my problem is a bit complicated, i hope u understand and be a bit patiente..
In my factory we work 7.00 till 2.00 - That's 19 hours (1140 minutes)
knowing how many minutes i need to finish my products, i can forecast the
daily times of starting and finishing a product.
But, there's breaks (9.30 till 10.00) and (12.30 to 13.00), etc
how can i transform this:
description Minutes Need
Rump

 

[Michel Walsh]

Hi,



If the table Breaks define the various interruption (over the assumed period of a day):

breaks Starting Duration
03:00:00 00:15:00
06:00:00 01:00:00
09:00:00 00:15:00



The following queries (there are 2 of them) seems to work, The final query is:

-----------------------------
SELECT MIN(CDate(t)+CDate(d)+penalities.penality) AS EndingTime
FROM penalities
WHERE CDate([d])<=[x1]
GROUP BY 1;
------------------------------

and this query call the saved query "penalities":

---------------------------
SELECT CDate(CDate(b.starting) -SUM(Nz(a.duration))-CDate(t) ) AS x1,
CDate(SUM(Nz(a.duration,0))) AS penality
FROM breaks AS a RIGHT JOIN breaks AS b ON b.starting > a.starting
WHERE b.starting > [t]
GROUP BY b.starting

UNION ALL

SELECT #23:59:59#, CDate(SUM(duration))
FROM breaks;
----------------------------

As example, if we start a job at 2:00:00, for a working time of 6 hours:



Query53 EndingTime
09:30:00



Indeed, starting at 2:00:00, ending at 9:30 will have spend the three breaks, giving a total work time of 6hours.

Always starting at 2:00, but for a working of 5 hours:

Query53 EndingTime
07:15:00


ending at 7:15, including the first and second break, make a total of 5 net hours.



The whole idea is almost in the last (embedded) query, which depends only on the parameter t, the starting time. If you run it with t= 2:00:00

penalities x1 penality
01:00:00 00:00:00
03:45:00 00:15:00
05:45:00 01:15:00
23:59:59 01:30:00



you read it as: if the net duration is 1 hour or less, there is no penality; if the net duration is less than 3:45, there is a 15 minutes to add, etc. Since the breaks are per day no job can have a net duration higher than 24h.





Hoping it may help,
Vanderghast, Access MVP