AutoNumbering grouped data within a table

[warren50]

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?

 

['69 Camaro]

Hi, Warren.

Does anybody know of a way to do this?

Yes. But this "Number" field is a calculated value, and it's usually a bad
idea to store calculated values. It's better to use a query to calculate
these values on the fly from whatever the current record values are in the
table, since they tend to change. Keeping up with all of these changes when
they where stored in a table is a nightmare.

By the way, don't use Number as a field name, because it is a Reserved
Keyword, and using Reserved words in queries or code can cause problems, like
returning the wrong results or just failing to run.

However, it appears from your data that you just need a number assigned to
each record of the group in a sequence from one to the last record in the
group, without any particular order. If not, go with the dynamic query I
mentioned and try the following:

SELECT O.ID, O.Stock, O.URL,
(SELECT COUNT(*)
FROM tblInventory AS I
WHERE ((I.Stock = O.Stock) AND (I.URL <= O.URL))) AS Num
FROM tblInventory AS O
ORDER BY O.Stock, 3 DESC;

.... where tblInventory is the name of the table that needs the grouped
number sequences, so change it to your table's name.

If you are going to store the calculated value in the table, then you'll
need to take a few steps. First, create a query and paste the following in
the SQL View pane:

SELECT O.ID, O.Stock, O.URL,
(SELECT COUNT(*)
FROM tblInventory AS I
WHERE ((I.Stock = O.Stock) AND (I.URL <= O.URL))) AS Num
INTO tblNumberedGrps
FROM tblInventory AS O
ORDER BY O.Stock, 3 DESC;

Again, change tblInventory to your table's name. Run this query to create
the tblNumberedGrps table. Then add the new field, Num, to your table as a
Number field and save the table and close it. Next, create a new query and
paste the following in the SQL View pane:

UPDATE tblInventory AS I INNER JOIN tblNumberedGrps AS N ON I.ID = N.ID
SET I.Num = N.Num;

Again, change tblInventory to your table's name. Run this query to update
your table.

You can now delete the tblNumberedGrps table, because you don't need it any
more. You can also delete the two queries you just created, because you
shouldn't need them -- unless you have to fix future records, but beware that
different numbers could be assigned to the records within a group because
they'll probably be in a different sort order.

If you use this method (instead of the dynamic query), I suggest adding code
in the data input form that will calculate and store the next number for each
record in each group, so that you don't have to run the queries above ever
again.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)

- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts as "Answers,"
so that all may benefit by filtering on "Answered questions" and quickly
finding the right answers to similar questions. Remember that questions
answered the quickest are often from those who have a history of rewarding
the contributors who have taken the time to answer questions correctly.

 

[warren50]

Thanks for the answer!

Since you mentioned not keeping a calculated field stored in a table I'll
give you the whole picture and you tell me what you think I should do.

The image URLs are photos submitted by end users in an online inventory
system. they pull up the stock number they want then upload multiple photos
of the item and it rights the image name to an array. There can be from 1-100
photos, the only requirement is that the 1st photo uploaded be a "main
exterior shot" because it is the thumbnail preview on a search results page.

The reason for getting the numbering of the URLs is to produce a
standardized name for the photo: STOCK_1.jpg, STOCK_2.jpg, .... down the
line. The order of the numbering is important because i need to make sure
that 1st uploaded photo is number "1". Once I have the numbering I intend to
create another query that contains the original photo name and its
corresponding standardized name, then I export this out to be used in another
program that downloads the original from the web then saves it locally under
the standardized name which I will determine based on the code you have
provided or some version of that code.

The imported URLs change almost daily so I already have import tables, but i
don't necessarily need the count in a table, just the ability to export the
resulting standardized photo name with the original name corresponding in the
proper order.