Showing only records that are different from those preceding them.

[dandc0711]

I have a table that generates automatically from a constant data feed from a
crystal grower. The pertinant data generated is the serial ID (RunID),
Datetime of the data (DATETIME), and what process phase its in (PROCESS).

This data comes across once a minute and each run can take up to 3 days.
Every day I have to manually take datetimes from specific points in this
process.

I need to make a Query that sorts by RunID and DATETIME and only displays
the fields immediatly before and after a process change. So for example if
the table looked like this:

RUNID | DATETIME | PROCESS
0001 1/1/09 1201 Neck
0001 1/1/09 1202 Neck
0001 1/1/09 1203 Neck
0001 1/1/09 1204 Neck
0001 1/1/09 1205 Neck
0001 1/1/09 1206 Neck
0001 1/1/09 1207 Body
0001 1/1/09 1208 Body
0001 1/1/09 1209 Body
0001 1/1/09 1210 Body
0001 1/1/09 1211 Tail
0001 1/1/09 1212 Tail
0001 1/1/09 1213 Tail
0001 1/1/09 1214 Body

I need the query to produce a result like this:


RUNID | DATETIME | PROCESS
0001 1/1/09 1201 Neck
0001 1/1/09 1206 Neck
0001 1/1/09 1207 Body
0001 1/1/09 1210 Body
0001 1/1/09 1211 Tail
0001 1/1/09 1213 Tail
0001 1/1/09 1214 Body

With the goal being to strip out all the middle values of each process group
and present a much smaller group to choose from since I"m only concerned
about the min and max date times.

I tried using a Group by but if I grouped by PROCESS then it will disregard
the DATETIME sorting and grab all instances of the process throughout the
table and I would loose the ability to see when the process changed multiple
times throughout the same run.

 

[John Spencer]

This MIGHT work depending on whether or not the Time is accurate to the minute
(no seconds).

SELECT A.RunID, A.DateTime
FROM Table as A LEFT JOIN Table As B
ON A.RUNID = B.RUNID
AND A.DateTime = DateAdd("n",-1,B.DateTime)
WHERE A.Process <> B.Process OR B.RunID is Null

If the time has the seconds also you could try changing the join to
AND Format(A.DateTime,"yyyymmddhhnn") =
Format(DateAdd("n",-1,B.DateTime),"yyyymmddhhnn")

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