query to assign row number?

[chris klein]

any way (access 2002) to design an update query that assigns to a number field the sequence determined by the sorting criteria in that query?

i.e. if I sort by name, date, can the resulting sequence see on-screen (row 1, row 2, etc.) be assigned as an update to a NUMber field?

thanks

 

[Dennisk]

I'm not quite sure what you are after but If you require to sort and renumber this can be achieved in a query. You will require a sub query.

Here is the basic outline.

it uses the Authors table wich consists od an numeric ID and Author name

Non Unique Sequence
SELECT (SELECT count(au_id) FROM Mytable1 AS x WHERE x.au_id<= y.au_id) AS Sequence, au_id, authors_name
FROM Mytable1 AS y
ORDER BY au_id;

Unique Sequence
SELECT TOP 100 (select top 100 count(b.authors_name) as c
from MyTable2 b
where b.authors_name<=a.authors_name
) AS rowN, a.*
FROM MyTable2 AS a
ORDER BY a.authors_name;

 

[chris klein]

Dennisk: thanks for the response. I played around with it some, but was hindered by the fact that my database has no field with unique values in each record (except the primary key, which is not the sort I need). I need to order by using four different fields. I finally found an excellent solution to my problem at:
http://www.trevor.easynet.co.uk/AccFAQ/queries.htm#counter
Best wishes, CWK

 

[chris klein]

Thanks for response Dennisk
I played around with the sql you suggested, but was unable to make it work for my need because I need to ORDER by using a sequence of four field names (order by a,b,c,d). Finally found a good solution at:

http://www.trevor.easynet.co.uk/AccFAQ/queries.htm#counter