Removing sort from index

[Guest]

I have a table called status in my database. I have two primary fields in
this table called the following

Appointmentid Number
Status Text

This index stops duplication of records when Appointmentid and status are in
combination. This works fine. The problem I have is that the records when
entered on a form always go into alphabetical order. So if I choose

Appoinmentid as 6 status = new
Appoinmentid as 6 status = completed

when the record is saved and displayed as

Appoinmentid as 6 status = completed
Appoinmentid as 6 status = new

This is frustrating as it is not displaying records in the order they are
entered. I have tracked this down to the index fields sorting the records as
ascending in table design view, but there is no way in table design to remove
the sort. Does anyone know of another way to remove this sort?

Thanks

Ronnie

 

[Marshall Barton]

I have a table called status in my database. I have two primary fields in
this table called the following

Appointmentid Number
Status Text

This index stops duplication of records when Appointmentid and status are in
combination. This works fine. The problem I have is that the records when
entered on a form always go into alphabetical order. So if I choose

Appoinmentid as 6 status = new
Appoinmentid as 6 status = completed

when the record is saved and displayed as

Appoinmentid as 6 status = completed
Appoinmentid as 6 status = new

This is frustrating as it is not displaying records in the order they are
entered. I have tracked this down to the index fields sorting the records as
ascending in table design view, but there is no way in table design to remove
the sort.

The order you're seeing is just Access (not the table)
providing a guess as to how it thinks you might want the
data sorted for table sheet view. Since tables in a
relational database are by definition unsorted, you must use
a query to specify how you want the data displayed.

 

[Guest]

Thanks Marshall

The data which is displayed on the forms from the status table, are from a
query called appointmentstatus. There is no sort order in this query so the
sort is not from this query. This has led me to the conclusion that it is
the index which is causing the problem.

 

[Marshall Barton]

The data which is displayed on the forms from the status table, are from a
query called appointmentstatus. There is no sort order in this query so the
sort is not from this query. This has led me to the conclusion that it is
the index which is causing the problem.

A query might be sorted by the PK index, that's a fair thing
for Access to use as a guess. But, I imagine they can be in
any order since Access should not waste time sorting your
query unless you tell it to.

I **think** Access might use the PK index field in the Order
By clause when it generates an internal query to display a
table in sheet view. But who cares? If you cared about the
order, you would use a query to specify it.

Bottom line, for queries and forms, specify the sorting you
want in the query.

Note: Except in trivial situations, report sorting is
specified in the Sorting and Grouping window.

 

[Guest]

Marshall

Thanks for the reply. You state that if I am to define a sort then use a
query. As I indicated I have used a query as the source for my form.

When I define the sort as Ascending in the query the records are displayed
on the form as ascending records. When I define a descending sort, I get
descending records. When I specify no sort I get an ascending sort. So
where is the logic that using a query for a sort will always display your
records as the way you define the sort.

Thanks Ronnie

 

[Marshall Barton]

Thanks for the reply. You state that if I am to define a sort then use a
query. As I indicated I have used a query as the source for my form.

When I define the sort as Ascending in the query the records are displayed
on the form as ascending records. When I define a descending sort, I get
descending records. When I specify no sort I get an ascending sort. So
where is the logic that using a query for a sort will always display your
records as the way you define the sort.

The sorting of a query is specified in the Order By clause.
If you do not specify Asc or Desc, it defaults to Asc.

If you do not use an Order By clause, the records may appear
in any order whatsoever. If they appear to be in some kind
of order, that's just a coincidence, fairly common in small
tables that have not had many changes or deletions. I'm
pretty sure the unsorted records are displayed in the order
the records are stored on disk, which is a space
optimization issue, not a sorting issue).

The bottom line is that relational database theory, not just
Access, does not specify any natural order of the records.
In other words, a table is just a bag of records that might
come out of the bag in any way the db engine decides to
retrieve them.

 

[Guest]

thanks Marshall

Thinking about it your theory makes sense.

Ronnie