Sort order in append query

[Guest]

The problem in brief: the order of the data I see in the datasheet view of
the append query is not the order I get in the table after I've run it. The
correct (or desired) order is the one in the datasheet view.

The details: the append query takes a linked text file and a linked SQL
table as sources. The destination is a linked access table in a different
database. The destination table is temporary and all its data is deleted
before a new append is performed. The destination table doesn't have a key.

I've tried this on both access xp and 2003 with the same results.

 

[pietlinden]

data in tables is *un*ordered. If you want it ordered, use a query.
if you want it inserted into the table in a specific order, sort the
append query. If you want the final table to be ordered, you have to
include an identifier key. Sorry, that's the way it is. This is in
Database 101 class.

 

[Guest]

"if you want it inserted into the table in a specific order, sort the append
query. "

That's exactly my problem, though. The data shown in the query is in a
different order than that added to the table. The query _is_ sorted.

 

[Guest]

What pietlinden means is that you need to use a query that is sorted on the
records AFTER you append them to the table. To be pedantic, data in a
relational database management system table is NOT guaranteed to stay in any
order. A table just holds data. You need to use queries, forms, and reports
to turn this data into information such as a certain order to the records.

Now Access seems to keep records in a certain order. For example they are
often displayed in the order of the primary key. Notice that I said
"displayed" and not "stored". Still you can not depend on this ordering
unless you sort them in the query, form, or report. If what you are doing
depends on the records staying in a certain order within the table, you will
have problems.

 

[pietlinden]

"if you want it inserted into the table in a specific order, sort the append
query. "

That's exactly my problem, though. The data shown in the query is in a
different order than that added to the table. The query _is_ sorted.

Well, actually what I said is not quite true. If you had an autonumber
field in your destination table (so that the records were sorted by
that index), then they would, by default, be in insertion order
(because of the PK being an autonumber). But Jerry's right - records
in a table are inherently unordered. Access and Excel are
fundamentally different. Tables are for storage, not really for
viewing. If you want the data sorted, use a query or a report.