Is there any way to rank dates using queries (without the use of vba)?
I have a table which contains 10 week dates, by using a query I would like to insert a column with a rank for each date. The rank represents the chronological date ordering and will be used as report headers to form a crosstab query for financial forecast reporting. As the data changes week by week new dates are added to the database.

Date: Rank:
09/08/2004 1
16/08/2004 2
23/08/2004 3
30/08/2004 4
06/09/2004 5
13/09/2004 6
20/09/2004 7

you can use "rownum" will give you the record position.



Gokulan Ethiraj

Could you please give me some examples of "Rownum". The problem is if its VBA which determine the row number, when the query is executed in MS Access, this will lock other users from running the same query.

You could use autonumber in your table, not in the query, as a field if you are entering weeks in order.

You might have:

tblDates
   PK AutoNumber
   Date Date
   Rank AutoNumber

   The problem is if you enter a week out of order, then your Rank gets out of order. You could manually chenge it if necessary.




mmcdonal