A query ranking numbers grouped by date. 1

[Pantoner]

In a query with two fields using Access. The first field is a date column. The second field is a number from 1-100. <br>
<br>
For each date of the first field there are from 6 to 12 entries with the same date and those dates have corresponding numbers in field 2. <br>
<br>
I would like to assign a number from 1 to 10 with 1 being the max and 12 being the min for the field of numbers. Ranking the numbers in field 2 as a group from 1-12 only as they have the same date. <br>
<br>
ie: field one has 9 rows with the date Dec 26 1998 I need to rank the 9 corresponding numbers in field 2 from<br>
1-9. Next in field one there are 10 rows with the date Dec 27 1998. I need to rank the 10 corresponding numbers in field 2 from 1-10. And so on through out the database. <br>
<br>
How do I write a query in SQl for Microsoft Access to do this?

 

[bitbrain]

Can't think of a pure SQL solution. You may need to write some VBA code.<br>
<br>
You could write a function that reads the table, orders by date & number, then assigns the rank by incrementing a counter. Reset the counter at break of date. The function could update the row with a rank column.<br>
<br>
Or, have then function return a recordset object with the date, number, and rank columns.<br>

 

[calahans]

I'm not entirely clear as to your problem but could you try somthing like the following:<br>
<br>
select date,number from table<br>
order by date asc, num desc<br>
<br>
This would put all like dates together then order the second field within these dates going downwards. Is this a help?<br>