Posted by bubbles on 04/20/07 00:44

On Mar 5, 2:12 pm, "Russ Rose" <russr...@hotmail.com> wrote:
> "bubbles" <bubbles....@hotmail.com> wrote in message
>
> news:1173068800.586859.21680@30g2000cwc.googlegroups.com...
>
>
>
>
>
> > Hi Guys n Gals,
>
> > I am going to migrate an Access VBA application to SQL Server 2005.
>
> > Essentially, what I need to do is to:
> > * open a recordset
> > * grab the ModelID and Age into variables
> > * and use these variables to compare with the next record
> > then
> > mark the current and previous records with the appropriate values.
>
> > In Access, I was able to open a recordset, step through the records
> > one by one and make the necessary comparisons,
>
> > calculations and updates.
>
> > Question:
> > =========
> > How can I achieve the same result with T-SQL?
>
> Cursors would closely approximate what you are doing with the recordset but
> I would not recommend using them.
>
> If you are familiar with VBA you may find writing a VB.Net CLR function to
> be a useful learning expirience.
>
> The true solution is to use the power of the relational database by
> developing a "set-based" solution. Joining a table to itself where RecordID
> = RecordID - 1 is an easy way to compare a "current" row's data to a
> previous row.
>
>
>
>
>
> > Here is the code:
>
> > Private Sub UpdateRecords()
> > '===============
> > ' Using DAO
> > '===============
> > Dim db as Database
> > Dim rs as Recordset
> > Dim intLastAge as Integer
> > Dim intLastRecID as Integer
> > Dim intLastModelID
>
> > Set db = Currentdb
>
> > '==============================
> > ' Opens a recordset
> > ' Data had been sorted by:
> > ' ModelID, Age
> > '==============================
> > Set rs = db.OpenRecordset("someTableName")
>
> > With rs
>
> > txtLastModel = !LastModel
> > intLastAge = !Age
> > intLastID = !RecordID
> > .MoveNext
>
> > Do Until .EOF
>
> > '==========================================================================­=====
> > ' Compare this record with the previous record
> > ' If this record is of the same Model then
> > ' If the Age is <= x days from the last record
> > ' 1. Mark this record's [Comment] as "Failed"
> > ' 2. Mark previous record's [Relate] with this
> > record's [RecID]
> > ' 3. Set the current record's [Age] as intLastAge
> > ' ELse
> > ' 1. Set the current record's [Age] as intLastAge
> > ' End If
> > ' Else
> > ' 1. Set this record's [ModelID] as intLastModelID
> > ' 2. Set this record's [Age] as intLastAge
> > ' End If
>
> > '==========================================================================­=====
>
> > '=================
> > ' If same ModelID
> > '=================
> > If !ModelID = intLastModelID Then
>
> > '=====================================================
> > ' Check whether Age is <= 5 from the previous record
> > '=====================================================
> > If !Age <= (intLastAge + 5) Then
> > '==========
> > ' Age <= 5
> > '==========
> > intLastRecID = !RecID
> > '=============================
> > ' Mark [Comment] as "Failed"
> > '=============================
> > .Edit
> > !Comment = "Failed"
> > .Update
>
> > '=============================================================
> > ' Mark previous record's [Relate] with current
> > record's RecID
>
> > '=============================================================
> > .MovePrevious
> > .Edit
> > !Relate = intLastRecID
> > .Update
>
> > '======================================================================
> > ' Grab the current record's [Age] for comparison
> > with the next record
>
> > '======================================================================
> > .MoveNext
> > intLastAge = !Age
>
> > Else
>
> > '=================================================================
> > ' Age not <= 5
> > ' Grab current record's [Age] for comparison with the
> > next record
>
> > '=================================================================
> > intLastAge = !Age
>
> > End if
>
> > '=======================
> > ' Not the same ModelID
> > '=======================
> > Else
>
> > '==================================================================
> > ' Grab the [ModelID] and [Age] for comparison with the
> > next record
>
> > '==================================================================
> > intLastModelID = !ModelID
> > intLastAge = !Age
>
> > End If
>
> > .MoveNext
> > Loop
> > End With
>
> > rs.Close
> > Set rs = Nothing
>
> > End Sub- Hide quoted text -
>
> - Show quoted text -- Hide quoted text -
>
> - Show quoted text -

THANK YOU, RUSS!

I've used your method [x].[ID] = [x].[ID] + 1 for a few stored
procedures.
Works like a charm!

Bubbles.

[Back to original message]