Function that inserts incremental values

[ppataki]

Dear All,

I have a query that has one output field
I would like to have a VBA function that if I apply in the second field (Field2) it just writes numbers from 1 to as many records there are in the query
(I dont want to sort my data, I just want those numbers to be added)

eg.
Field1 Field2
Y 1
X 2
Z 3

Could you please advise?
Many thanks in advance

 

[MarkK]

Unless your sequential numbers are an essential and meaningful part of the data--and in a database system they rarely are--then don't bother with them. How does it add value to a list that the eleventh item has "11)" in front of it?

 

[ppataki]

The data that I am working with is really funky and unfortunately this cannot be changed
I have many queries having the same number of records but there is no field to make a join
So I would need a field that I could use for creating a join
I have had a workaround with creating temp tables with Autonumber fields but there is a lot of extra work with it and I feel that with a function it would be much easier (avoiding temp tables), I just dont know how to start...

Cheers

 

[boblarson]

Well, how do you make a field to do a join when the records returned are not necessarily in the same order each time? Tables don't store the data in any particular order (contrary to how it sometimes looks) and queries don't return it in any particular order UNLESS you order it by a specific field or fields.

The data you have is useless at being joined with another table because at any one time the data coming out can be in a any particular order.

 

[ppataki]

The order of the records returned is always OK, I do not have to sort it

 

[boblarson]

The order of the records returned is always OK, I do not have to sort it

That is only a coincidence. It is not necessarily always that way. You don't understand how records are stored in the tables then. It can show up this way in 9 out of 10 times but how do you know the 10th time isn't going to be the one you use. Again, I will state it - the data that is in the tables is NOT STORED IN ANY PARTICULAR ORDER. What you see when you open a table is not the table but a special type of QUERY which lets you see the data that is stored. What you see is not necessarily what you will get. PERIOD! If you want to guarantee that the records have order then a date/time stamp or a numbering sequence MUST be added at the time the records are entered AND then that used in a query to sort them.

 

[boblarson]

But now you've been warned.

So you can look here and see if this helps with the numbering.

 

[ppataki]

Thanks! The problem is that if I apply the subquery it will add numbers to the records alphabetically and not in the order of the records
eg
Field1 Field2 (created with subquery)
A 1
C 3
B 2

I would need
A 1
C 2
B 3
instead
or maybe I did something incorrectly...

 

[Banana]

As long you keep in mind Bob's warning and treat it as a display only thingy, this may work:

Private MyNumber As Long

Public Sub ResetNumbering()
  MyNumber = 0
End Sub

Public Function RowNum(varRow As Variant) As Long
  MyNumber = MyNumber + 1
  RowNum = MyNumber
End Function

In your query:

SELECT RowNum(ID), ID...
FROM aTable;

HTH.

 

[ppataki]

This works perfectly!
Thank you very much!!!

 

[ppataki]

Unfortunately it turned out that I cannot make joins between queries where I used this code as the numbering always changes from query to query (it jumps one increment upwards at each run)
Any advise please?
Many thanks

 

[Banana]

Try this.

SELECT RowNum(ID), *
FROM (
   SELECT ...
   FROM ...
   JOIN ...
   ON ...
);

 

[Banana]

For others' benefit, I worked out a solution that makes it easy to work exclusively within queries and won't require a external call in VBA to reset the numbering as well avoid losing the number value when viewing via datasheet which causes a repeating call to the function:

Private i As Long
Private c As Collection

Public Function ResetNumber() As Boolean
    i = -1
    Set c = Nothing
    Set c = New Collection
    ResetNumber = True
End Function

Public Function RowNum(varPrimaryKey As Variant) As Long
On Error Resume Next
    If c(CStr(varPrimaryKey)) = 0 Then
        i = i + 1
        c.Add i, CStr(varPrimaryKey)
        RowNum = i
    Else
        RowNum = c(CStr(varPrimaryKey))
    End If
End Function

To use it in a query:

SELECT RowNum([PrimaryKey]), ...
FROM ...
WHERE ResetNumber();