Sort Table

[gaugust]

I would like to sort the underlying table so that when viewed it is in sorted
order. My code (below) is currently sorting a table and creating a new sorted
table. I really do not want to have 2 tables however. I tried using SELECT
.... FROM Case1 ORDER BY random asc, but it does not work. This should be easy
to do but have not found the code to do this. Is there an easy way to do
this? Thanks.

Dim lstrSQL As String
Dim qryDef As QueryDef
lstrSQL = "SELECT Control, LName_Con, FName_Con, MRN_Con, ICUdt_Con,
DCdt_Con, EXPTime_Con, Random, Indexdt_Con "
lstrSQL = lstrSQL & "INTO Case1Sorted FROM Case1 Order by Random asc"
DoCmd.RunSQL lstrSQL

 

[Damian S]

Hi,

What are you trying to achieve?

Returning records from a table and sorting them is very simple. Where are
you using your query? If you are setting a sort order in a query, then using
it in a report (for instance), the report settings will over-ride your query
settings with respect to sort order.

You definitely DO NOT need to create a new table and insert "sorted" records
into it.

Please provide further info about what you are using your query for.

Damian.

 

[John W. Vinson]

I would like to sort the underlying table so that when viewed it is in sorted
order.

You can't.

A Table is an unordered "heap" of data. It HAS no order. Access will display
the records in whatever order it finds convenient - often in Primary Key order
but you can't count on it.

If you want to see records in a particular order, you must - no options! - use
a Query sorting them in that order.

You can do anything with a Query that you can do with a Table, though - base a
form on it, edit data, add and delete records, export it.

John W. Vinson [MVP]

 

[gaugust]

I am creating several tables in a programming loop using:

DoCmd.CopyObject , Control, acTable, "tblControlStub"

the variable Control gets assigned in the loop to a table name. I don't know
at design time how many tables will be created or what there names will be.
The number of tables depends on the number of records in table Cases I am
reading from. I create one table for each record in table Cases and the name
of the new table comes from the key field in the Cases table. After the table
is created I load the table name into an array. I can use this array for
further table processing if needed, such as sorting the tables. All the
tables created have the same fields and format taken from the shell
"tblControlStub" In the programming loop I have an SQL INSERT statement that
loads the new table(s) with records. What I am trying to achieve is having
the records in the new table(s) in sorted order by the field Random, which is
a random number.

I can achieve this through menus by opening one of the new table(s),
clicking on the random column, clicking "sort ascending" on the the menu bar
and then saving the table. Then when the table is reopened it is in sorted
order. I need to achieve the same outcome, but I need to do it in code.
Thanks.

 

[JorgeLagos]

I would like to sort the underlying table so that when viewed it is in sorted
order. My code (below) is currently sorting a table and creating a new sorted
table. I really do not want to have 2 tables however. I tried using SELECT
... FROM Case1 ORDER BY random asc, but it does not work. This should be easy
to do but have not found the code to do this. Is there an easy way to do
this? Thanks.

Dim lstrSQL As String
Dim qryDef As QueryDef
lstrSQL = "SELECT Control, LName_Con, FName_Con, MRN_Con, ICUdt_Con,
DCdt_Con, EXPTime_Con, Random, Indexdt_Con "
lstrSQL = lstrSQL & "INTO Case1Sorted FROM Case1 Order by Random asc"
DoCmd.RunSQL lstrSQL

 

[JorgeLagos]

I populated a table at running time first by looping and performing
calculations from a recordset. When a record it is ready I send it to an
empty existing table using the INSERT command. My question is if I can sort
the resulting table from my VB code

 

[John W. Vinson]

My question is if I can sort
the resulting table from my VB code

No.

A table HAS NO ORDER.

A table CANNOT BE SORTED.

If you want to see records in some particular order, your *only* option is to
create a Query with an Order By term (or a Report with its sorting and
grouping specified) and display the data from the Query or Report.

John W. Vinson [MVP]