Query doesn't always sort

J

John Spencer

You can try the following if your table structure is always consistent.

-- Define an table structure - TheTableBlank - with all your fields and add
a sequential autonumber field as the primary key
-- Copy the table structure (Docmd.CopyObject
Currentdb().Name,"TheTable",acTable,"TheTableBlank")
-- APPEND your records to the table you have just created

THAT MAY give you the records in sequence. BUT remember TABLES are
unordered. If you want to impose an order you must sort the data either
using a query or in Access imposing a sort order on the datasheet view. If
you don't impose an order on the datasheet view of a table and there is a
primary key in the table, then Access will show the records in primary key
order (at least that is the behavior I have observed)

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
G

Gary Walter

Hi Sam,

If the table already exists then there
is no reason to make it again.

Create a query that will clear that table
(for example if the table name was "Table1"):

qryClearTable1

DELETE * FROM Table1;

then you could in a macro run

qryClearTable1

then the append query you saved...
 
S

sam.alame

Hi Gary,

Your solution seemed to have significantly reduced the number of
unsorted tables produced, but I'm still getting a couple of unsorted
tables here and there (about 2 out of 30). Is there anything else that
you could suggest?

FYI, there is a field in the original table that I am sorting by but
not bringing into the new table. Could this be causing some of the
issues?


Thanks again, your help is much appreciated,

Sam
 
S

sam.alame

Hi John,

My table doesn't have a primary key. The 'unsort' seems to be
completely random. Also, my table structures are not very consistent,
as I'm dealing with quite a few tables in multiple databases.

How do I impose a sort on the datasheet view? Would that actually
order the table? I am asking because after the final tables are
produced, I have a tool that splits and exports the data into Excel,
and I need the data to be sorted over there as well.

Thanks,
Sam
 
G

Gary Walter

Your solution seemed to have significantly reduced the number of
unsorted tables produced, but I'm still getting a couple of unsorted
tables here and there (about 2 out of 30). Is there anything else that
you could suggest?

FYI, there is a field in the original table that I am sorting by but
not bringing into the new table. Could this be causing some of the
issues?
Click to expand...
Quite honestly Sam I don't know (that's why I asked
the question in my first post).

Does append only produce unsorted when
you don't include a sort-by field into the table,
i.e., did you test it by including it in the table?

I said earlier that I defer to the Johns' posts.

A table is just a "repository" of data (a "bag
of marbles" as John V once said). It is not
an Excel spreadsheet.

I personally never expect a table to be sorted.

My users never see a table, only a form or report
that has been sorted via a query (form or control
on a form) or the report's builtin sorting settings.

I do send a lot of properly sorted data to Excel
(and some to xml) that is all sorted via a query.

Tables hold data, queries sort data.

Good luck,

gary
 
J

John Spencer

I'm sorry, but nothing you can do will guarantee that the table is sorted in
any special order.

You have to use a query to sort the data in to your required order and then
export the query. IF you aren't bringing in the fields that you need to
determine the order you need and then using them in a query to export the
data, then as far as I know there is no reliable solution.

As I've repeatedly said, records in tables have no order.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
S

sam.alame

Thank you Gary & John,

I will attempt to tweak the Excel-exporting macro to work for queries.
I'll post a new thread if I come across any trouble with that :)

Thanks again for taking the time.

Sam
 
Top