Only Display records before and after a change in teh value of a f

[dandc0711]

I have a table that looks like the following

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 Body
0001 1/1/09 1207 Body
0001 1/1/09 1208 Body
0001 1/1/09 1209 Body
0001 1/1/09 1210 Neck
0001 1/1/09 1211 Neck
0001 1/1/09 1212 Neck
0001 1/1/09 1213 Neck
0001 1/1/09 1214 Body
0001 1/1/09 1215 Body
0001 1/1/09 1216 Body
0001 1/1/09 1217 Body
0001 1/1/09 1218 Shutdown

I need to make a query that only displays the recrods before and after a
change in the process field. So where it changes from Neck to Body I want to
see the Last Neck and the first Body. So the above table would ideally yeild
something like the following.

RUNID | DATETIME | PROCESS
0001 1/1/09 1201 Neck
0001 1/1/09 1205 Neck
0001 1/1/09 1206 Body
0001 1/1/09 1209 Body
0001 1/1/09 1210 Neck
0001 1/1/09 1213 Neck
0001 1/1/09 1214 Body
0001 1/1/09 1217 Body
0001 1/1/09 1218 Shutdown


I tried to use a Group By but it just lumped all the necks together and and
all the Bodies together, which doesn't work for my needs since I need to be
able to see the datetimes when it changes each time.

Thanks for the help

 

[vanderghast]

Rank oder by datetime over all processes, use your favorite algorithm.

That makes a query q1.



Make a new query:

SELECT q1.dateTime, q1.process
FROM q1 LEFT JOIN q1 AS q1_1
ON q1.rank = q1_1.rank + 1
AND q1.process <> q1_1.process




Vanderghast, Access MVP

 

[vanderghast]

The previous query was only supplying the first record of each new sequence,
to get the first and the LAST record of each sequence:


SELECT q1.dateTime, q1.process
FROM q1 LEFT JOIN q1 AS q1_1
ON (q1.rank = q1_1.rank + 1
AND q1.process <> q1_1.process)
OR
(q1.rank = q1_1.rank - 1
AND q1.process <> q1_1.process)


which can list an original record twice if it is the only one of its
sequence:


would list 1206 twice, since it is the first of a new sequence, and also
the last one of its sequence. Add a DISTINCT to remove its duplicated
occurence, if you want it to be listed just once.


Vanderghast, Access MVP

 

[dandc0711]

This looks like it will solve my problem, thanks. Now If I can just figure
out how to get it to run faster. With each table having nearly a million
records its a rather slow load of the final query.

 

[vanderghast]

Probably the time is spent in finding the ranks.

A 'fast' way to do it, with million of record, is to use an empty table,
with an autonumber field, and fill the table with your data with an insert
query having an ORDER BY clause:

INSERT INTO temp( dateTime, process) SELECT dateTime, process FROM somewhere
ORDER BY dateTime, process


The autonumber should then supply the rank we look for (note that it is not
necessary for the rank to start at 1, or at 0, or at any starting value, in
fact).



Vanderghast, Access MVP