physically sort records in a table

[Guest]

I want to permanently (physically) sort the records in a table. All forms of
sort/filter just seem to change the view and leave the records in the order
they were entered. When I restructure the table, the records revert to the
order they were entered.

I can use a query to create a new table in a different physical order, but
would rather sort the original table. Am I missing something obvious?

Keith

 

[Rick B]

Yes. You don't work in a table and the order does not matter.

Once created, you should rarely, if ever, open a table again. All your work
should be done in the forms, reports, and queries. You can then set the
sorting to any order you desire.

 

[Rick B]

Yes. You don't work in a table and the order does not matter.

Once created, you should rarely, if ever, open a table again. All your work
should be done in the forms, reports, and queries. You can then set the
sorting to any order you desire.

 

[John Vinson]

Am I missing something obvious?

Yes: the fact that a table HAS NO ORDER.

It should be viewed as an unordered "bucket" of data. You can create a
Query with a sort, and use this Query as the basis of Forms, Reports,
other Queries, exports, etc.

I believe that if you Compact the database, the records in the table
will be sorted into Primary Key order - but the moment you add a new
record, the new record will be placed wherever Access finds
convenient. You should NOT rely on tables being in *any* particular
order.

John W. Vinson[MVP]

 

[John Vinson]

Am I missing something obvious?

Yes: the fact that a table HAS NO ORDER.

It should be viewed as an unordered "bucket" of data. You can create a
Query with a sort, and use this Query as the basis of Forms, Reports,
other Queries, exports, etc.

I believe that if you Compact the database, the records in the table
will be sorted into Primary Key order - but the moment you add a new
record, the new record will be placed wherever Access finds
convenient. You should NOT rely on tables being in *any* particular
order.

John W. Vinson[MVP]

 

[Guest]

If I can do everything in a logical view, your response would be correct.
However, for example, if I sort a view into the order I want and then
restructure the table to add an autoincrement column, the records are
numbered in the original physical order rather than the order I want. So, you
see, physical order does make a difference for this and other functions.

I can get the result I need by exporting to Excel, sorting there and
reimporting into Access. I was hoping someone who does more Access than I
would be able to tell me how to do the same thing in Access alone. Instead,
you tell me that I shouldn't want to do what I want to do. Not helpful.

 

[Guest]

If I can do everything in a logical view, your response would be correct.
However, for example, if I sort a view into the order I want and then
restructure the table to add an autoincrement column, the records are
numbered in the original physical order rather than the order I want. So, you
see, physical order does make a difference for this and other functions.

I can get the result I need by exporting to Excel, sorting there and
reimporting into Access. I was hoping someone who does more Access than I
would be able to tell me how to do the same thing in Access alone. Instead,
you tell me that I shouldn't want to do what I want to do. Not helpful.

 

[Guest]

I want to permanently (physically) sort the records in a table.
Build a query with your sort order and save - there is your permanent record.

If ascending or descinding will not do it for you add a field that you
insert a number to sort on.

 

[Guest]

I want to permanently (physically) sort the records in a table.
Build a query with your sort order and save - there is your permanent record.

If ascending or descinding will not do it for you add a field that you
insert a number to sort on.

 

[Guest]

How can you say a "table has no order" when an autoincrement column added to
the table always numbers the rows in the same sequence - that is, the
sequence the rows were entered in. Obviously, there is a "natural" order to
the table. If I export the table to Excel, sort it an reimport it, the
autoincrement now works the way I want it to.

What you seem to be saying is that Access does not give me any direct way to
control the "natural" sequence that it uses for some operations. If true,
that is disappointing.

Imagine you had a reference table that was going to be accessed tens of
thousands of times a day and you wanted to do a binary search on it in order
to speed response. If I can't control the physical sequence of rows in the
table and had to use a logical view to get the records into sequence, the
system could be forced to do literally ten to one hundred times the disk
accesses to find my data compared to accessing a well-ordered physical set.
So, there are definately times when you need to be able to "rely" on a table
to be in the order you set it in - or pay a huge price in other areas.
"Unordered buckets of data" are fine for simple, low-usage applications, but
it is a pity if that is the only option Access provides. I'm still hoping
someone else will tell me how to control physical order in the cases where it
is important.

Keith

Keith

 

[Guest]

How can you say a "table has no order" when an autoincrement column added to
the table always numbers the rows in the same sequence - that is, the
sequence the rows were entered in. Obviously, there is a "natural" order to
the table. If I export the table to Excel, sort it an reimport it, the
autoincrement now works the way I want it to.

What you seem to be saying is that Access does not give me any direct way to
control the "natural" sequence that it uses for some operations. If true,
that is disappointing.

Imagine you had a reference table that was going to be accessed tens of
thousands of times a day and you wanted to do a binary search on it in order
to speed response. If I can't control the physical sequence of rows in the
table and had to use a logical view to get the records into sequence, the
system could be forced to do literally ten to one hundred times the disk
accesses to find my data compared to accessing a well-ordered physical set.
So, there are definately times when you need to be able to "rely" on a table
to be in the order you set it in - or pay a huge price in other areas.
"Unordered buckets of data" are fine for simple, low-usage applications, but
it is a pity if that is the only option Access provides. I'm still hoping
someone else will tell me how to control physical order in the cases where it
is important.

Keith

Keith

 

[Arno R]

I can get the result I need by exporting to Excel, sorting there and
reimporting into Access. I was hoping someone who does more Access than I
would be able to tell me how to do the same thing in Access alone. Instead,
you tell me that I shouldn't want to do what I want to do. Not helpful.

Not helpful at first sight maybe, but both Rick and John are right on this issue.
Experienced developers are not going to teach you 'the wrong way'.
Maybe someone like PCDatashit can pop in?

Arno R

 

[Arno R]

I can get the result I need by exporting to Excel, sorting there and
reimporting into Access. I was hoping someone who does more Access than I
would be able to tell me how to do the same thing in Access alone. Instead,
you tell me that I shouldn't want to do what I want to do. Not helpful.

Not helpful at first sight maybe, but both Rick and John are right on this issue.
Experienced developers are not going to teach you 'the wrong way'.
Maybe someone like PCDatashit can pop in?

Arno R

 

[Rick B]

If you want to sort the records in a particular order, then you would have
to add a field to do so. You are free to add a new field to your table and
include a number for each record.

Not really sure why you'd do this in Access. Access is NOT a spreadsheet.

What happens as you add new records? Will you always want them at the end,
or will you forever be manipulating your new "sort" field?

Just FYI, there are hundreds of previous posts on this very topic. Go take
a look at them and maybe you'll find some responses that better suit your
needs.

 

[Rick B]

If you want to sort the records in a particular order, then you would have
to add a field to do so. You are free to add a new field to your table and
include a number for each record.

Not really sure why you'd do this in Access. Access is NOT a spreadsheet.

What happens as you add new records? Will you always want them at the end,
or will you forever be manipulating your new "sort" field?

Just FYI, there are hundreds of previous posts on this very topic. Go take
a look at them and maybe you'll find some responses that better suit your
needs.

 

[Guest]

Imagine you had a reference table that was going to be accessed tens of
thousands of times a day and you wanted to do a binary search on it in order
to speed response.
Create an index.

 

[Guest]

Imagine you had a reference table that was going to be accessed tens of
thousands of times a day and you wanted to do a binary search on it in order
to speed response.
Create an index.

 

[Guest]

Hi Keith -- A couple of things to note as what Rick and John have stated is
correct. I don't know your experience with relational databases but my guess
is you don't have that much based on your arguments. With that said, Access
is used to build relational databases which act completely differently then a
spreadsheet such as Excel -- Don't expect the same functionality!

The next thing is, the table should only be used to store data, the order it
is stored is irrelevant to relational databases. For items that will be
searched on repeatedly, these should be indexed to speed up retrieval...but
you cannot index everything.

Also, the autonumber's only function is to create a unique identifier for a
record, not a sequential number.

Last, it would be helpful if you would provide more detail on exactly it is
you want to accomplish. Based on your original post, your descripton seems
to be heading down the wrong path; hence the reason for the original
responses as we do not want to give you bad advice...

 

[Guest]

Hi Keith -- A couple of things to note as what Rick and John have stated is
correct. I don't know your experience with relational databases but my guess
is you don't have that much based on your arguments. With that said, Access
is used to build relational databases which act completely differently then a
spreadsheet such as Excel -- Don't expect the same functionality!

The next thing is, the table should only be used to store data, the order it
is stored is irrelevant to relational databases. For items that will be
searched on repeatedly, these should be indexed to speed up retrieval...but
you cannot index everything.

Also, the autonumber's only function is to create a unique identifier for a
record, not a sequential number.

Last, it would be helpful if you would provide more detail on exactly it is
you want to accomplish. Based on your original post, your descripton seems
to be heading down the wrong path; hence the reason for the original
responses as we do not want to give you bad advice...

 

[John Vinson]

Imagine you had a reference table that was going to be accessed tens of
thousands of times a day and you wanted to do a binary search on it in order
to speed response.

I do so, very frequently.

I use Access JET B-Tree indexes - which implement a VERY efficient
binary search - on any fields which are to be used for searching or
sorting.

Doing so is MUCH more efficient than sorting the table and writing
your own VBA code to try to beat Jet's search engine.

John W. Vinson[MVP]