More VBA to SQL code migration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

More VBA to SQL code migration

Author

Topic

jwmayo
Starting Member

28 Posts

Posted - 2010-08-13 : 12:08:04

While Im going to paste the code in here Im going to summarize what is needed. I have a table(example Below)that I need to update with a sequencial numerical value that increases by +1 each time the code is the same. When the code changes then I need the numbers to start back at 1. I need to do this in SQL and Im not sure again where to do this in SQL and how to write it.. Example of the code tha we had in access below..

Before
Sequence# Code Title
ABC ABC BOOK
ABC ABC BOOK VOL2
ABC ABC BOOK VOL3
HIS History Book VOL1
HIS History Book VOL2

results
Sequence# Code Title
1 ABC ABC BOOK
2 ABC ABC BOOK VOL2
3 ABC ABC BOOK VOL3
1 HIS History Book VOL1
2 HIS History Book VOL2

Dim dbsdb As Database
Dim rstitems
Dim lastcust As String
Dim thiscust As String
Dim brk_code As Integer
Dim cntr As Integer

Set dbsDB = CurrentDb
Set rstitems = dbsdb.openrecordset("SELECT [Item Master].[Sequencenumber], [Item Master].[Code], [Item Master].[title]FROM [Item Master]ORDER BY [Item Master].[Code], [Item Master].[title] ")

rstEmployees.MoveFirst
lastcust = rstEmployees!code
Do Until rstEmployees.EOF
thiscust = rstEmployees!code ' Field for title code

If thiscust = lastcust Then
brk_code = brk_code + 1
cntr = 0
'lastcust = thiscust
Else
'brk_code = 0
'brk_code = brk_code + 1
'cntr = 0
lastcust = thiscust
brk_code = 0
GoTo lastline
End If

With rstEmployees
.Edit
!Sequencenumber = brk_code
.Update
.MoveNext
End With
DoEvents
lastline:
Loop
If lastcust <> "" Then

End If

End Sub

jwmayo
Starting Member

28 Posts

Posted - 2010-08-13 : 13:19:11

The Before and Results part of my explanation bleeded together.. There are three columns: Sequence#, Code,Title.
Before as follows with Commas between values:
Nothing, ABC, ABC VOL 1
Nothing, ABC, ABC VOL 2
Nothing, ABC, ABV VOL 3
Nothing, HIS, History vol 1
Nothing, HIS, History vol 2

I need to just put a sequence number in that resets with each change in the value within the "Code" Column.

1, ABC, ABC VOL 1
2, ABC, ABC VOL 2
3, ABC, ABV VOL 3
1, HIS, History vol 1
2, HIS, History vol 2

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-08-13 : 15:00:47

If its just a one-time update..

update t
set t.Sequence# = t.seq
from
(select row_number() over(partition by Code order by Title) as seq,* from YourTable) t

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources