Query doesn't always sort

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

wrote in message
ups.com...
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" 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 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
.

wrote in message

oups.com...



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

wrote in message

roups.com...

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.

On Apr 27, 11:55 am, "John Spencer" 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
.

wrote in message

groups.com...

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 -

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


sam.alame wrote:
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


On May 2, 6:44 am, "Gary Walter" wrote:
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.

sam.alame wrote:





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

On Apr 29, 7:04 pm, "Gary Walter" wrote:
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

"JensB" wrote

Yes
The original fieldname does not exist in your result.
JensB
wrote in message
oups.com...
JensB, thanks for your response. So all I need to do is order by
the
alias as opposed to the original field name?

On Apr 27, 10:37 am, "JensB" wrote:
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

wrote in message

egroups.com...

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 -

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

On May 2, 9:51 am, "Gary Walter" wrote:
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...



sam.alame wrote:
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

On May 2, 6:44 am, "Gary Walter" wrote:
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.

sam.alame wrote:

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

On Apr 29, 7:04 pm, "Gary Walter" wrote:
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

"JensB" wrote

Yes
The original fieldname does not exist in your result.
JensB
wrote in message
oups.com...
JensB, thanks for your response. So all I need to do is order by
the
alias as opposed to the original field name?

On Apr 27, 10:37 am, "JensB" wrote:
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

wrote in message

egroups.com...

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 -

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

On May 2, 9:05 am, "John Spencer" 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 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
.

wrote in message

ups.com...



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

wrote in message

groups.com...

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

wrote in message

roups.com...

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.

On Apr 27, 11:55 am, "John Spencer" 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
.

wrote in message

groups.com...

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 -

sam.alame wrote:
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?


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

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

wrote in message
ps.com...
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

On May 2, 9:05 am, "John Spencer" 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 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
.

wrote in message

ups.com...



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

wrote in message

groups.com...

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

wrote in message

roups.com...

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.

On Apr 27, 11:55 am, "John Spencer" 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
.

wrote in message

groups.com...

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 -

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