Return the value of the next record into a field in the current record

[jett88]

I have a table named Sign_On that looks like the following:

User1 Event1 SubEvent2 TimeStamp
User1 Event2 SubEvent1 TimeStamp
User1 Event1 SubEvent3 TimeStamp
User1 Event2 SubEvent1 TimeStamp
User2 Event1 SubEvent2 TimeStamp
User2 Event2 SubEvent1 TimeStamp
User2 Event1 SubEvent3 TimeStamp
User2 Event2 SubEvent1 TimeStamp

There are about 10 event and 100 subevents. What I am trying to do is get a query that will give me:

User1 Event1 SubEvent2 TimeStamp TimeStamp2

Where TimeStamp2 is the the value from the next record. The end result would be:

User1 Event1 SubEvent2 TimeStamp TimeStamp2
User1 Event1 SubEvent3 TimeStamp TimeStamp2
User2 Event1 SubEvent2 TimeStamp TimeStamp2
User2 Event1 SubEvent3 TimeStamp TimeStamp2

Any ideas on how this could be done?

 

[swampBoogie]

What do you mean by next? Next is not a relational concept.

Do you mean the record with second lowest timestamp for this event or what?

 

[jett88]

Next would be the timestamp that is the next largest

Example:

Bob Sign Off Break 8:15
Bob Sign On Automatic 8:30

This would become:

Bob Sign Off Break 8:15 8:30

 

[swampBoogie]

I think it would be easiest to handle this in the application.

Retrieve the rows ordered by user and timestamp

pseudo like vb-script influenced

set rs = connect.execute ("select ... order by user,timestamp&quot
user = ""
while not rs.eof
if user = rs("user&quot then
response.write rs(&quot;timstamp&quot & &quot;<br>&quot;
user = &quot;&quot;
else
response.write rs(&quot;user&quot & &quot; &quot; & rs(&quot;event&quot & &quot; &quot; & rs(&quot;timestamp&quot & &quot; &quot;
user = rs(&quot;user&quot
end if
rs.movenext
wend

This sort of assumes that there always is an even number of events for each user.