Re: Query doesn't always sort

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

<sam.alame@xxxxxxxxx> wrote in message
news:1178131075.530557.54980@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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 data*** 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

On May 2, 9:05 am, "John Spencer" <spen...@xxxxxxxxx> wrote:
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 data*** view.
If
you don't impose an order on the data*** 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
.

<sam.al...@xxxxxxxxx> wrote in message

news:1178108659.966528.243140@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx



John,

Do you mean that I would have to create a blank table in a query and
then append the data to it, as per Gary's suggestion? Or do you have
another workaround?

Thanks,
Sam

On May 1, 2:47 pm, "John Spencer" <spen...@xxxxxxxxx> wrote:
I just realized that you are using the query to do a make table.

============ Speculation follows ===============
Make table queries can more or less ignore the sort order when they
are
creating the table. Even if the records were written in the sorted
order,
they may or may not be written to the disk in any specific order as
far
as
disk storage order goes. Sowhen they are retrieved they aren't
retrieved
in
the write order but in disk access order (if no specific order is
available).

If you have a primary key then Access will use that as the default
order
when it displays the table in Data*** view.
=========== End of speculation =================

BUT tables have no inherent order. If you want to impose a reliable
order
you must sort the data in a query based on the table you have created.

Sorry, but that is the way it is (at least as far as I know).
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

<sam.al...@xxxxxxxxx> wrote in message

news:1178041281.145939.293740@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

John,

From my experience and that of my collegues with a similar problem,
this sometimes occurs even after a compact and repair. Also, it
seems
to be happening with multiple databases on a regular basis, so I
don't
see this as a corrupt database issue.

Any other ideas?

Thanks,
Sam

On Apr 27, 2:22 pm, "John Spencer" <spen...@xxxxxxxxx> wrote:
A couple of things to try.

Try using DISTINCTROW in the query. That may take care of the
problem.

It is possible that an index has gotten corrupted.
Try removing all indexes on the table, doing a compact and repair,
and
then
restoring the indexes. Perhaps you have a corrupted index.

SELECT DISTINCTROW P.GEOSORT, P.TERR_CODE_IND AS TERR_CODE,
P.GEO_CODE,
P.GEO_DESC, P.PRODUCT_SHORT_NAME, P.M23VA, P.M22VA, P.M21VA,
P.M20VA,
P.M19VA, P.M18VA, P.M17VA, P.M16VA, P.M15VA, P.M14VA, P.M13VA,
P.M12VA, P.M11VA, P.M10VA, P.M9VA, P.M8VA, P.M7VA, P.M6VA, P.M5VA,
P.M4VA, P.M3VA, P.M2VA, P.M1VA, P.CMTHVA, P.M23VC, P.M22VC,
P.M21VC,
P.M20VC, P.M19VC, P.M18VC, P.M17VC, P.M16VC, P.M15VC, P.M14VC,
P.M13VC, P.M12VC, P.M11VC, P.M10VC, P.M9VC, P.M8VC, P.M7VC, P.M6VC,
P.M5VC, P.M4VC, P.M3VC, P.M2VC, P.M1VC, P.CMTHVC, P.M23VSAA,
P.M22VSAA, P.M21VSAA, P.M20VSAA, P.M19VSAA, P.M18VSAA, P.M17VSAA,
P.M16VSAA, P.M15VSAA, P.M14VSAA, P.M13VSAA, P.M12VSAA, P.M11VSAA,
P.M10VSAA, P.M9VSAA, P.M8VSAA, P.M7VSAA, P.M6VSAA, P.M5VSAA,
P.M4VSAA,
P.M3VSAA, P.M2VSAA, P.M1VSAA, P.CMTHVSAA, P.M23VSAC, P.M22VSAC,
P.M21VSAC, P.M20VSAC, P.M19VSAC, P.M18VSAC, P.M17VSAC, P.M16VSAC,
P.M15VSAC, P.M14VSAC, P.M13VSAC, P.M12VSAC, P.M11VSAC, P.M10VSAC,
P.M9VSAC, P.M8VSAC, P.M7VSAC, P.M6VSAC, P.M5VSAC, P.M4VSAC,
P.M3VSAC,
P.M2VSAC, P.M1VSAC, P.CMTHVSAC INTO SORTED_P
FROM P
ORDER BY P.GEOSORT, P.TERR_CODE_IND, P.GEO_CODE, P.GEO_DESC,
P.PRODUCT_LOGICAL_ORDER;
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

<sam.al...@xxxxxxxxx> wrote in message

news:1177692240.921476.34240@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

John, thanks for your response.

It's not sorted because, in the data*** view of the table, some
lines are clearly out of place and not sorted. I need to deliver
these
tables directly to my client, but sorted in a particular way. The
sort
works most of the time, but it sometimes leaves a big chunk of
the
table unsorted. When I see it, I usually rerun the query once or
twice
and it works. The problem is that I can't automate the entire
procedure through a macro because of this.

Any advice would be appreciated.

On Apr 27, 11:55 am, "John Spencer" <spen...@xxxxxxxxx> wrote:
Why do you say it doesn't always sort? Do you see the behavior
when
you
use
the query as the source for a report? If so, then you need to
be
aware
that
reports more or less ignore any sorting done by the query. You
need
to
use
the report's Sorting and Group (View: Sorting and Grouping) to
set
up
the
sorting.

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

<sam.al...@xxxxxxxxx> wrote in message

news:1177676065.778413.252420@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

Hello,

I have an access query that I run on a regular basis but that
doesn't
always sort. It sorts most of the time, but once in a while it
won't,
which makes it really annoying and impractical.

Does anybody know why this is happening?

Thank you.

The query is:

SELECT P.GEOSORT, P.TERR_CODE_IND AS TERR_CODE, P.GEO_CODE,
P.GEO_DESC, P.PRODUCT_SHORT_NAME, P.M23VA, P.M22VA, P.M21VA,
P.M20VA,
P.M19VA, P.M18VA, P.M17VA, P.M16VA, P.M15VA, P.M14VA, P.M13VA,
P.M12VA, P.M11VA, P.M10VA, P.M9VA, P.M8VA, P.M7VA, P.M6VA,
P.M5VA,
P.M4VA, P.M3VA, P.M2VA, P.M1VA, P.CMTHVA, P.M23VC, P.M22VC,
P.M21VC,
P.M20VC, P.M19VC, P.M18VC, P.M17VC, P.M16VC, P.M15VC, P.M14VC,
P.M13VC, P.M12VC, P.M11VC, P.M10VC, P.M9VC, P.M8VC, P.M7VC,
P.M6VC,
P.M5VC, P.M4VC, P.M3VC, P.M2VC, P.M1VC, P.CMTHVC, P.M23VSAA,
P.M22VSAA, P.M21VSAA, P.M20VSAA, P.M19VSAA, P.M18VSAA,
P.M17VSAA,
P.M16VSAA, P.M15VSAA, P.M14VSAA, P.M13VSAA, P.M12VSAA,
P.M11VSAA,
P.M10VSAA, P.M9VSAA, P.M8VSAA, P.M7VSAA, P.M6VSAA, P.M5VSAA,
P.M4VSAA,
P.M3VSAA, P.M2VSAA, P.M1VSAA, P.CMTHVSAA, P.M23VSAC,
P.M22VSAC,
P.M21VSAC, P.M20VSAC, P.M19VSAC, P.M18VSAC, P.M17VSAC,
P.M16VSAC,
P.M15VSAC, P.M14VSAC, P.M13VSAC, P.M12VSAC, P.M11VSAC,
P.M10VSAC,
P.M9VSAC, P.M8VSAC, P.M7VSAC, P.M6VSAC, P.M5VSAC, P.M4VSAC,
P.M3VSAC,
P.M2VSAC, P.M1VSAC, P.CMTHVSAC INTO SORTED_P
FROM P
ORDER BY P.GEOSORT, P.TERR_CODE_IND, P.GEO_CODE, P.GEO_DESC,
P.PRODUCT_LOGICAL_ORDER;- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -




.