Running sum, by product? Autonumber, by product?

[snipe]

Hey guys,

I have an output that lists a package sku and its components. Each component get its own record. What I need is to autonumber by package sku, like this:


I have seen things about somehow creating a temp table, and using autonumber on that temp table, then appending that data to a master table, and doing that for each package. I was also thinking of making a column of all 1's and just doing a running total (DSum?) but only by each package sku.

We have 12k package sku's, so obviously I can't do this by hand..

Any ideas?

[June7]

Review http://allenbrowne.com/ranking.html

Can probably also be done with DCount() but domain aggregate functions can be slow, although I have known the subquery approach to run slow as well on large datasets.

Record 7 in the example doesn't seem to fit. Why does it show 4 under What I Need?

[snipe]

Review http://allenbrowne.com/ranking.html

Record 7 in the example doesn't seem to fit. Why does it show 4 under What I Need?

Typo, did that really fast while the bosses were trying to rush me out the door.

I am fully expecting this to run slow, but I will only have to do it once. This is all for a conversion from our old system to the new.

I'll go over that Ranking page and see if I can get it to work.

Thanks

[June7]

Updating a new field can also probably be done programmatically by opening and manipulating a recordset of the table.

[snipe]

Updating a new field can also probably be done programmatically by opening and manipulating a recordset of the table.

That is where you lose me. Only messed with recordsets in VBA a couple times and it was just over my head.

[June7]

Code like:

Public Sub SetPackSeq()
Dim rs As DAO.Recordset
Dim strPKG As String, intSeq As Integer
Set rs = CurrentDb.OpenRecordset("SELECT * FROM Table1 ORDER BY PKG_ITM_CD, CMPNT_ITM_CD;")
strPKG = rs!PKG_ITM_CD
intSeq = 1
While Not rs.EOF
    rs.Edit
    If rs!PKG_ITM_CD <> strPKG Then
        strPKG = rs!PKG_ITM_CD
        intSeq = 1
    End If
    rs!WhatINeed = intSeq
    rs.Update
    intSeq = intSeq + 1
    rs.MoveNext
Wend
End Sub

[snipe]

That worked perfectly on a test sample. Wow.

Bout to test on main database.

[snipe]

Looks good! Thank you so much!! The skill and knowledge that some of you guys have just blows me away.