Query with increment number (2 Viewers)

[Leo_Polla_Psemata]

Hi,
How could we create an increment number on a query when the table doesn't have "auto number" or other similar field?

Even if i create one AN field in my table, then, we can create one increment number field on query but this
will not be in the same ascending order as the query is sorted.

For example, the query will be sorted in the way we tell however the increment field will not be
1
2
3
4

but
2
4
1
3

If i sort the query as per increment number, then the whole set of records, won't be as per sorted as it should be.

So, we need something so simpe and easy as "auto number" we use in tables but be used in a query, no matter other fields with number etc.

Why access does almost everything except coffee and increment number on queries ?

 

[Ranman256]

why is AN not the right order? Its just a field for you to reference. You can sort on anything. DateStamp order works just as well as AN.
I say, make a AN field and always have a passive reference created for you.

 

[CJ_London]

do you mean a row number? e.g.

PK..Row
1.....1
2.....2
3.....3
4.....4

and then sort PK desc you get

PK..Row
4.....1
3.....2
2.....3
1.....4

If your query is for a report - reports provide a row number

 

[Leo_Polla_Psemata]

Hi, i mean a row number in a query.
The query has a certain sort criteria itself.
Then we need to add a row with numbering
In excel, you type 1, next row 2 and you auto fill the whole block

In Access query, with this i can make row number (id is a/n field)
myn: DCount("*","tb1","id<=" & [id])

However, tb1, doesn't have any id A/N, if i enter one auto number, then,
yes, i can create row number on query but the sequence is not same as sorting criteria.
So, we just need a very simple method to add row number field in a query that it works on its own, no need to see and calculate any other field from the table etc.

 

[Leo_Polla_Psemata]

If your query is for a report - reports provide a row number

Yes, i know, why why why this access forgot to add something so simple in query ?

 

[Minty]

Because in normal use it's relatively meaningless.
It is a transitory reference that will different the next time you run the query if the data or sort order has changed.
If it's a rank (1st place, 2nd etc.) then maybe it has some value at that time.

You can still create a row number without an ID field, just replace whatever you are sorting on into the count expression you have above.
How would you handle ties?

In a report that you publish it might be useful, but normally on a form or datasheet it's not worth the effort of displaying it.

 

[CJ_London]

You would cause a lot less confusion if you provided an example as to what you mean - as I did in post #3 - is that what you mean?

 

[Leo_Polla_Psemata]

You would cause a lot less confusion if you provided an example as to what you mean - as I did in post #3 - is that what you mean?

Sorry if I cause confusion, English is not my mother language.

We have one query , imagine any set of record, outlined in a query

Say
John
Abraham
Nick
Charles

Now, we want to add one field with row number and get
1 John
2 Abraham
3 Nick
4 Charles

If we use a formula as the below, we will get row number HOWEVER the sort will change
myn: DCount("*","tb1","id<=" & [id])

We may get
1 Abraham
2 Charles
3 John
4 Nick

Because the field id in the tables, doesn't necessarily match our sorting criteria
If we sort based on our criteria, we will get
3 John
1 Abraham
4 Nick
2 Charles
which is not what the example in bold we want.

 

[theDBguy]

I think the main question was "why you need it in the query?" Can't you use a report instead?

In any case, see if you can use the GetRowNum() function posted on this link.

sql - Row_Number() in Access select statement - Stack Overflow

 

[CJ_London]

or use something like this. Put this in a standard module

Function RowNumber(Optional r As Variant = -1) As Variant
Static X As Long

    If r = -1 Then
    
        X = 0
    Else
    
        X = X + 1
        
    End If
    
    RowNumber = X
    
End Function

and call it in your query

SELECT *, RowNumber([somefield]) AS RowNum
FROM myTable
WHERE RowNumber()=False

you need the where clause to reset the count. somefield can be any field you like, doesn't have to be unique

note that clicking on a row will trigger the function for that row again so rownum will change to a higher number. However you can sort away from the column headers and the rownum will remain consistent.

 

[oleronesoftwares]

Here is a walk around you can try.
1. Add a number column to the table, you can name it countofrecords
2. Put the default value as 1
3. Run an update statement that sets the value of the field(for the records in the table) to 1
4. You can now use the dsum function in your query, using countofrecords as parameter, and table name(running total option has to be chosen)

Check this link below to see sample of dsum function with running total

Create a running totals query - Microsoft 365 Apps

Demonstrates two methods that you can use to create a running totals query in Microsoft Access.

docs.microsoft.com

 

[Pat Hartman]

1. What is the exact purpose this sequence number serves in a query? This type of function is expensive and if you don't need it for something (other than the user likes to see it), don't do it in a query.
2. What dictates the sequence you actually want? As you have noticed, calculating a sequence number REQUIRES a unique PK or sort sequence. It doesn't have to be an autonumber but it does have to be unique because otherwise, you can't count the number of rows less than the current value. You might be able to create a unique field out of multiple fields if the string is really unique by concatenating the fields and using that value as you comparison to get a count.

If you want "permanent" sequence numbers, they can be calculated when the data is stored and they won't change and your queries won't slow to a crawl when they return more than a few rows.

 

[Leo_Polla_Psemata]

1. What is the exact purpose this sequence number serves in a query? This type of function is expensive and if you don't need it for something (other than the user likes to see it), don't do it in a query.
2. What dictates the sequence you actually want? As you have noticed, calculating a sequence number REQUIRES a unique PK or sort sequence. It doesn't have to be an autonumber but it does have to be unique because otherwise, you can't count the number of rows less than the current value. You might be able to create a unique field out of multiple fields if the string is really unique by concatenating the fields and using that value as you comparison to get a count.

If you want "permanent" sequence numbers, they can be calculated when the data is stored and they won't change and your queries won't slow to a crawl when they return more than a few rows.

Hi
The purpose of this is, we convert several queries to edi and upload immediately to a system.
However, the layout of this specific query, contains an number of the record. its record.
Because two years ago, when i "tailored" the old database, i couldn't make this AN field, i knew it was very complicated, i skipped it.
We used a bypass method through excel, we retrieve the query layout copy-paste in an excel, the make manually the AN in excel
and convert excel to edi. No big deal, it is just a bit more "dapa dupa" .
For 2022, i create the new db, i try to improve several issues of the old one. this is all.

 

[Pat Hartman]

It is far better to assign the number when you add the row to the table. That way the number never changes. Here's a database with two different samples. You probably want the one from the subform.