Re: Query doesn't always sort

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




.