How do I sequentially number my rows in a query? 1

[Cleis]

Group:

How do I sequentially number my rows in a query used on a continuous form? I’d Like some thing like the Row() Function used in Excel? End result would be 1,2,3,4, ect.


Regards!


Itch

 

[traingamer]

For several ways, look in the FAQs for this forum or search for other answers.

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[jedraw]

Cleis,

Here is a sample Table and some SQL that proudces a rownum. May be a starting point.


Rownum:
A way to get a rownum column in output from an Access query (not using recordset and procedure)
against an Access table.

Table: CCL_EST_NM


est_no est_seq est_nm est_typ est_lang
345678000000 10 Diligens Computer Systems O eng
345678000000 11 Diligens L eng
345678000022 12 Seewind Professional L eng
345678000022 13 SeaWind X eng
345678000033 14 Manitoba Chamber Music of Commerce and Systems O eng
345678000033 15 Manitoba Chamber Music of Commerce and Systems L eng

Query: MyRowNum

SELECT CCL_EST_NM.est_no, CCL_EST_NM.est_seq, CCL_EST_NM.est_nm,
CCL_EST_NM.est_typ, CCL_EST_NM.est_lang,
(Select Count (*) FROM [CCL_EST_NM] as Temp
WHERE [Temp].[est_seq] < [CCL_EST_NM].[est_seq])+ 1 AS RowNum
FROM CCL_EST_NM;

Note: est-seq is a field within the Table that has unique values in ascending order. The numbers do not have to be contiguous.

Result:


est_no est_seq est_nm est_typ est_lang RowNum
345678000000 10 Diligens Computer Systems O eng 1
345678000000 11 Diligens L eng 2
345678000022 12 Seewind Professional L eng 3
345678000022 13 SeaWind X eng 4
345678000033 14 Manitoba Chamber Music of Commerce and Systems O eng 5
345678000033 15 Manitoba Chamber Music of Commerce and Systems L eng 6

 

[Cleis]

Okay buddy. I've tries several key word searches. Perhaps you could give me a keyword with which to use? You know the saying to close to the tree's to see the forest.

Thanks!

Itch

 

[Cleis]

Thanks jedraw! You've at least got me pointed in a direction!


Thanks for your time and here is a star for helping!


Regards!

Itch

 

[PHV]

you could give me a keyword with which to use
I've already posted a lot of rank (or ranking) queries here.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886