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.

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
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Cam" <(E-Mail Removed)> wrote in message
news:E788596F-5D5A-4C39-8607-(E-Mail Removed)...
> 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.

Aloha Cam,

have you considered creating a 'OprStrtDate' field?

Darren

"Cam" wrote:

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

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

"Allen Browne" wrote:

> 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
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "Cam" <(E-Mail Removed)> wrote in message
> news:E788596F-5D5A-4C39-8607-(E-Mail Removed)...
> > 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.
>
>

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" wrote:

> 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
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "Cam" <(E-Mail Removed)> wrote in message
> news:E788596F-5D5A-4C39-8607-(E-Mail Removed)...
> > 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.
>
>

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
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Cam" <(E-Mail Removed)> wrote in message
news:E2FAAB66-E096-4E4B-A858-(E-Mail Removed)...
>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" wrote:
>
>> 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" <(E-Mail Removed)> wrote in message
>> news:E788596F-5D5A-4C39-8607-(E-Mail Removed)...
>> > 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.