Lead or Lag SQL options available?

[brandGuy]

I need to know if the Lead or Lag SQL options are available in MS Access. I have a need to use them to determine if data is different on the next day or previous day. The reason I need this is to limit data being moved over the network by not sending duplicate data and having the program assume that the day after data is the same. For example:

Original Datset
Start Date UserID WorkType Start_Time End_TIme
10/02/2007 1234 SHIFT 08:00AM 03:00PM
10/03/2007 1234 SHIFT 08:00AM 03:00PM
10/04/2007 1234 SHIFT 08:00AM 12:00PM
10/05/2007 1234 SHIFT 08:00AM 03:00PM
10/06/2007 1234 SHIFT 08:00AM 03:00PM
10/07/2007 1234 SHIFT 08:00AM 03:00PM
10/08/2007 1234 SHIFT 08:00AM 10:00AM

Passed Dataset
Start Date UserID WorkType Start_Time End_TIme
10/02/2007 1234 SHIFT 08:00AM 03:00PM
10/04/2007 1234 SHIFT 08:00AM 12:00PM
10/05/2007 1234 SHIFT 08:00AM 03:00PM
10/08/2007 1234 SHIFT 08:00AM 10:00AM

This eliminates the need to send unneeded data over the network since it is already being passed.

I am having trouble think of a way to do this without the lag and lead functions that SQL offers, but can not find a way to do it in ol' MS Access.

Any help would be greatly appreciated.

[ADezii]

I need to know if the Lead or Lag SQL options are available in MS Access. I have a need to use them to determine if data is different on the next day or previous day. The reason I need this is to limit data being moved over the network by not sending duplicate data and having the program assume that the day after data is the same. For example:

Original Datset
Start Date UserID WorkType Start_Time End_TIme
10/02/2007 1234 SHIFT 08:00AM 03:00PM
10/03/2007 1234 SHIFT 08:00AM 03:00PM
10/04/2007 1234 SHIFT 08:00AM 12:00PM
10/05/2007 1234 SHIFT 08:00AM 03:00PM
10/06/2007 1234 SHIFT 08:00AM 03:00PM
10/07/2007 1234 SHIFT 08:00AM 03:00PM
10/08/2007 1234 SHIFT 08:00AM 10:00AM

Passed Dataset
Start Date UserID WorkType Start_Time End_TIme
10/02/2007 1234 SHIFT 08:00AM 03:00PM
10/04/2007 1234 SHIFT 08:00AM 12:00PM
10/05/2007 1234 SHIFT 08:00AM 03:00PM
10/08/2007 1234 SHIFT 08:00AM 10:00AM

This eliminates the need to send unneeded data over the network since it is already being passed.

I am having trouble think of a way to do this without the lag and lead functions that SQL offers, but can not find a way to do it in ol' MS Access.

Any help would be greatly appreciated.

To the best of my knowledge, there is no equivalent functionality to SQL's Lead and Lag Functions in Access SQL. Off the top of my head, I would create a Nested Loop representing the Original Recordset (Table) and a Clone comparing data in every other Field other than Date for equivalency. If the data were equivalent, it would be ignored, if not it would be written to a sort of holding Table where a SQL, Statement would retrieve it. I'll hold off on this approach since there is probably a better answer out there from a Moderator/Expert, but I'll keep an eye on this Thread.