Help with Code

[Sousa]

Hi All,

I am really new to access and am trying to figure out a way to write the following script, but am really struggling. If anybody on here could help me figure it out or even give me a jump off point I would be really appreciative. I know its kind of weak to leave the code so incomplete, but I'm not really sure where else to go.
---------------------------------
Option Compare Database
Function DeleteDupTimes()

Dim rs1 As Recordset
Set rs1 = CurrentDb.OpenRecordset("test", dbOpenDynaset)

x = 0
rs1.MoveFirst 'MOVE TO THE FIRST RECORD
With rs1 'OPENS TABLE
Do Until .EOF
If column A of record 1 equals column A of record 2
and column B of record 1 equals column B of record 2
and column C of record 1 equals column C of record 2
then column E of record 2 equals column D of record 2 minus column D of record 1
'Then compare record 2 with record 3 in the same fashion and record 3 with 4 until the end of the table
-------------------------------------------------

Thanks for any help.

Sincerely,
Sousa

[Tony Toews]

On Fri, 1 Jun 2012 19:43:16 -0700 (PDT), Sousa <benwi...@gmail.com>
wrote:

>I am really new to access and am trying to figure out a way to write the following script, but am really struggling. If anybody on here could help me figure it out or even give me a jump off point I would be really appreciative. I know its kind of weak to leave the code so incomplete, but I'm not really sure where else to go.

Try using two recordsets. Maybe open one as read only if that suits
so as to help avoid page locking issues.

Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
For a convenient utility to keep your users FEs and other files
updated see http://www.autofeupdater.com/

[darth...@gmail.com]

Hi Sousa,

This is a really complex "IF" statement that could take a very long time to execute in the VBA/.Net code world. Is this a SQL Server backend that you are retrieving the data from? If it is, I would recommend that you put this code there in a T-SQL Stored Procedure that joins the data set to itself. You can order the data the way you want using Order by clause, and then use Row_Number function to assign a RowID. The self join then can join on Row_Numner = (Row_number -1). You can then just put in the select clause of the query the CASE statement you list below and execute your entire question using SQL servers set logic and get much better perf. Also the code will probably be much cleaner and hard baked.