Help please - Date from previous record calculation

  • Thread starter Thread starter Cam
  • Start date Start date
C

Cam

Hello,

I have the following query that I need to calculate the number of days at
each operation. Unfortunately, the data calculation is from a previous
operation date. I don't know how to achieve this.

Sample date:
Order# Part# Opr ProdStrtDate CompDate
456000 A3633 10 2/10/09 2/13/09
456000 A3633 20 2/10/09 2/14/09
456000 A3633 30 2/10/09 2/16/09
456000 A3633 40 2/10/09 2/20/09
456000 A3633 50 2/10/09 2/22/09

Result need:
Order# Part# Opr ProdStrtDate CompDate OprCompDate
456000 A3633 10 2/10/09 2/13/09 3
(2/13-2/10)
456000 A3633 20 2/10/09 2/14/09 1
(2/14-2/13)
456000 A3633 30 2/10/09 2/16/09 2
(2/16-2/14)
456000 A3633 40 2/10/09 2/20/09 4
(2/20-2/16)
456000 A3633 50 2/10/09 2/22/09 2
(2/22-2/20)

where the opr 10 is compDate minus ProdStrtDate from opr 10 only. The rest
of the calculation is from previous records. Thank you for any help.
 
Assuming that your data always increment Opr by 10's this will work.
Substitute your table name for Cam ---
Cam_A
SELECT Cam.[Order#], Cam.[Part#], Cam.Opr AS Opr1, Cam.ProdStrtDate,
Cam.CompDate AS CompDate1
FROM Cam
UNION SELECT Cam.[Order#], Cam.[Part#], 0 AS Opr1, null, Cam.ProdStrtDate AS
CompDate1
FROM Cam
WHERE (((Cam.Opr)=10));

SELECT Cam_A.[Order#], Cam_A.[Part#], Cam_A.Opr1, Cam_A.ProdStrtDate,
Cam_A.CompDate1, [Cam_A].[CompDate1]-[Cam_A_1].[CompDate1] AS OprCompDate
FROM Cam_A INNER JOIN Cam_A AS Cam_A_1 ON (Cam_A.[Part#] = Cam_A_1.[Part#])
AND (Cam_A.[Order#] = Cam_A_1.[Order#])
WHERE (((Cam_A.Opr1)=[Cam_A_1].[Opr1]+10));
 
Thanks for the response Karl. the opr is not alway increment of 10. It varies
from different part#. Is there another way?

KARL DEWEY said:
Assuming that your data always increment Opr by 10's this will work.
Substitute your table name for Cam ---
Cam_A
SELECT Cam.[Order#], Cam.[Part#], Cam.Opr AS Opr1, Cam.ProdStrtDate,
Cam.CompDate AS CompDate1
FROM Cam
UNION SELECT Cam.[Order#], Cam.[Part#], 0 AS Opr1, null, Cam.ProdStrtDate AS
CompDate1
FROM Cam
WHERE (((Cam.Opr)=10));

SELECT Cam_A.[Order#], Cam_A.[Part#], Cam_A.Opr1, Cam_A.ProdStrtDate,
Cam_A.CompDate1, [Cam_A].[CompDate1]-[Cam_A_1].[CompDate1] AS OprCompDate
FROM Cam_A INNER JOIN Cam_A AS Cam_A_1 ON (Cam_A.[Part#] = Cam_A_1.[Part#])
AND (Cam_A.[Order#] = Cam_A_1.[Order#])
WHERE (((Cam_A.Opr1)=[Cam_A_1].[Opr1]+10));


Cam said:
Hello,

I have the following query that I need to calculate the number of days at
each operation. Unfortunately, the data calculation is from a previous
operation date. I don't know how to achieve this.

Sample date:
Order# Part# Opr ProdStrtDate CompDate
456000 A3633 10 2/10/09 2/13/09
456000 A3633 20 2/10/09 2/14/09
456000 A3633 30 2/10/09 2/16/09
456000 A3633 40 2/10/09 2/20/09
456000 A3633 50 2/10/09 2/22/09

Result need:
Order# Part# Opr ProdStrtDate CompDate OprCompDate
456000 A3633 10 2/10/09 2/13/09 3
(2/13-2/10)
456000 A3633 20 2/10/09 2/14/09 1
(2/14-2/13)
456000 A3633 30 2/10/09 2/16/09 2
(2/16-2/14)
456000 A3633 40 2/10/09 2/20/09 4
(2/20-2/16)
456000 A3633 50 2/10/09 2/22/09 2
(2/22-2/20)

where the opr 10 is compDate minus ProdStrtDate from opr 10 only. The rest
of the calculation is from previous records. Thank you for any help.
Click to expand...
Click to expand...
 
SELECT a.opr, LAST(a.compDate) - Nz(MAX(b.compDate), LAST(a.prodStrtDate))
FROM table AS a LEFT JOIN table AS b
ON a.compDate> b.compDate
GROUP BY a.opr



should do (but I didn't test it). Opr can be arbitrary (not even in order of
time, ie, opr 30 can have its compDate done before opr 20), as long as it is
UNIQUE (no dup).



Vanderghast, Access MVP
 
.... and you can add additional fields as long as you aggregate them with,
say, LAST:


SELECT LAST(a.PartNumber), a.opr, LAST(a.prodStrDate), LAST(a.compDate),
LAST(a.compDate) - Nz(MAX(b.compDate), LAST(a.prodStrtDate))
FROM ...



Vanderghast, Access MVP
 
Back
Top