|
|
Posted by Russ Rose on 03/05/07 06:12
"bubbles" <bubbles.one@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
>
Navigation:
[Reply to this message]
|