How are these sorted

  • Thread starter Thread starter Steve J
  • Start date Start date
S

Steve J

Hi
I have atable in which drecords have been entered at random However when i
open a query that is based on that table, the records are being displayed
sorted by the surname field, but if I look in Design view, the surname field
(in fact none of the fields) does not have any sort by attributed set?

How is this please?
A
 
You cannot guarantee which order they will be returned in, unless you
specify an Order By clause within a query.

Normally Access will default to retrieve records in a query based on the PK
of the table.

Either way, best to specify what you want in the Order By
 
Hi Jon

yes I realise that, but what really puzzles me about this instance is that I
can add records to the table with different surnames, and when I open the
query, they are sorted by surname. I have opened and closed the query about
20 times and each time they are sorted, so I am thinking there has to be
something I am missing?

S
 
Steve,

Care to enlighten? Having raised and resolved the question, maybe others of
us would be interested in what youv'e learned.
 
Of course - apologies -
didn't realise that if you right click on a column and change the data
order, Access retains this information and order next time dataview is
opened.
 
Your problem sounds as though it's related to mine. I changed the column order
in Datasheet view. Now I can't get things back so the order is controlled by
what's shown in Design view.

I've had no response to my previous post on this, but I have my fingers
crossed <g>.
 
Best way I've found to solve this problem is to copy the SQL view (text) and
paste it into a new query. Save the new query and when you are happy with it,
delete the old - source - query; and finally rename the new query with the
appropriate name
 
The Order By Property of the Query is automatically saved.

Had you looked at the "Order By" row of the Query Properties window, you
would have seen the entry there.
 
Another way is to open the DatasheetView of the Query and simply move the
Column(s) back to whatever the order you want.
 
I did that, but it doesn't restore the normal behavior. The way I read it, you
are supposed to be able to change the column order in any of the 3 views --
design grid, SQL, or datasheet -- and the other 2 are supposed to be
automatically changed as well. That isn't happening.

Let's say you move the columns back so they correspond with the grid. Now go
to the Design view grid and change the order (or edit the SQL statement) and
run the query again. Your changes aren't there. Datasheet view remains as you
left it after making the last manual change, i.e. what you do in the Datasheet
seems to override any changes in design view or the SQL statement.

I believe this must be a bug, no?

What I want to know is, how do you restore the query so it behaves the way it
did before you made any manual changes in the datasheet. Is the only way to
re-create it? That's the conclusion I have reached...
 
The Column order of the Datasheet View can be *different* from, i.e.
independent from the Design View / SQL View. Design View & SQL View are
always the same. IIRC, this has always been the case with A97, A2000 and
AXP. Hence, I think this is by design and not a bug.

I believe the Column order of the Datasheet View is saved / updated with the
last view but we don't seem to be able to access it.

OTOH, I don't normally change the Column order of the Datasheet View and let
the order (of the Datasheet) to default to the Design View / SQL View. If I
want to show the Columns differently, I change the Design View / SQL String.
 
Back
Top