Need advice re. extracting data for a cycle

[PD]

Greetings,
I need to extract data from a table of equipment readings. The equipment
passes reading data in 1 minute intervals in cycle. I need to compare cycle
to cycle so I need to label which data belongs to which cycle. There will be
an interval longer than 1 minute between cycles. It looks like this...

Serial Date 24hrtime Property ANreading
123 11/17/08 8:00:00 PM Cycle 158
123 11/17/08 8:01:00 PM Waste Temp 185
123 11/17/08 8:02:00 PM Chamber Temp 195
123 11/17/08 8:03:00 PM Waste Temp 205
123 11/17/08 8:03:00 PM Chamber Temp 228
123 11/17/08 8:04:00 PM Complete
123 11/18/08 8:28:00 PM Cycle 142
123 11/17/08 8:29:00 PM Waste Temp 155
123 11/17/08 8:30:00 PM Chamber Temp 175
123 11/17/08 8:31:00 PM Waste Temp 175
123 11/17/08 8:32:00 PM Chamber Temp 209
123 11/17/08 8:33:00 PM Complete

Any suggestions are greatly appreciated!
Thanks,

 

[Allen Browne]

Use a subquery to get the previous ANreading for the same Property, based on
[Date] + [24hrtime].

For an example, see:
Subquery basics: Get the value in another record
at:
http://allenbrowne.com/subquery-01.html#AnotherRecord
The example is for a meter reading.

It would be easier (and more efficient) if you were able to combine the date
and time into one field.

You might also want to consider avoiding reserved words as field names:
- Date is a reserved word, and known to give problems;
- Property is an Access reserved word.
For a list to refer to when designing your table, see:
http://allenbrowne.com/AppIssueBadWord.html

 

[John Spencer]

I assume that when Property = cycle that is the beginning of a cycle and
Complete Ends the cycle.

You would need a query something like the following to get the start and end
times of the cycles.
SELECT A.Serial, A.Date + A.24hrTime as CycleStart
, Min(B.Date + B.24HrTime) as CycleEnd
FROM YourTable As A INNER JOIN YourTable as B
ON A.Serial = B.Serial
AND A.Date + A.24HrTime< B.Date + B.24HrTime
WHERE A.Property = "Cycle"
AND B.Property = "Complete"
GROUP BY A.Serial, A.Date + A.24hrTime

Then using that you could get records and assign them periods.
SELECT A.Serial
, Q.CycleStart & "/" & Q.CycleEnd as CyclePeriod
, A.Date, A.Time, A.Property, A.ANreading
FROM YourTable as A INNER JOIN qCycles as Q
ON A.Serial = Q.Serial
AND A.Date + A.24hrTime >= Q.CycleStart
AND A.Date + A.24hrTime <= Q.CycleEnd



John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[PD]

Mr. Spencer,

Here is my sql based on your example. A Syntax error (missing operator) in
query expression 'Min(B.[Date Generated]...' occurs. When I clear the
message the 'WHERE' is highlighted.

Any thoughts?

Thanks again

SELECT A.[Serial Number], A.[Date Generated] + A.[24 HourTime] as CycleStart,
Min(B.[Date Generated] + B.[24 HourTime) as CycleEnd
FROM Data As A INNER JOIN Data as B
ON A.[Serial Number] = B.[Serial Number]
AND A.[Date Generated] + A.[24 HourTime]< B.[Date Generated] + B.[24 HourTime]
WHERE A.[Property Display Label] = "Cycle Count"
AND B.[Property Display Label] = "Complete"
GROUP BY A.[Serial Number], A.[Date Generated] + A.[24 HourTime]

 

[John Spencer]

Looks ok to me other than you may need to add parentheses around
A.[Date Generated] + A.[24 HourTime]
AND
B.[Date Generated] + B.[24 HourTime]
in the ON phrase


SELECT A.[Serial Number]
, A.[Date Generated] + A.[24 HourTime] as CycleStart
, Min(B.[Date Generated] + B.[24 HourTime) as CycleEnd
FROM Data As A INNER JOIN Data as B
ON A.[Serial Number] = B.[Serial Number]

AND (A.[Date Generated] + A.[24 HourTime])<
(B.[Date Generated] + B.[24 HourTime])

WHERE A.[Property Display Label] = "Cycle Count"
AND B.[Property Display Label] = "Complete"
GROUP BY A.[Serial Number], A.[Date Generated] + A.[24 HourTime]

Also, sometimes extraneous characters get introduced when you cut and paste
from the newsgroup. Try typing in the query from scratch and see if that helps.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[PD]

Hi Mr. Spencer,

I have tried to simplify by combining the Date Generated and 24 Hour Time
but I still get the error "Syntax error (missing operator) in query
expression 'Min (.[Generated]....

I have typed from scratch but just can't seem to figure out why it won't work.

SELECT [A].[Serial Number], [A].[Generated] As [CycleStart], Min
(.[Generated] As [CycleEnd]
FROM [120808a] As [A] INNER JOIN [120808a] As
ON [A].[Serial Number] = .[Serial Number]
AND [A].[Generated] < .[Generated]
WHERE [A].[Property Display Label] = "Cycle Count"
AND .[Property Display Label] = "Complete"
GROUB BY [A].[Serial Number], [A].[Generated];

--
PRD

Looks ok to me other than you may need to add parentheses around
A.[Date Generated] + A.[24 HourTime]
AND
B.[Date Generated] + B.[24 HourTime]
in the ON phrase


SELECT A.[Serial Number]
, A.[Date Generated] + A.[24 HourTime] as CycleStart
, Min(B.[Date Generated] + B.[24 HourTime) as CycleEnd
FROM Data As A INNER JOIN Data as B
ON A.[Serial Number] = B.[Serial Number]

AND (A.[Date Generated] + A.[24 HourTime])<
(B.[Date Generated] + B.[24 HourTime])

WHERE A.[Property Display Label] = "Cycle Count"
AND B.[Property Display Label] = "Complete"
GROUP BY A.[Serial Number], A.[Date Generated] + A.[24 HourTime]

Also, sometimes extraneous characters get introduced when you cut and paste
from the newsgroup. Try typing in the query from scratch and see if that helps.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Mr. Spencer,

Here is my sql based on your example. A Syntax error (missing operator) in
query expression 'Min(B.[Date Generated]...' occurs. When I clear the
message the 'WHERE' is highlighted.

Any thoughts?

Thanks again

SELECT A.[Serial Number], A.[Date Generated] + A.[24 HourTime] as CycleStart,
Min(B.[Date Generated] + B.[24 HourTime) as CycleEnd
FROM Data As A INNER JOIN Data as B
ON A.[Serial Number] = B.[Serial Number]
AND A.[Date Generated] + A.[24 HourTime]< B.[Date Generated] + B.[24 HourTime]
WHERE A.[Property Display Label] = "Cycle Count"
AND B.[Property Display Label] = "Complete"
GROUP BY A.[Serial Number], A.[Date Generated] + A.[24 HourTime]

 

[John Spencer]

What you posted was missing a closing parentheses in line 3 (below)
and has misspelled Group By in the last line.

SELECT [A].[Serial Number]
, [A].[Generated] As [CycleStart]
, Min (.[Generated]) As [CycleEnd]
FROM [120808a] As [A] INNER JOIN [120808a] As
ON [A].[Serial Number] = .[Serial Number]
AND [A].[Generated] < .[Generated]
WHERE [A].[Property Display Label] = "Cycle Count"
AND .[Property Display Label] = "Complete"
GROUP BY [A].[Serial Number], [A].[Generated];

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[PD]

That was embarassing.

Thank you
--
PRD

What you posted was missing a closing parentheses in line 3 (below)
and has misspelled Group By in the last line.

SELECT [A].[Serial Number]
, [A].[Generated] As [CycleStart]
, Min (.[Generated]) As [CycleEnd]
FROM [120808a] As [A] INNER JOIN [120808a] As
ON [A].[Serial Number] = .[Serial Number]
AND [A].[Generated] < .[Generated]
WHERE [A].[Property Display Label] = "Cycle Count"
AND .[Property Display Label] = "Complete"
GROUP BY [A].[Serial Number], [A].[Generated];

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Hi Mr. Spencer,

I have tried to simplify by combining the Date Generated and 24 Hour Time
but I still get the error "Syntax error (missing operator) in query
expression 'Min (.[Generated]....

I have typed from scratch but just can't seem to figure out why it won't work.

SELECT [A].[Serial Number], [A].[Generated] As [CycleStart], Min
(.[Generated] As [CycleEnd]
FROM [120808a] As [A] INNER JOIN [120808a] As
ON [A].[Serial Number] = .[Serial Number]
AND [A].[Generated] < .[Generated]
WHERE [A].[Property Display Label] = "Cycle Count"
AND .[Property Display Label] = "Complete"
GROUB BY [A].[Serial Number], [A].[Generated];