Re: Order of records in table as a result of a Query

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

It will. A query will be displayed (and exported) in the order specified.

It is actually writing the records in the order you specify, but the table
does not retain that information.

--
Hope that helps!

RBear3
..

"vtj" <vtj@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:696DBE4D-9A2C-4A50-9796-9A9BA5D6D552@xxxxxxxxxxxxxxxx
I'd be happy to export the query except that it won't be in the order I'm
looking for. The ORDER BY is not working to write records into a table so
why would it work for the query when it is the same query?

"RBear3" wrote:

Again, why create a table? Save the query, then EXPORT THE QUERY. It
will
be in the order you wish.

--
Hope that helps!

RBear3
..

"vtj" <vtj@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:9728503D-A227-48E8-BFBA-FAC17BD2333D@xxxxxxxxxxxxxxxx
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" wrote:

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" <vtj@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:FCA8CD1D-2E7C-4C8F-A502-24043C31D87F@xxxxxxxxxxxxxxxx
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.

"RBear3" wrote:

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
..

"vtj" <vtj@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:B679B3D5-2C8B-4E74-9C80-5FAE8FA2FBE2@xxxxxxxxxxxxxxxx
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];












.

Relevant Pages

  • Re: Sorting Query Results - Advice Please
    ... Can the ORDER BY Clause only be entered directly into the SQL Satement. ... Then I created a query to sort alphabetically by Lastname and then Firstname ...
    (microsoft.public.access.queries)
  • RE: Any good T-SQL quick reference recommended?
    ... The full syntax of the SELECT ... SELECT Clause ... Specifies the columns to be returned by the query. ... Specifies that duplicate rows can appear in the result set. ...
    (microsoft.public.sqlserver.programming)
  • Re: Order of records in table as a result of a Query
    ... That is a whole world of difference in the meaning "not important": ... Why don't you simply export the results of your query? ... AL EXPORT in the same order as they exist in AL EXPORT SORT. ... ORDER BY clause is not being used at all. ...
    (microsoft.public.access.queries)
  • Re: Using a Select Query to build a MDB table from a Fox table
    ... MS Access has a 'make-table' query with IN clause you can specify ... As you said the IN <database> clause is specific to Access SQL. ...
    (microsoft.public.fox.helpwanted)
  • Re: Sorting Query Results - Advice Please
    ... If you enter a query in SQL view, you can USUALLY select VIEW: Design from the menu and see how you would have to do it using the design view. ... Can the ORDER BY Clause only be entered directly into the SQL Satement. ... Then I created a query to sort alphabetically by Lastname and then Firstname order. ...
    (microsoft.public.access.queries)