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@hotmail.com> wrote in message
news:1173068800.586859.21680@30g2000cwc.googlegrou ps.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
>

>
> 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...@hotmail.com> wrote:
> "bubbles" <bubbles....@hotmail.com> wrote in message
>
> news:1173068800.586859.21680@30g2000cwc.googlegrou ps.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.