Why doesn't the ORDER BY function work properly?

[Guest]

I import into Micorsoft Access a 200,000 record CSV file using VBA code
executed from within a form module. My biggest problem is that when I use an
SQL statement with the ORDER BY function to create a backup table of the
imported file, the new table is sorted incorrectly. Here is an example of
the recurring dillema: I execute the SQL statement, using the INSERT and
ORDER BY commands to create a new, sorted backup table of the original
imported table. Well, after the SQL command was executed, records 17,394 to
17,744 were placed at the very top of the new table (in chronological order),
while the other 199,000 records are placed in chronological order starting
immediately below the misplaced records. I am using Access 2002. My data
requires the records to be sorted correctly. I am sorting by a date/time
field. After nearly a hundred tests, I still have never seen the new table
sorted correctly. This seems to be an inherent flaw in Access. How can I
fix this?

 

[Guest]

You might want to take a look at the question I sent regarding a similar
situation.

Look at the Sort Question post

 

[Kevin K. Sullivan]

This is a common misperception of the definition of "table" in Access.
There is no such thing as a sorted table. A *table* has no internal
sorting logic. Think of it as a bucket of records. Even if you create
a field with unique, ascending values, there is no guarantee that the
order will be preserved when you open the table. Instead of opening
this table, open a *query* based on the table with an ORDER BY clause.
In almost any situation, you can substitute the name of query for the
name of a table. What do you want to do with this data in the long run?
Massage it and export? Do it with the query, not the table.

HTH,

 

[Guest]

Thanks Kevin. That tidbit of information is something I haven't read
anywhere. My problems are solved now.

 

[Guest]

Thanks Michal. My problems are solved now.

You might want to take a look at the question I sent regarding a similar
situation.

Look at the Sort Question post