Calculating Job End Dates

  • Thread starter Thread starter Joe Williams
  • Start date Start date
J

Joe Williams

I have a query that has the following fields (Sample data included)

MAchine # Job No HoursToGo
Machine 1 001 24
Machine 1 002 16
Machine 2 004 45

What I need to be able to do is calculate the forecasted end date for each
job. how I am thinking I want to do this is to take the first job in each
machine and add the hours to go to the current date/time. That will give me
the forecast end for the first job. Then I need to take this new date/time
and add the hours to go fro the second job to get the end date/time for that
job. And so on and so on. Every time the query sees a new machine number,
then it should reset to adding the hours to go to the current date/time and
go from there.

I am thinking I need to have a module function to do this, but not sure how
to write it or work it into the query. Any thoughts?

- joe
 
For a SQL solution, try something along the lines of

SELECT T1.machineNo, T1.jobNo, dateadd("h", T1.hoursToGo +
Nz((SELECT Sum(T2.hoursToGo)
FROM qryMachineTime AS T2
WHERE T1.machineNo = T2.machineNo AND T1.jobNo >
T2.jobNo),0),now()) AS timeToComplete
FROM qryMachineTime AS T1;

Hope This Helps
Gerald Stanley MCSD
 
Gerald,

Another option, that doesn't require the subquery follows. I've been using
this method for some time because I assumed that it would be quicker.

SELECT T1.MachineNo
, T1.JobNo
, DateAdd("h", SUM(T2.HoursToGo) as CompletionTime, Now())
FROM qryMachineTime T1
INNER JOIN qryMachineTime T2
ON T1.MachineNo = T2.MachineNo
AND T1.JobNo >= T2.JobNo
GROUP BY T1.MachineNo, T1.JobNo

I was about to make a statement regarding the speed of the two, and decided
that before I did, I should run a test. So, I generated 5000 records
(Machines 1-10, JobsNo maxed out at about 500, and machine times on the
order of 5-15 hours). I then tested my query and yours and found that yours
only took about a second to run, whereas my method took approximately a
minute. Any idea why? Is there a way in Access to see the execution plan,
like there is in SQL Server?

Dale
 
Back
Top