Date calculation from previous record?

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 DaysComp
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.
 
A

Allen Browne

There are a couple of ways to get the value from the previous record - a
subquery, or a DMax() expression.

To get the prior completion date using a subquery, you would type an
expression like this into the Field row in query design:

(SELECT Max(CompDate) AS PriorCompDate
FROM Table1 AS Dupe
WHERE Dupe.[Order#] = Table1.[Order#]
AND Dupe([Part#] = Table1.[Part#]
AND Dupe.CompDate < Table1.CompDate)

If there is no prior completion date, you want to use ProdStrtDate from this
record, so use:
Nz(xxx, [ProdStrtDate])
where xxx is the subquery you got working.
JET won't recognise this as a date, so wrap it in CVDate(), i.e.:
CVDate(Nz(xxx, [ProdStrtDate]))

If subqueries are new, here's an introduction:
http://allenbrowne.com/subquery-01.html
The example on the previous meter reading value will probably be the most
relevant for you.
 
C

Cam

Thank Allen. I put in your expression and it says it has a syntax error
missing operator.

Allen Browne said:
There are a couple of ways to get the value from the previous record - a
subquery, or a DMax() expression.

To get the prior completion date using a subquery, you would type an
expression like this into the Field row in query design:

(SELECT Max(CompDate) AS PriorCompDate
FROM Table1 AS Dupe
WHERE Dupe.[Order#] = Table1.[Order#]
AND Dupe([Part#] = Table1.[Part#]
AND Dupe.CompDate < Table1.CompDate)

If there is no prior completion date, you want to use ProdStrtDate from this
record, so use:
Nz(xxx, [ProdStrtDate])
where xxx is the subquery you got working.
JET won't recognise this as a date, so wrap it in CVDate(), i.e.:
CVDate(Nz(xxx, [ProdStrtDate]))

If subqueries are new, here's an introduction:
http://allenbrowne.com/subquery-01.html
The example on the previous meter reading value will probably be the most
relevant for you.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

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 DaysComp
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...
 
C

Cam

I fixed the syntax error, but when run it, it ask to input Order#, Part# and
CompDate fields. What is wrong? Anyway, the sql now look like this. Please
note I change the field name to reflect actual name.

SELECT Max([OprCompDate]) AS PriorCompDate
FROM ProdOrdClosedOpr AS Dupe
WHERE (((Dupe.ProdOrd)=[ProdOrdClosedOpr].[ProdOrd]) AND
((Dupe.Item)=[ProdOrdClosedOpr].[Item]) AND
((Dupe.OprCompDate)<[ProdOrdClosedOpr].[OprCompDate]));

Allen Browne said:
There are a couple of ways to get the value from the previous record - a
subquery, or a DMax() expression.

To get the prior completion date using a subquery, you would type an
expression like this into the Field row in query design:

(SELECT Max(CompDate) AS PriorCompDate
FROM Table1 AS Dupe
WHERE Dupe.[Order#] = Table1.[Order#]
AND Dupe([Part#] = Table1.[Part#]
AND Dupe.CompDate < Table1.CompDate)

If there is no prior completion date, you want to use ProdStrtDate from this
record, so use:
Nz(xxx, [ProdStrtDate])
where xxx is the subquery you got working.
JET won't recognise this as a date, so wrap it in CVDate(), i.e.:
CVDate(Nz(xxx, [ProdStrtDate]))

If subqueries are new, here's an introduction:
http://allenbrowne.com/subquery-01.html
The example on the previous meter reading value will probably be the most
relevant for you.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

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 DaysComp
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...
 
A

Allen Browne

If Access is asking for values, it means that it cannot find fields with
those names.

Are they there in the source query? The subquery returns just one date
value: do you need other values from that record as well? If so, this might
help:
Getting a related field from a GroupBy (total) query
at:
http://www.mvps.org/access/queries/qry0020.htm

Be sure to use square brackets around the field names that have odd
characters, e.g. [Order#]

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Cam said:
I fixed the syntax error, but when run it, it ask to input Order#, Part#
and
CompDate fields. What is wrong? Anyway, the sql now look like this. Please
note I change the field name to reflect actual name.

SELECT Max([OprCompDate]) AS PriorCompDate
FROM ProdOrdClosedOpr AS Dupe
WHERE (((Dupe.ProdOrd)=[ProdOrdClosedOpr].[ProdOrd]) AND
((Dupe.Item)=[ProdOrdClosedOpr].[Item]) AND
((Dupe.OprCompDate)<[ProdOrdClosedOpr].[OprCompDate]));

Allen Browne said:
There are a couple of ways to get the value from the previous record - a
subquery, or a DMax() expression.

To get the prior completion date using a subquery, you would type an
expression like this into the Field row in query design:

(SELECT Max(CompDate) AS PriorCompDate
FROM Table1 AS Dupe
WHERE Dupe.[Order#] = Table1.[Order#]
AND Dupe([Part#] = Table1.[Part#]
AND Dupe.CompDate < Table1.CompDate)

If there is no prior completion date, you want to use ProdStrtDate from
this
record, so use:
Nz(xxx, [ProdStrtDate])
where xxx is the subquery you got working.
JET won't recognise this as a date, so wrap it in CVDate(), i.e.:
CVDate(Nz(xxx, [ProdStrtDate]))

If subqueries are new, here's an introduction:
http://allenbrowne.com/subquery-01.html
The example on the previous meter reading value will probably be the most
relevant for you.

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
DaysComp
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...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Previous opr date calculation help 7
Help please - Date from previous record calculation 4
Max of Multiple Date Fields 4
time_report_date-wise_employee-wise 6
Query/Expression Help Required! 2
WCG Stats Friday 26 May 2023 3
Pls help for Simple Calculation 1
VBA Programming help 2

Top