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?



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

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?


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 = !ModelID ' Typo in prev post
intLastAge = !Age
intLastID = !RecID ' Typo in prev post
.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

"bubbles" <bubbles.one (AT) hotmail (DOT) com> wrote

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.

I'll try this.
Thanks!

On Mar 5, 2:12 pm, "Russ Rose" <russr... (AT) hotmail (DOT) com> wrote:
THANK YOU, RUSS!

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

Bubbles.