Numbering grouped data within a table

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a table which contains stock numbers and URLs to photos of those stock
numbers. I need to AutoNumber the records within each stock number group.

i have this:

STOCK|URL|NUMBER
1234|http://photo.store.com/12324234.jpg|
1234|http://photo.store.com/12324289.jpg|
1234|http://photo.store.com/dcm5.jpg|
1234|http://photo.store.com/1234578.jpg|
1235|http://photo.store.com/144234324.jpg|
1235|http://photo.store.com/12rtyru.jpg|

i need this:

STOCK|URL|NUMBER
1234|http://photo.store.com/12324234.jpg|1
1234|http://photo.store.com/12324289.jpg|2
1234|http://photo.store.com/dcm5.jpg|3
1234|http://photo.store.com/1234578.jpg|4
1235|http://photo.store.com/144234324.jpg|1
1235|http://photo.store.com/12rtyru.jpg|2

It seems that if I could do some type of count on the grouped STOCK and
apply the count to the NUMBER field as it counted each grouping (1,2,3,...)
and the count would restart on the next STOCK group that I could achieve my
goal.

Does anybody know of a way to do this?
 
warren50 said:
I have a table which contains stock numbers and URLs to photos of those stock
numbers. I need to AutoNumber the records within each stock number group.

i have this:

STOCK|URL|NUMBER
1234|http://photo.store.com/12324234.jpg|
1234|http://photo.store.com/12324289.jpg|
1234|http://photo.store.com/dcm5.jpg|
1234|http://photo.store.com/1234578.jpg|
1235|http://photo.store.com/144234324.jpg|
1235|http://photo.store.com/12rtyru.jpg|

i need this:

STOCK|URL|NUMBER
1234|http://photo.store.com/12324234.jpg|1
1234|http://photo.store.com/12324289.jpg|2
1234|http://photo.store.com/dcm5.jpg|3
1234|http://photo.store.com/1234578.jpg|4
1235|http://photo.store.com/144234324.jpg|1
1235|http://photo.store.com/12rtyru.jpg|2

It seems that if I could do some type of count on the grouped STOCK and
apply the count to the NUMBER field as it counted each grouping (1,2,3,...)
and the count would restart on the next STOCK group that I could achieve my
goal.

Does anybody know of a way to do this?
Click to expand...


Warren,

If I understand right, you want to enter a Stock number and URL and have the
Item Number automatically entered (in sequence).

For a table name I used tblFotos, for field names I used lngStock,txtURL and
lngNumber.

On the form I used tbStock, tbURL and tbNumber (tb = text box).

For me, this helps reduce confusion on whether it is a control or a field I
am looking at - change the code to match your field/control names.

And you will have to change "tblFotos" to your table name in three lines;
the lines are marked with:

'*** change tblFotos to your table name ***

To initally number the items, use

Public Sub Number_Fotos()


To clear *ALL* item numbers use

Private Sub No_Numbers_Click()

The sub "Form_BeforeUpdate" adds/updates the Item Number when you add/edit a
record.


'++++++++++ Begin code +++++++++++
'(watch for line wrap)

Option Compare Database
Option Explicit

'===============================================
Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo BeforeUpdate_Err

Dim rst As Recordset
Dim strSQL As String
Dim MaxNumber As Long

'*** change tblFotos to your table name ***
strSQL = "SELECT Max(lngNumber) AS MaxOflngNumber FROM tblFotos GROUP BY
lngStock HAVING lngStock = " & Me.tbStock
Set rst = CurrentDb.OpenRecordset(strSQL)

If rst.BOF And rst.EOF Then
MaxNumber = 1
Else
MaxNumber = rst.Fields(0) + 1
End If

Me.tbNumber = MaxNumber

BeforeUpdate_Exit:
'clean up
rst.Close
Set rst = Nothing
Exit Sub

BeforeUpdate_Err:
MsgBox Err.Number & " " & Err.Description
Cancel = True
Me.Undo
Resume BeforeUpdate_Exit
End Sub


'===============================================

' clears all Item Numbers
Private Sub No_Numbers_Click()

'*** change tblFotos to your table name ***
CurrentDb.Execute "UPDATE tblFotos SET lngNumber = Null;", dbFailOnError
Me.Refresh
End Sub



'===============================================
' Numbers Items
Public Sub Number_Fotos()

On Error GoTo NumberFotos_Err

Dim rst As Recordset
Dim strSQL As String
Dim tmp As Long
Dim i As Long

'*** change tblFotos to your table name ***
strSQL = "Select lngStock, lngNumber From tblfotos Order by lngStock,
txtURL;"
Set rst = CurrentDb.OpenRecordset(strSQL)

'check for records
If rst.BOF And rst.EOF Then
'no records
GoTo NumberFotos_Exit
End If

rst.MoveFirst

tmp = rst!lngStock
i = 1
Do
With rst
.Edit
If !lngStock = tmp Then
!lngNumber = i
Else
i = 1
!lngNumber = i
tmp = !lngStock
End If
.Update
i = i + 1
.MoveNext
End With
Loop Until rst.EOF
Me.Refresh

NumberFotos_Exit:
'clean up
rst.Close
Set rst = Nothing
Exit Sub

NumberFotos_Err:
MsgBox Err.Number & " " & Err.Description
Resume NumberFotos_Exit
End Sub

'++++++++++ End code +++++++++++

HTH
 
This imported data based on photos uploaded by users of an online inventory
database. I need the numbering to produce a standardized name based on the
stock number_1.jpg, stock number_2.jpg,... . Then I export original photo url
and the standardized image name into a another program which retrieves the
photos from the web and downloads them renamed to the standardized naming
convention.

The code that Steve S. provided doesn't work for me because there is no form
entry for the data. I should have been more specific in my explaination. The
table examples I gave are "exactly" what the imported table info looks like.
 
In that case, Autonumber is not what you're looking for. Autonumber is
feature of Access that automatically increments the value of field in a
table as records are added. This is predominately for use when a key
value is required. For this I would use DAO to grab the records and set
the values via code. This will allow you the flexibilty of renumbering
the values in the event that one is deleted (eg you have five records
1,2,3,4,5. Record number 3 is deleted leaving 1,2,4,5.) I'm assuming
that should this occur you would want the values to be adjusted to
remain sequential. The sub below is written off the top of my head so
there may be some adjustments neccessary...

Sub numberStockRecords(lngStockNumber as long)

Dim db as DAO.Database
Dim rs as DAO.RecordSet
Dim strSQL as string
Dim intRecordCount as Integer

'SQL statement to select the records that we're going to number
'tableName and fieldName will have to be adapated
strSQL = "SELECT * FROM tableName WHERE fieldName = " & lngStockNumber & ";"
intRecordCount = 1

set db = currentDB()
set targetRS = db.OpenRecordset(strSQL)

with targetRS
while NOT .EOF
.Edit
.Fields("number") = intRecordCount
.Update
.MoveNext
intRecordCount = intRecordCount + 1
wend
end with

targetRS.close
set targetRS = Nothing
set db = nothing


end sub
 
Forgot something. The sub that I provided takes a parameter representing
the STOCK number whose records are to be updated. If you're running it
from a form, make sure there's a field that is bound to the STOCK field
in the underlying table. Then add a command button with the statement

Call numberRecords(Me.fieldName)

where Me.fieldName is the bound field with the STOCK information. You
may need to do some polishing from there such as adding a msg that the
records have been updated or do a requery on a subform if that's how
you're displaying the records. Once you've got that up and running let
me know if you'd like a sub that will batch update ALL records
regardless of the STOCK number.
 
Which online database are you using? How are the users uploading the photos?
(web page?)
It sounds like you want to rename the uploaded photos.

The concept is the same wether youare using Access, SQL, Phyton, Pearl, PHP,
JavaScript, etc.


Select all of the same Stock number photos.
Get the max (highest) number.
Add one to that number
Save the number for the photo.

Then you can parse the uploaded photo name, append an underscore and the
number, append the extension and save the new name.
 
Back
Top