470,586 Members | 1,302 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,586 developers. It's quick & easy.

Access 2003 Table not always sorting data properly

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!!!!
Jun 22 '07 #1
1 3600
14,534 Expert Mod 8TB
The Jet Engine will impose its own sort order on unsorted data. If you want to get data in a particular order then you will have to create a query and sort the data before exporting it.
Jun 22 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

14 posts views Thread by Sean C. | last post: by
7 posts views Thread by manning_news | last post: by
52 posts views Thread by Neil | last post: by
4 posts views Thread by lupo666 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.