By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
457,837 Members | 1,537 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 457,837 IT Pros & Developers. It's quick & easy.

Access 2003 Table not always sorting data properly

P: 1
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
Share this Question
Share on Google+
1 Reply

Expert Mod 10K+
P: 14,534
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.