August 10, 2006 at 2:48 pm
I need to sequentially number groups of data within a single table based on alphabetical ordering. Geez, I can't figure out a more human of describing it. Let's get to the data...
Here's my table(simplified):
CREATE TABLE [WorkTable] (
[PackingSlipID] [float] NOT NULL ,
[ProductName] [varchar] (50) NOT NULL ,
[ProductNumber] [float] NULL
);
PackingSlipID and ProductName are the keys.
Here's some sample data:
PackingSlipID,ProductName,ProductNumber
343075,KITPC,
343075,LM23,
343075,LHS01,
343075,GFPC,
343075,LM2,
343079,GFPC,
343079,KITPC,
343079,LM2,
343081,OFR5514,
343081,CLDM,
343081,CS01LH,
343081,BM519,
343081,BM738,
343082,LHS01,
343082,L48L,
343082,L45L,
343082,L46L,
I think the simplest way to explain what I need to do is to show the desired result:
PackingSlipID,ProductName,ProductNumber
343075,GFPC,1
343075,KITPC,2
343075,LHS01,3
343075,LM2,4
343075,LM23,5
343079,GFPC,1
343079,KITPC,2
343079,LM2,3
343081,BM519,1
343081,BM738,2
343081,CLDM,3
343081,CS01LH,4
343081,OFR5514,5
343082,L45L,1
343082,L46L,2
343082,L48L,3
343082,LHS01,4
I attempted to use the solution at http://www.databasejournal.com/features/mssql/article.php/10894_2244821_2 for Sequentially Numbering Groups of Records, but it appears it works for numerical data only, not alphanumerical. The solution needs to work in Access2k as a query. Source data comes from an external app and cannot be altered.
Thanks in advance..
..remy.
August 11, 2006 at 8:33 am
Solution:
declare @PackSlipID float, @ProductName varchar(50), @Num integer, @PreviosPackSlipID float
declare rst cursor for
select PackSlipID, ProductName
from WorkTable
order by PackSlipID, ProductName
Set @PreviosPackSlipID = 0
Set @Num = 1
open rst
fetch next from rst into @PackSlipID, @ProductName
while @@fetch_status = 0
Begin
If @PreviosPackSlipID = @PackSlipID
Begin
Set @num = @num + 1
End
Else
Begin
Set @num = 1
End
update WorkTable
set WorkTable.Productnumber = @Num
from WorkTable
where WorkTable.PackSlipID = @PackSlipID and WorkTable.ProductName = @ProductName
Set @PreviosPackSlipID = @PackSlipID
fetch next from rst into @PackSlipID, @ProductName
End
close rst
deallocate rst
August 11, 2006 at 8:58 am
Thanks for the TSQL solution, Nass.
Cursors and variables would work nicely in TSQL, which is what I'm used to, but (unfortunately) this is Jet SQL, which, unless I'm completely off my rocker (it IS Friday morning here...), does not support either! How some developers get along without variables is beyond me, but I digress.
And besides, for this query, optimization is important enough to preclude the use of cursors.
I'll admit, I'm rusty with Access right now, having avoided it like the plague since college. TSQL is more my cup of tea. I'd simply link to a table in our SQL Server db and use a stored procedure there, but that just doesn't feel right. Better to keep things localalized, right?
..remy.
August 11, 2006 at 10:19 am
I find it better to use a sql back-end...
Maybe you should try using VBA.
Set cn = CurrentProject.Connection
Dim rst As New ADODB.Recordset
Dim sqlStr, PrevPackSlipID As String
Dim i As Integer
Dim TotRecs As Integer
sqlStr = ""
sqlStr = sqlStr & "select PackSlipID, ProductName "
sqlStr = sqlStr & "from WorkTable "
sqlStr = sqlStr & " order by PackSlipID, ProductName "
rst.Open sqlStr, cn, adOpenKeyset, adLockOptimistic
i = 1
PrevPackSlipID = ""
Do While Not rst.EOF
If PrevPackSlipID = rst.Fields.Item(0) then
i=i+1
Else
i = 1
End if
'TO DO issue update statement for jet sql where i = productnumber
rst.MoveNext
Loop
rst.Close
August 12, 2006 at 5:43 am
or use docmd.runsql in vba
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply