Autonumber in a query containing text field

[ppataki]

Dear All,

I have a query that has only one field and I would like to add a new field that displays the number of the record

I have managed to do this for numeric fields with the help of a subquery like this:

SELECT [Copy Of qapp_ext_cikk].Cikkszam, (SELECT Count(*)FROM [copy of qapp_ext_cikk] AS M1 WHERE [cikkszam]>[copy of qapp_ext_cikk].[cikkszam])+1 AS test
FROM [Copy Of qapp_ext_cikk];

and it works fine

My question is: how can I do this if my field is TEXT and not numeric?

Many thanks in advance

 

[jdraw]

Here's an article by Allen Browne on Ranking with Access
http://allenbrowne.com/ranking.html

 

[ppataki]

Thanks, but the problem is that the article is also only about numeric fields

 

[jdraw]

Well, you could create a temporary table (TempTable) with 2 fields

fld1 Autonumber
fld2 text

Then use an append query

INSERT INTO TempTable (fld2)
SELECT [Copy Of qapp_ext_cikk].Cikkszam
FROM [Copy Of qapp_ext_cikk];

Then

SELECT fld1, fld2 from TempTable

or some variation of this depending on your circumstances.

 

[ppataki]

This is exactly what I have done to solve this, I was just curious if that can be done with a single query instead of creating temp tables

Or maybe this can also be solved with VBA to write a function that just generates increments starting at 1 to the end of the recordset...?

Thank you!

 

[JANR]

Perhaps Lebans serialize function might help: http://www.lebans.com/rownumber.htm

JR

 

[ppataki]

That works perfectly!!!
Many thanks!!!

 

[ppataki]

It turned out that it does not work that well unfortunately
If the keyname and keyvalue are null values or the same, the function outputs same numbers or zeros

Any advise please?

Thx!