Number Serial Numbers in sequential order by order of appearance

[Tcurtis]

I have a question about numbering rows of data in a Access table or query. I do not want to number each row but want to number each time a serial number appears in a table or query. I have already sorted by date and would like to have a reference in the table of each appearance of a Serial number. Below is an example.
[TABLE="width: 148"]
<colgroup><col width="63" style="width: 47pt; mso-width-source: userset; mso-width-alt: 2247;"> <col width="135" style="width: 101pt; mso-width-source: userset; mso-width-alt: 4807;"> <tbody>[TR]
[TD="width: 63, bgcolor: transparent"]Serial
[/TD]
[TD="width: 135, bgcolor: transparent"]Order of Appearance
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]A-360451[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]A-360451[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]A-360642[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]A-360642[/TD]
[TD="bgcolor: transparent, align: right"]2
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]A-362214[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]A-362214[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]A-365228[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]A-365228[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]A-365446[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]A-365446[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]A-366077[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]A-366077[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]A-366077[/TD]
[TD="bgcolor: transparent, align: right"]3[/TD]
[/TR]
</tbody>[/TABLE]

 

[xenou]

Google MSAccess Rank Function for ideas. What is the purpose of this numbering?

 

[Tcurtis]

Great question. I am ranking the serial numbers for different types of transactions. A serial number will have many types of transactions, Date New, Date Repaired, Date Out to Floor, Date Received. So a serial number may appear only twice for Repair but 4 times for Out to Floor. I am creating a report that links each occurrence back to the data of that occurrence. The technician can then investigate each occurrence by clicking on the ranking number. All from one report or form.

 

[xenou]

I don't see any connection between numbering serial numbers from 1 to x and having a certain type of transaction. Are you trying to assign a unique ID number here?

I suppose a secondary (possibly more fundamental) question would be "does your table have a primary key?"

 

[Tcurtis]

I have both a primary key and a unique identifier for each transaction that takes place for each serial number. The report is easier to go thru this way than digging thru the data for some people.

 

[xenou]

Okay you are welcome to use a ranking technique. But from what you have said it sounds like unnecessary work that may make it more difficult to maintain and continue to develop the database. Keep in mind that many databases have user interfaces for selecting data but probably very few use a row number function for doing so.

 

[Tcurtis]

There is an added benefit to having these like this. If I know when a serial number came into the store room and I know when it went out, these would be the first occurrences of each category. So serial number 123456 came into the store room on 1/1/2019 went out on 2/1/19 I can put these side by side and subtract for days on the shelf. When that serial number comes back in I can subtract the 1st entry of it going against the first entry of it coming back in. So now I know how long it took someone to return something. Numbering each occurrence of each category allows me to not only display all events it also enables me to count time inbetween each event no matter how many events there are for on serial number.

 

[Micron]

I am creating a report that links each occurrence back to the data of that occurrence

That and your last post suggests that the ranking criteria is probably there - such as date or a combination of other data. I agree with xenou that your approach is most likely not required and may in fact, interfere elsewhere.