In general the ORDER BY is respected, and in fact, it is probably in your
case too, EVEN with the INSERT INTO. But you don't seem to keep in mind the
IMPORTANT fact that a TABLE is NOT a page of paper, neither a recordset,
neither a pile, neither a stack where the record would be nicely positioned
one on top of the other. A table is more like a bag! you insert the marbles,
one by one, into the bag in a given order, great, well, nice, the order of
inputting the marbles, one by one, will be respected. BUT, and that is the
point, when your marbles get picked out of the bag, BY DEFAULT, they could
be put out in ANY ORDER, and NOWHERE it is remembered the order into which
the marbles have been put INSIDE, one by one, into the bag, UNLESS you do
something special (like using a table with an autonumber field). Exactly as
it won't remember WHO, which user, inserted the marble, and so on. YOU have
to TELL it, IF that is important.
So, if your original data has NOT that required information (because it
relays on another principle than the one implied by a table, such a where it
physically appears, in a spreadsheet), you can generate it by appending the
data into a table which has an autonumber. When you insert your data in the
table, with INSERT INTO... ORDER BY, the records will be created, one by
one, accordingly to the order by clause, and the default value, for the
autonumber, will be incremented, automatically for you.
You will be then able to retrieve (for exportation, or whatever reason) the
records, in the order of their creation, by specifying
SELECT ... ORDER BY yourAutoNumberField
You DON'T need that technique IF your original data has already a field that
naturally specifies the order (like records about events, then a date_time
stamp defines the order, already).
Vanderghast, Access MVP
vtj said:
I reiterate, I'm trying to get the text file to be in a particular order.
For the text file to be in order, the table has to be in that order.
Export
does not change the order of the file and even if it did, there is nothing
in
the file at that point to put it in order. The reason that the ORDER BY
clause is included in my example is to create the order. It just doesn't
work. Why? When I build a table without a key (which this doesn't have)
the
order of the records in the table will the order in which they are placed
in
the table. Obviously the INSERT INTO is ignoring the ORDER BY clause. I
just want to know why and how to fix it.
Michel Walsh said:
The order is not important, for the table, while it may be important for
you. That is a whole world of difference in the meaning "not important":
the table may move records, in position, all around, first becoming last
or
whatever. Well, in fact, if you have a primary key, in Jet, the table
'will
appear' to have stored your data in ascending value of the pk.
Anyhow, the records could be pumped back to you in a completely different
sequence than the one you used to append them in the table.
So, always specify an implicit ORDER BY clause, if you want the result to
have its rows to be order:
SELECT *
FROM table
ORDER BY field1, field2
and don't, DON'T relay that records are given back in the same ...
order....
than the one they have been created. That order does NOT MATTER for the
table. A table IS NOT a recordset.
Vanderghast, Access MVP
vtj said:
The order of the records in the table is the order that they are
exported
therefore the order of the records is relevant. The second table has
only
part of the record that is in the first table. At the point of the
second
table (AL EXPORT) there is no longer a method of ordering what is left
but
it
has to be in the proper order in the text file that is output. If
there
is a
way of having the export output be of an order other than that which
the
table is in, I don't know how to do it. The output file is named
something
unique each time a file is exported but until it is the process for
getting
it ready to export is always the same and part of a macro. It is just
easy
to have it come from a separate table.
:
the order of records in a table are not relevant.
Why don't you simply export the results of your query? Why go through
the
extra step of placing the query results in a table first?
--
Hope that helps!
RBear3
..
I am using Access 2000. The following query puts the records into
the
table
AL EXPORT in the same order as they exist in AL EXPORT SORT. The
records
are
created somewhat randomly and put into AL EXPORT SORT. It appears
like
the
ORDER BY clause is not being used at all. Why? How do I fix? AL
EXPORT
is
then in turn exported to a text file which must be in a the order
described
in the ORDER BY clause.
Thank you for your help.
INSERT INTO [AL EXPORT] ( BLOCK1, BLOCK2, [DOC-TRAN-TYPE] )
SELECT [AL EXPORT SORT].BLOCK1, [AL EXPORT SORT].BLOCK2, [AL EXPORT
SORT].[DOC-TRAN-TYPE]
FROM [AL EXPORT SORT]
ORDER BY [AL EXPORT SORT].[DOC-AGENCY-ID], [AL EXPORT
SORT].[DOCUMENT
NUMBER], [AL EXPORT SORT].[DOC-TRAN-TYPE];
