Sorting a table?

[John O'Boyle]

I'm fairly newe to Access, and am not trying to do anything that I think
is difficult. I need to be able to "permanently" sort a table on two or
three fields. I've no idea how to do that. In "data view" it seems to
only let me sort on one column at a time.

Can someone point me in the right direction?

Thanks.

JLOB

 

[John Vinson]

I'm fairly newe to Access, and am not trying to do anything that I think
is difficult. I need to be able to "permanently" sort a table on two or
three fields. I've no idea how to do that. In "data view" it seems to
only let me sort on one column at a time.

Can someone point me in the right direction?

Away from the idea of sorting a table.

A Table HAS NO ORDER. It's just a "heap" of records.

If you want to see records in a particular order create a Query based
on the table, sorting the records using the Sort line on the query
grid, or (equivalently) using an Order BY clause in the query's SQL.

This Query can be used anywhere that you would use a table - as the
recordsource for a form, for reports, whatever.

If you are using table datasheets for data entry and display... don't.
That's not what they're for. Table datasheets are excellent for design
and debugging, and that's about it! If you want users to view and edit
data use a Form; if you want to print data use a Report; but don't use
a table.

John W. Vinson[MVP]

 

[John O'Boyle]

I'm fairly newe to Access, and am not trying to do anything that I think
is difficult. I need to be able to "permanently" sort a table on two or
three fields. I've no idea how to do that. In "data view" it seems to
only let me sort on one column at a time.

Can someone point me in the right direction?

Thanks.

JLOB

Thanks for the clarifications. But I'm still having difficulty
understanding how to designate primary and secondary etc. fields within
the query for sorting. I'd like to order by DATE within ACCOUNT (for
example) but not at all certain how to go about doing that.

Thanks.
JLOB

 

[Larry Daugherty]

Hi John,

I only see your part of the thread so don't know what other info you've
gleaned.

You can sort by as many fields as you have in the query. If you view your
query in the QBE grid, the field furthest left with a sort other than
"unsorted" selected will be the last to order the returned data. The sort
"priority" is from left to right. You can play with it in the QBE grid by
changing the sort setting of the various fields and then click the
bang/exclamation mark to run the query.

HTH

 

[doyapore]

JLOB,
What you want may be possible by the steps enumerated below:
1. Open the table containing your records in Design View.
2. Now on the menu bar which you see on top of your screen, select
View - > Indexes. [I am not sure as to the proper plural term for Index, but
in High School we were taught that the word was, or rather is, "Indices"
;-) ] A separate small form window will open for you.
3. You mentioned that you would like to order by ACCOUNT followed by
DATE. [Please correct me if I am wrong.]
4. Now under View -> Indexes, fill in the name for the Index, e.g., you
could supply the Index name as "AccountIdx".
5. In the next column, type the field name, in this instant case,
"ACCOUNT" (without the quotes of course).
6. In the column after the field name, select ASCENDING as your sort
order.
7. Skip to the next row. But instead of filling in the Index name, just
leave it as it is, i.e., blank.
8. Repeat step #5, but type in DATE.
9. Repeat step #6.
10. Close the Index form window.
11. Save the table.
12. View the table, and you would find the table sorted in the manner
which you had wished to view.

The index which you have saved thus, (i.e., AccountIdx) can also be invoked
using Access' VBA programming powers when you build an application of your
own.

For viewing the sorted records using your choice in a query can be achieved
in the following manner.
1. Open a new Query using Design View.
2. Add the table which you wish to view in the sorted order.
3. In the columns below, choose the fields which would be placed side by
side from the table. In your case, please select ACCOUNT in the first column
and then select DATE in the next column.
4. If you will notice, under each column there is a row which says
"Sort".
5. So, assuming that you have selected and the ACCOUNT column on the left
of the DATE column, on the "Sort" row select "Ascending" for both the
columns.
6. Save and name the Query.
7. You are now ready to view the query.

 

[John O'Boyle]

Thanks. I tried that. I can see the Index relationship is set to what
it should be. When I display the table however, the sequence hasn't
changed! I guess I don't know how to associate the new index with the
table display. Any thoughts?

Thanks.
JLOB

 

[Brendan Reynolds]

You can't depend on a table being in any particular order. Access will sort
tables by their primary key, but as you add new records the table is not
automatically resorted. (Precisely when and under what conditions it is
resorted I can't say). The addition of another index will not change the way
the table is displayed at all, only the primary key index is significant
here. If you need a specific order, you need to use a query, form or report
rather than looking directly at the table. The first solution, involving a
new index, that was suggested to you in the earlier response is not going to
work, but the second solution, describing how to specify the order you need
in a query, should work.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

Thanks. I tried that. I can see the Index relationship is set to what it
should be. When I display the table however, the sequence hasn't changed!
I guess I don't know how to associate the new index with the table
display. Any thoughts?

Thanks.
JLOB

JLOB,
What you want may be possible by the steps enumerated below:
1. Open the table containing your records in Design View.
2. Now on the menu bar which you see on top of your screen, select
View - > Indexes. [I am not sure as to the proper plural term for Index,
but in High School we were taught that the word was, or rather is,
"Indices" ;-) ] A separate small form window will open for you.
3. You mentioned that you would like to order by ACCOUNT followed by
DATE. [Please correct me if I am wrong.]
4. Now under View -> Indexes, fill in the name for the Index, e.g.,
you could supply the Index name as "AccountIdx".
5. In the next column, type the field name, in this instant case,
"ACCOUNT" (without the quotes of course).
6. In the column after the field name, select ASCENDING as your sort
order.
7. Skip to the next row. But instead of filling in the Index name,
just leave it as it is, i.e., blank.
8. Repeat step #5, but type in DATE.
9. Repeat step #6.
10. Close the Index form window.
11. Save the table.
12. View the table, and you would find the table sorted in the manner
which you had wished to view.

The index which you have saved thus, (i.e., AccountIdx) can also be
invoked using Access' VBA programming powers when you build an
application of your own.

For viewing the sorted records using your choice in a query can be
achieved in the following manner.
1. Open a new Query using Design View.
2. Add the table which you wish to view in the sorted order.
3. In the columns below, choose the fields which would be placed side
by side from the table. In your case, please select ACCOUNT in the first
column and then select DATE in the next column.
4. If you will notice, under each column there is a row which says
"Sort".
5. So, assuming that you have selected and the ACCOUNT column on the
left of the DATE column, on the "Sort" row select "Ascending" for both
the columns.
6. Save and name the Query.
7. You are now ready to view the query.

 

[John O'Boyle]

You can't depend on a table being in any particular order. Access will sort
tables by their primary key, but as you add new records the table is not
automatically resorted. (Precisely when and under what conditions it is
resorted I can't say). The addition of another index will not change the way
the table is displayed at all, only the primary key index is significant
here. If you need a specific order, you need to use a query, form or report
rather than looking directly at the table. The first solution, involving a
new index, that was suggested to you in the earlier response is not going to
work, but the second solution, describing how to specify the order you need
in a query, should work.

Thanks Brendan. I guess I was expecting to be able to view the table in
its original form, but ordered differently. Guess that can't be done.
Bummer.

JLOB

 

[Dirk Goldgar]

Thanks Brendan. I guess I was expecting to be able to view the table
in
its original form, but ordered differently. Guess that can't be
done. Bummer.

While I agree completely with Brendan and the others who have told you
that queries are the way to enforce order in records, you may find that
you can get what you want, for the time being, by opening the table in
datasheet view, clicking menu items Records -> Filter -> Advanced
Filter/Sort..., dragging fields to the grid of the filter design and
settig the sort order you want, then clicking the Apply Filter button on
the toolbar. When you close the table, it will ask you if you want to
save your design changes. If you click Yes, the sort order will be
saved with the table, and records will be displayed in that order when
you open the table as a datasheet.

 

[doyapore]

Yes thats another way to do it.

 

[John O'Boyle]

While I agree completely with Brendan and the others who have told you
that queries are the way to enforce order in records, you may find that
you can get what you want, for the time being, by opening the table in
datasheet view, clicking menu items Records -> Filter -> Advanced
Filter/Sort..., dragging fields to the grid of the filter design and
settig the sort order you want, then clicking the Apply Filter button on
the toolbar. When you close the table, it will ask you if you want to
save your design changes. If you click Yes, the sort order will be
saved with the table, and records will be displayed in that order when
you open the table as a datasheet.

Thanks. That was EXACTLY what I needed.

JLOB