numbering each reccord in a query?

J

jan hendrickx

hi,
i need to add a field to a query so that each record is numbered...

my current query returns data like :
abcde
fghij
klmno
pqrst
.....

i need it like :
1 abcde
2 fghij
3 klmno
4 pqrst
5 .....

i think it should be something like 'Me.CurrentReccord' when using a form
but i need it in a query

how can i do that?

Jan Hx.
 
D

Duane Hookom

You can use a subquery similar to
SELECT tableA.*,
(SELECT Count(*) FROM tableA A WHERE A.Fielda >= tableA.Fielda) as Rank
FROM tableA;
 
J

jan hendrickx

hi again,

this works fine, but it numbers the records in alfabetic order, wich is not
what i need...
i need them to be numbered in order of appearence!
first i show the 3-letter-words, then 4-letters, 5-letters, etc...
each group is ordered alfabeticly
can i adjust the SQL to do this?

tnx !
Jan Hx.
 
J

John Spencer (MVP)

TRY (no guarantee) sorting by the Length of the field and then by the word

ORDER BY Len(Word), Word
 
D

Duane Hookom

You should attempt to include all of your requirements in your intial
posting. Try something like this. You may need to play with the >= vs <=:
SELECT tableA.*,
(SELECT Count(*) FROM tableA A WHERE Len(a.FieldA) >=Len(tableA.FieldA) AND
A.Fielda >= tableA.Fielda) as Rank
FROM tableA;
 
J

Jan Hx.

hi,

this is almost what i need, but...

please look at this example :

Field Rank
aaa 1
bbb 2
ccc 3
aaaa 2
bbbb 4
cccc 6

for 'aaaa' it returns '2' because 'bbb' and 'ccc' alfabeticly apear
after 'aaaa'.

so i changed the SQL to this :

SELECT qryZ1.Woord, ((SELECT COUNT(*) FROM qryZ1 A WHERE
(LEN(A.woord)=LEN(qryZ1.woord)) AND (A.woord<=qryZ1.Woord))+(SELECT
COUNT(*) FROM qryZ1 B WHERE (LEN(B.woord)<LEN(qryZ1.woord)))) AS Rank
FROM qryZ1;

first i count the words that have the same length as the current word
AND alfabeticly apear before the current word
then i add the count of ALL words that have a LESSER length than the
current word...

it is a bit complicated and SLOW against large tables but it WORKS!!!

Thank you for your help, i couldn't have done it without it !!!

Jan Hx.
 
Top