Number Serial Numbers in sequential order by order of appearance

Tcurtis

Board Regular
Joined
Aug 15, 2009
Messages
147
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.
Serial
Order of Appearance
A-3604511
A-3604512
A-3606421
A-3606422
A-3622141
A-3622142
A-3652281
A-3652282
A-3654461
A-3654462
A-3660771
A-3660772
A-3660773
<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> </tbody>
 

Some videos you may like

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,705
Office Version
  1. 2019
Platform
  1. Windows
Google MSAccess Rank Function for ideas. What is the purpose of this numbering?
 

Tcurtis

Board Regular
Joined
Aug 15, 2009
Messages
147
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

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,705
Office Version
  1. 2019
Platform
  1. Windows
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?"
 
Last edited:

Tcurtis

Board Regular
Joined
Aug 15, 2009
Messages
147

ADVERTISEMENT

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

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,705
Office Version
  1. 2019
Platform
  1. Windows
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

Board Regular
Joined
Aug 15, 2009
Messages
147
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

Well-known Member
Joined
Jun 3, 2015
Messages
1,940
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,112,812
Messages
5,542,632
Members
410,562
Latest member
FrankieBue
Top