Make Table Query Not Saving Sort Order

[pnpez]

Is anyone having an issue with a query not saving the sort order?

The only way I know to sort is to list the field in the query, check it off as
"show field", then choose it a 2nd time and sort "ascending" and uncheck it for "show field." So the field is listed twice but only shows in the result once, which works fine.

But when I save the query, then go back into it later, the unchecked field goes away, and the resulting table doesn't get sorted properly when I run it.

I can't run this as a report because there are other queries that rely on the output of this result.

Any suggestions would be helpful.

Thanks,
Paul

 

[MSAccessRookie]

Is anyone having an issue with a query not saving the sort order?

The only way I know to sort is to list the field in the query, check it off as
"show field", then choose it a 2nd time and sort "ascending" and uncheck it for "show field." So the field is listed twice but only shows in the result once, which works fine.

But when I save the query, then go back into it later, the unchecked field goes away, and the resulting table doesn't get sorted properly when I run it.

I can't run this as a report because there are other queries that rely on the output of this result.

Any suggestions would be helpful.

Thanks,
Paul

I do not believe that two instances of a column that does not have an aggregate function involved (such as SUM or Count), and that you should be able to add the sorting criteria to the first instance.

I also believe that you have found a case where MS Access JET is trying to be helpful, and reformatting your Query. If that is the case, then perhaps the second instance of the Field (which contains the sorting criteria) is somehow being corrupted.

 

[pnpez]

Thank you. That was quite helpful

 

[dnlbrky]

The title to your post mentions that this is a make table query. It sounds like you are creating a table, and expecting the resulting table to be sorted because the make table query is sorted. I'm not sure if you can do this from a make table--I think the table resorts to a non-deterministic sort order.

If the fields that you want to sort can be part of a multi-field index, I suggest you add an index, since indexes should be sorted. You could use a DDL query to create the table with a CONSTRAINT clause (http :// office.microsoft.com/en-us/access-help/create-or-modify-tables-or-indexes-by-using-a-data-definition-query-HA010206112.aspx), listing each indexed field in the CONSTRAINT clause in the order that you want it sorted. Then you could modify your make table to an update query. If you're not comfortable with that, you can just manually create the index after you've created the table with your existing make table query.