472,780 Members | 1,964 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,780 software developers and data experts.

Access 2003 Table not always sorting data properly

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!!!!
Jun 22 '07 #1
1 3808
MMcCarthy
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

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

Similar topics

14
by: Sean C. | last post by:
Helpful folks, Most of my previous experience with DB2 was on s390 mainframe systems and the optimizer on this platform always seemed very predictable and consistent. Since moving to a WinNT/UDB...
8
by: Mike MacSween | last post by:
tblCourses one to many to tblEvents. A course may have an intro workshop (a type of event), a mid course workshop, a final exam. Or any combination. Or something different in the future. At...
47
by: ship | last post by:
Hi We need some advice: We are thinking of upgrading our Access database from Access 2000 to Access 2004. How stable is MS Office 2003? (particularly Access 2003). We are just a small...
24
by: Bob Alston | last post by:
Most of my Access database implementations have been fairly small in terms of data volume and number of concurrent users. So far I haven't had performance issues to worry about. <knock on wood> ...
7
by: manning_news | last post by:
I've got a report that's not sorting correctly. I build a SQL statement and assign it to the recordsource in the Open event, sorting the data the way the user chooses. The user can choose up to 3...
52
by: Neil | last post by:
We are running an Access 2000 MDB with a SQL 7 back end. Our network guy is upgrading to Windows Server 2003 and wants to upgrade Office and SQL Server at the same time. We're moving to SQL Server...
4
by: lupo666 | last post by:
Hi everybody, this time I have three problems driving me nuts :-((( (1) I have a report with 20 or so Yes/No "squares". Is there a way to either hide/show the "square" or change the yes/no...
6
by: jsacrey | last post by:
Hello everybody, I've got a bit of a situation that I could use some guidance with if possible. I work for an auditing firm where my users audit electronic shipping data for customers to see if...
2
by: Bob Laubla | last post by:
Hello I have a very complex maketable query with many records and involving multiple VB functions which call other functions. I need this table to be sorted by the first field. But no matter what...
0
by: Rina0 | last post by:
Cybersecurity engineering is a specialized field that focuses on the design, development, and implementation of systems, processes, and technologies that protect against cyber threats and...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 2 August 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...
0
linyimin
by: linyimin | last post by:
Spring Startup Analyzer generates an interactive Spring application startup report that lets you understand what contributes to the application startup time and helps to optimize it. Support for...
0
by: erikbower65 | last post by:
Here's a concise step-by-step guide for manually installing IntelliJ IDEA: 1. Download: Visit the official JetBrains website and download the IntelliJ IDEA Community or Ultimate edition based on...
0
by: kcodez | last post by:
As a H5 game development enthusiast, I recently wrote a very interesting little game - Toy Claw ((http://claw.kjeek.com/))。Here I will summarize and share the development experience here, and hope it...
0
by: Taofi | last post by:
I try to insert a new record but the error message says the number of query names and destination fields are not the same This are my field names ID, Budgeted, Actual, Status and Differences ...
5
by: DJRhino | last post by:
Private Sub CboDrawingID_BeforeUpdate(Cancel As Integer) If = 310029923 Or 310030138 Or 310030152 Or 310030346 Or 310030348 Or _ 310030356 Or 310030359 Or 310030362 Or...
0
by: lllomh | last post by:
How does React native implement an English player?
0
by: Mushico | last post by:
How to calculate date of retirement from date of birth

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.