1
Hi,
I use Access 2003 to generate the back-end data for a ColdFusion report at work. The report is sorted by a column and based on the sorting, rankings are assigned to each row(i.e. the biggest value gets No. 1, and then the row of the second biggest value is ranked as No. 2). Each task owners will then have to attack their owning issues based on the ranking.
The No. 1, 2, 3,... rankings are generated in this way:
1. An Append query that is based on series of tables and sub queries calculates the value, sort the value descending, and then pastes the result to a table.
2. The table has one additional field that the append query does not have - the No 1, 2, 3... rankings.
3. Assuming the table is sorting properly after the data is pasted to the table from the sorted append query, a VB script kicks off to go through the table and assign values to the ranking field of each row from top to the bottom of the table. For example, top item has the highest value so it gets assigned "1" in the ranking field, the second row has the 2nd biggest value so it get assigned "2" to its ranking field, ... etc.
4. Here comes the problem. We run the report hourly. Once or maybe twice in a day, the table takes the pasted info from the append query but the data is not sorted the same as how it is defined in the append query. Then the VB script assigns rankings wrongly.
I have googled a lot but cannot find a simple VB solution to force sorting data in an Access table.
Please he~~~~lp!!!!
