numbering each reccord in a query?

  • Thread starter Thread starter jan hendrickx
  • Start date Start date
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.
 
You can use a subquery similar to
SELECT tableA.*,
(SELECT Count(*) FROM tableA A WHERE A.Fielda >= tableA.Fielda) as Rank
FROM tableA;
 
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.
 
TRY (no guarantee) sorting by the Length of the field and then by the word

ORDER BY Len(Word), Word
 
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;
 
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.
 
Back
Top