Making a labeling column with Sequential Numbers

[Darth_Elicious]

Hello,

I am attempting to convert an excel table I made for quickly labeling photos I take for my job. The photos have to be labeled like "[ID Number]_1 Facing [Direction]". What I need to do in access that I can't figure out is make a query column that assigns a number for every duplicate ID Number. Basically I am trying to create something like this:

Photo	ID Number	Label No.	Direction
1234	XX12345	1	North
1235	XX12345	2	Northeast
1236	XX12345	3	West
1237	XX54321	1	South
1238	XX54321	2	Southwest

The third column is the one I want to replicate in Access. Note how the Label number restarts when there is a new Resource ID I can use that to generate the command for naming the photos. Is this possible, or am I stuck with the Excel table? I achieved this in excel by using the formula "=IF(B3=B2,C2+1,1)". Can anyone help me?

Thanks!!

[CJ_London]

you can use a subquery in a query

SELECT *, (SELECT count(*) FROM myTable T WHERE IDnum=myTable.IDnum AND Photo<=myTable.Photo) AS LabelNo
FROM myTable
ORDER BY IDNum, Photo

[JoeM]

We just did something like this the other day here: https://www.accessforums.net/showthread.php?t=65484
Paul (who is on this site too) explained the process in detail here: https://access-programmers.co.uk/for...d.php?p=941585

I think you should be able to use that method to get what you want.
If you run into any issues creating it, post back here.

[andy49]

certainly possible in access through VBA

I'm not sure if it can be done through SQL

[Darth_Elicious]

Thanks all! I am still getting an error in the column. The formula I got from Paul's et al's post was

DCount("*","[Photo Log]"," Resource = " & '[Resource]' & " AND Photo <= " & [Photo])

Photo Log is the name of the table the query is based from. However, this is not doing what I need, it is giving me this:

Photo	Resource	Num
123	XX12345	1
124	XX12345	2
125	XX12345	3
126	XX12346	4
127	XX12346	5
128	XX12347	6

The numbers are in sequence and don't restart when I need them to. Please note that "Photo" is numerical and "Resource" is text, so I added the 's around [Resource] as note on the post, otherwise I get an error (the IDs for our surveys are always two letters and five numbers). Any ideas what I'm doing wrong?

[andy49]

Resource = ' " & [resource] & " ' and photo <= " & [photo]

[Darth_Elicious]

Thanks, but that makes the Num Column all zeros...

Photo	Resource	Num
123	XX12345	0
124	XX12345	0
125	XX12345	0
126	XX12346	0
127	XX12346	0
128	XX12347	0

[andy49]

No space between the ' and " in all cases? I don't usually it that way as you can probably tell.


Sent from my iPhone using Tapatalk

[Darth_Elicious]

Aha! That solved the problem. Thanks Andy!!