Row numbers

in Ms Access, you would need something like this:

SELECT a.ID, a.Field1, 
(select count(*)+1 from Table1 where field1 < a.field1 )as RowNo
FROM Table1 as a
Order By a.Field1

Select allOpen in new window

in which field1 is the field you wish to sort

more info:

Row Number in a Query
https://www.599cd.com/tips/access/140703-row-number/

Yes, use this clever function which uses a collection to speed up things:

Public Function RowCounter( _
  ByVal strKey As String, _
  ByVal booReset As Boolean, _
  Optional ByVal strGroupKey As String) _
  As Long
  
' Builds consecutive RowIDs in select, append or create query
' with the possibility of automatic reset.
' Optionally a grouping key can be passed to reset the row count
' for every group key.
'
' Usage (typical select query):
'   SELECT RowCounter(CStr([ID]),False) AS RowID, *
'   FROM tblSomeTable
'   WHERE (RowCounter(CStr([ID]),False) <> RowCounter("",True));
'
' Usage (with group key):
'   SELECT RowCounter(CStr([ID]),False,CStr[GroupID])) AS RowID, *
'   FROM tblSomeTable
'   WHERE (RowCounter(CStr([ID]),False) <> RowCounter("",True));
'
' The Where statement resets the counter when the query is run
' and is needed for browsing a select query.
'
' Usage (typical append query, manual reset):
' 1. Reset counter manually:
'   Call RowCounter(vbNullString, False)
' 2. Run query:
'   INSERT INTO tblTemp ( RowID )
'   SELECT RowCounter(CStr([ID]),False) AS RowID, *
'   FROM tblSomeTable;
'
' Usage (typical append query, automatic reset):
'   INSERT INTO tblTemp ( RowID )
'   SELECT RowCounter(CStr([ID]),False) AS RowID, *
'   FROM tblSomeTable
'   WHERE (RowCounter("",True)=0);
'
' 2002-04-13. Cactus Data ApS. CPH
' 2002-09-09. Str() sometimes fails. Replaced with CStr().
' 2005-10-21. Str(col.Count + 1) reduced to col.Count + 1.
' 2008-02-27. Optional group parameter added.
' 2010-08-04. Corrected that group key missed first row in group.

  Static col      As New Collection
  Static strGroup As String
  
  On Error GoTo Err_RowCounter
  
  If booReset = True Then
    Set col = Nothing
  ElseIf strGroup <> strGroupKey Then
    Set col = Nothing
    strGroup = strGroupKey
    col.Add 1, strKey
  Else
    col.Add col.Count + 1, strKey
  End If
  
  RowCounter = col(strKey)
  
Exit_RowCounter:
  Exit Function
  
Err_RowCounter:
  Select Case Err
    Case 457
      ' Key is present.
      Resume Next
    Case Else
      ' Some other error.
      Resume Exit_RowCounter
  End Select

End Function

Select allOpen in new window

See in-line notes for typical usage.

/gustav

Depends where and how you want to use it.

 Ryan and gutav have shown you two ways.  There's also an alternative  to those, which is to use a single variable.   That gives you the best performance over all, but you have to scroll forward through the set and only visit the rows once.  Using the other method, if scroll through the set in any way, then the numbers get re-assigned.   It's a great way though to get rows numbers as it is fast.

  If your doing this in a report, that's built in to the report engine and does not need to be done in the query.

Jim.

A way, for Table1(a)
a
1
3
5
2
4

Resukt:
r      a
1      1
2      3
3      5
4      2
5      4

Query:

SELECT nn(-1) AS r, Table1.a
FROM Table1 where false
UNION SELECT nn([a]) AS Expr1, Table1.a
FROM Table1;

Select allOpen in new window

Module1:

Option Compare Database
Public n As Integer
Public Function nn(i As Integer)
    If i = -1 Then n = -1
    n = n + 1
    nn = n
End Function

Select allOpen in new window

Thanks Ryan. I created table a and used this
SELECT a.ID, a.Field1,
(select count(*)+1 from a where field1 < a.field1 )as RowNo
FROM  a
Order By a.Field1 but all rownumbers are the same. What did I do wrong?

Gustav That works great! Is there any way here to restart the count at 1 when a new ID number appears in the list? EG
ID              RowID
104            1
104            2
105            1
106            1
106            2
106            3

Jim you got me again. I don't how to use a single variable in this situation

thanks all

Thanks Hasnr

Just got your message looks nice and simple! Could it do above? I suppose I had better come clean here. I have a table that stores concatenated data from a child table. The data is stored in a Memo field and all is fine with the world. Until I have to produce a query that filters the data where I then need to group the records. When grouping the memo field's contents are shortened so that only the first 250 to 350 characters are shown. But there may be 500 characters in the memo field. What I am looking to do is add numbers starting with the number 1 for each new item number so instead of grouping the data I can filter it by selecting only the rows with the number 1 in the row number field i.e the first record. I will add a table to show data
db4.zip

Is there any way here to restart the count at 1 when a new ID number appears in the list?

Yes, that's what the parameter strGroupKey  is for. Please study the in-line comments for example usage.

/gustav

It's what hnasr posted.

 You call a function from the query, which uses a single variable to track the count.  While it is very fast, it's a one shot deal.   The best way to use it is with an append or make table query because if you re-visit any of the rows, you end up with a new row number.

  So if you really need a select query (i.e. for a data sheet) and don't want a temp table, then gustav's solution is next up as you don't take the performance hit of using a sub-select.   On a very, very, small recordset, a sub-select  might eek it out, but I would doubt it.

Jim.

There comes a time, after 3 hours, where you just have to ask!  I have tried
17:' Usage (with group key):
18:'   SELECT RowCounter(CStr([ID]),False,CStr[GroupID])) AS RowID, *
19:'   FROM tblSomeTable
20:'   WHERE (RowCounter(CStr([ID]),False) <> RowCounter("",True));

in every possible combination from my table so HELP.
This is my table how do I change the sample code to use my table (TblTemp1) and get the RowID  to read 1 to however many when an Item number changes As sample DB. What I want is in, believe it or not, "What ! Want" column
db4--2-.zip

but all rownumbers are the same. What did I do wrong?

in your case, you would need to group them with "unique" fields, like:

SELECT a.*,
(select count(*)+1 from TblTemp1 where Item_ItemNumber & DN < a.Item_ItemNumber & a.DN and Item_ItemNumber = a.Item_ItemNumber) as RowNo
FROM TblTemp1 as a
order by a.Item_ItemNumber, a.DN

Select allOpen in new window

Your problem is, that you don't have a unique key. Thus, you have to construct one - it can be built from [Item_ItemNumber]  and [DN]. It is for this reason, to concatenate values easily, that the parameter keys must be strings.

Then you also need the group key, and that could be built from [Item_ItemNumber]  and [DoorType]

SELECT RowCounter(CStr([Item_ItemNumber]) & [DN],False,CStr([Item_ItemNumber]) & [DoorType]) AS RowID, *
FROM tblTemp1
WHERE (RowCounter(CStr([Item_ItemNumber]) & [DN],False) <> RowCounter("",True));

Select allOpen in new window

The example database with the query is attached.

/gustav
db4.mdb

Gustav  you have given me exactly what I asked for thank you. Do you have any idea why I cannot now filter the RowID even using a second query as in:
SELECT q1.RowID, q1.Item_ItemNumber, q1.Sets_ItemNumber, q1.DoorType, q1.Batch, q1.Location, q1.DN, q1.Result, q1.[What I want], q1.RowID AS A
FROM q1
WHERE (((q1.RowID)=1));

I also tried it with
WHERE (((q1.A)=1));

Life is so frustrating sometimes! Derek

Yes, that is strange. I believe it, again, is due to the lack of a unique key.

You may have to use the trick of writing the result to a temp table, then use and filter on that in your next query.

/gustav

Derek,
Relational databases do not support the concept of Row Numbers.  Tables and queries are unordered sets of data.  They have no permanent sequence.  That means you always have to sort them by some unique identifier if you need a specific row order.

What are you trying to use this sequence number for?  Your example of selecting by a specific RowID makes no sense.  All the examples make the assumption that you have a uniqueID and you just want to assign a sequence number for human reference.  The RowID is not permanent.  The RowID for any row will change depending on what records are selected by the query.

Every table requires a unique primary key.  Perhaps you should start with that.  If you have a natural key, you can use it or you can add an autonumber and Access will generate a unique ID for each record as it is added to the database.  Because of the way combo boxes work, multi-field primary keys are difficult to work with so if your natural key is multi-column, I would make a unique index for the natural key but also add an autonumber to use as the primary key and in all relationships.

The issue here Pat is caused by the need for concatenation.

Imagine a manufacturer that accepts an order for the supply of Hotel room doors. You may have 3 different types of door for each room but 300 rooms. When you send documentation to the customer you want all doors of one type in a convenient list, hence the concatenation. But when you come to manufacture you may want to split the manufacture into 3 batches. So now you need a document with two thirds of doors filtered out but you still want a convenient list of doors that need to be made for that part of the order. The temporary table takes care of this listing what is required and concatenating only doors of that batch by door type. For reports, what I had been doing was using grouping to eliminate the duplicates. (Query without the row unique field "Door Number") but for memo fields any form of grouping limits the output in a way I have not worked out yet, the results are irregular. Sometimes limiting field to 254 characters and on another record 334? So instead of using grouping I just wanted to allocate row numbers so I could select only one record from each "Door Type" by filtering for row number "1". The results is seeing the full list of concatenated records in the report because the query does not need grouping.

I cannot use a unique primary key numbers because I have no way of knowing those number until I filter the data and I would have to find the unique row ID for each door type.

The data is used for reports. Each report is shown after the filter is applied and the temporary table is populated. It does not matter which row is designated number 1 as long as there is only one number 1 for each door type.

I found out how to do.

It is the "automatic" row counter reset that doesn't work without a unique key.

So use the simple query (q1):

SELECT 
    RowCounter(CStr([Item_ItemNumber]) & [DN],False,CStr([Item_ItemNumber]) & [DoorType]) AS RowID, *
FROM 
    tblTemp1;

Select allOpen in new window

Now, this can be used in your filtering query:

SELECT DISTINCT q1.*
FROM q1
WHERE q1.RowID = 1;

Select allOpen in new window

Whenever records are changed in the source table (here TblTemp1), call this command before running the queries:

Call RowCounter(vbNullString, False)

Select allOpen in new window

See modified demo attached please.

/gustav
db4.mdb

Are doors serialized the way expensive machines like PCs or cars are?  If they are not, I don't understand why you would concatenate door IDs.  Why not just use a simple type + quantity if all doors of TypeA are interchangeable.  
55 TypeA
80 TypeB
95 Typec

Then if you need to print out by room, you would list the details

Room 1, 1 TypeA
Room 1, 2 Type B
Room 2, 1 TypeB
Room 2, 4 Type c

Hi Pat

All doors have a unique door reference number. This is shown on the building's floor plan. Often there will be a slight difference in many doors such as the colour for example. I would consider that to be a different door type but the industry does not. So I'm a bit stuck with the system.

I don't understand the need to assign a sequence number.  I also don't understand the usefulness of storing potentially hundreds of doorIDs in  a memo field.  There is a big difference between construction documentation and management reports.  For a management report, a door type and a quantity should be all that is needed.  For construction documentation, you would never use a mushed field that could run on for pages.  You would use lists and along with the specific ID, you would also include identifying information such as color or whatever else distinguishes a particular door.

When using a form, you would never need to type a doorID.  You would just click on it to go to a form with the specs.  To search for a door, you would use a combobox.  You could use cascading combos to reduce the length of the list.  Perhaps filtering first by room or by floor.

Can you tell us exactly how you intend to use the sequence numbers.

I wasn't questioning that Gus.  We get lots of questions from people who have no clue what they need but they have decided on the solution so they ask how to implement their "solution".  Helpful people jump in and come up with excellent solutions on how to put the bullets in the gun but no one asks if the shooter knows how to aim or use the safety or if perhaps he should be loading blanks.  Nothing I see in this thread convinces me that sequence numbers generated on the fly will actually solve any problem. The only indication of how they would be used was a remark that he wanted to use the sequence number in a query to find a specific record.  Well we all know how that works.  There is no guarantee that #12 today will still be #12 tomorrow.

Hi All sorry for lack of response had a last minute (I mean last minute) holiday.

Perhaps attachments make it clearer.  

The concatenated fields font size is dependent on number of characters in the memo field. So this highlighted portion of the report can hold  up to 1500 readable door numbers. Door numbers can be 1, 2, 3,   A B C or "Doo1.9827.008.GF" with max for each door number 35 characters.

If someone can show me a better way, I'm very interested.

Both Gustav and Hsnar's work well have yet to try Ryan so will close when I have done so.

Thank you all.
Capture.PNG
Capture1.PNG

If you want to replace all the door numbers with sequence numbers, what are you expecting to accomplish?  If people are going to be using a printed document to reference door #3, then the sequence number must be PERMANENTLY associated with whatever the actual Door ID is.  If you generate it on the fly, the sequence number could change.   And why is 1,2,3,4,5,6,7,9,9,10,11,12,13,14,15,16,17,18,19,20  more informative than just 20 or 1-20?

Also notice that since your sequence numbers are not numeric, they do not sort numerically.  They sort alphabetically.  Won't people find that confusing?

Hi Pat

Replacing is not the purpose of the sequenced numbers as I said earlier

"I just want to allocate row numbers so I could select only one record from each "Door Type" by filtering for row number "1". The results is seeing the full list of concatenated records in the report because the query does not need grouping."

What you see in the previously attached report is the result of using Gustav or Hsnar's query. The sequence of door numbers is not relevant because with real data the door number sequence will be in line with the order in which each individual door is entered into the forms and is chosen by the operator. For example he may want to keep door numbers together by floor with no relevance to any sequence.

As long as you understand that Door #3 today is not necessarily Door #3 on yesterday's report.

OK Pat got it.