Query doesn't always sort

S

sam.alame

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;
 
J

JensB

Try to use the alias for P.TERR_CODE_IND like this:
ORDER BY P.GEOSORT, TERR_CODE, P.GEO_CODE, P.GEO_DESC,
P.PRODUCT_LOGICAL_ORDER

JensB
 
J

John Spencer

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

sam.alame

JensB, thanks for your response. So all I need to do is order by the
alias as opposed to the original field name?
 
S

sam.alame

John, thanks for your response.

It's not sorted because, in the datasheet 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.
 
J

John Spencer

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

John W. Vinson

It's not sorted because, in the datasheet 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.
Click to expand...

Two problems here:

An Access Table has no controllable order. It's an unordered "heap" of data.
It may be presented in Primary Key order, if it suits the whim of the query
optimizer, or it may be presented in some other order.

And, a Table has no independent existance outside a database anyway. What
exactly are you delivering to the client? An Access .mdb file? An exported
Table? A report? or what? The export or report can be based on your Query, or
sorted using the Report's Sorting and Grouping feature; but you cannot (in any
practical way) change the order of records in the Table, and your client
should never see the table datasheet in any case!

John W. Vinson [MVP]
 
G

Gary Walter

Hi Sam,

Pardon me for jumping in...

I wonder if JensB is maybe thinking of SQL Server?

I don't believe the query is from SQL2K since
it does not start with "TOP 100%" which is
necessary for ORDER BY in SQL2K
(I don't work w/ newer version of SQL Server
so don't know if that has changed).

With Jet queries, you cannot use alias in
ORDER BY clause.

ORDER BY tangents:

-- In a UNION query, you can set an alias
in the first SELECT, then at the end of all
SELECTs, you do order by that alias.

-- Your ORDER BY can use the ordinal position
of the field in the SELECT query.

SELECT f1, f2
FROM tbl
ORDER BY 2;

would sort by f2.

I defer to both John's posts (they know much
more than I), but wonder about 2 things:

1) Why is

P.PRODUCT_LOGICAL_ORDER

in the ORDER BY
but not in the SELECT clause?

Would it hurt to put it in the SELECT clause
and test for consistency?

Or just leave it out of ORDER BY and test?

2) Why use a make table?

Have you tried emptying the table in code,
then appending new data, where your append
query also includes field mentioned above in
SELECT clause (so also in empty table) ?

Good luck,

gary
 
J

John Spencer

Gary,
Seeking enlightenment

I've never had to use TOP 100% in MS SQL 2000 queries in order to use an
ORDER BY clause or when using ODBC to linked MS SQL tables.

Would you mind expanding on on your statement
"... with "TOP 100%" which is necessary for ORDER BY in SQL2K"?

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

Gary Walter

This is what I was thinking (rightly or wrongly)...

If the query was "within" SQL Server, then alias
gets treated differently than in a Jet query.

If I create a View in Enterprise Manager and
apply an ORDER BY (which is discouraged
for this very reason), it will automatically add
the TOP 100%.

I should have just asked if was adp or mdb.
Instead I assumed it was a Jet query due to
the lack of TOP 100%, so alias will act one
way versus loosy-goosy way "within" SQL Server.

A Jet query against a SQL table or View
will be using Jet ORDER BY (so no TOP 100%
is needed).

That's what I thought (rightly or wrongly)...
 
G

Gary Walter

From BOL:

The ORDER BY clause is invalid in views, inline functions, derived tables,
and subqueries, unless TOP is also specified.
 
T

Tom Ellison

Hey, John:

A SQL Server view cannot be ordered unless it uses a TOP, like Gary says. I
believe it has been that way for some time now.

The latest tools for SQL Server queries will ADD "TOP 100" to your view if
you tell it to ORDER BY. Saw this as recently as yesterday.

Tom Ellison
Microsoft Access MVP
 
S

sam.alame

John, what I'm delivering to the client is the table that results from
that make-table query. After that table is produced, I usually import
it into a new .mdb and deliver that one-table .mdb to the client (I
have 20-30 such .mdb's and that is why I'd like to find a way to
automate the process, including the sorts).

Any ideas?
 
S

sam.alame

John,
From my experience and that of my collegues with a similar problem,
Click to expand...
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
 
S

sam.alame

Garry, thanks for your response.

To tell you the truth, this SQL code was not tweaked manually. It's a
direct result of creating a make-table query in the access GUI. Should
I tweak it as per your suggestion? How would that reflect on my
ability to change the query using the Access GUI in the future?

As for the make-table query, it is necessary because: a) the data must
be sorted and b) more than one final table is produced from the same
source tables, depending on which fields are selected.

Your input would be appreciated.

Thanks,
Sam
 
J

John Spencer

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

Gary Walter

Hi Sam,

Okay....
It sounds like you have this need to perform
this task only in the GUI...

One workaround:

Set up your make-table query w/sorting.

Add one more column

Field: 1
Table:
Sort:
Show: <unchecked>
Criteria: 0
Or:

Run your make-table query.

(this will make the table, but not add data)

Change your query to an append query.

Change that column (or just delete it)

Field: 1
Table:
Sort:
Show: <unchecked>
Criteria: 1
Or:

Run the append query.
 
S

sam.alame

Thank you very much Gary; I will ask my colleague to give this a try
with her database today. Since my ultimate aim is to run the entire
process through a macro, do you think it would work equally well if I
created two queries, one to create a blank table and the other to
append? (I don't see why it shouldn't but, given these limitations I
didn't know about, I thought it would be wise to ask!)

Thanks again,
Sam
 
S

sam.alame

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
 
Top