Multi column sorting in query

[Guest]

I have multi user queries set up on our server that return student
standardized test results to inquiring teachers. Each query contains
information for all grade levels. I'm accustomed, when using Excel, to be
able to sort by multiple columns but I'm not seeing how to do this in Access.
For display purposes, I already have 2 sorts built into the Design View of
each query. But I need to be able to sort the results of a filter by 2 or
more columns. How to?

 

[BruceM]

If you mean that, for instance, you want to sort by LastName, then by
FirstName, just put LastName to the left of FirstName in the query design
grid.

 

[Guest]

Yes that's the idea but I'd hoped to do it without moving columns around in
design view.

 

[Guest]

You can select Ascending [Asc] or Descending against multiple fields in the
query design view. Thus you may have
FirstName LastName Subject1 Subject2
Asc Asc Asc
This will sort first by FirstName, then by LastName and finally by Subject1.

To change to sorting first by LastName, then by FirstName and finally by
Subject1 use
LastName FirstName Subject1 Subject2
Asc Asc Asc
Note that this changes the display order.

If you want to retain the original display order use
FirstName LastName FirstName Subject1 Subject2
Asc Asc Asc
and against Show select
Yes Yes No Yes

SQL is easier:
SELECT Student.FirstName, Student.LastName, Student.Subject1
FROM Student
ORDER BY Student.LastName, Student.FirstName
WITH OWNERACCESS OPTION;

 

[BruceM]

Why are you reluctant to move columns? If you wish to view the query fields
in some particular order, make a form based on the query, either in
Continuous view or in Datasheet view.

 

[Guest]

I think I can use the design view method well enough. Thanks

 

[Vincent Johns]

Note that, even though this looks a bit clumsy in Query Design View, as
you have the same field listed twice (once to display, once to sort),
the corresponding SQL is pretty concise. And after you've closed the
Query and re-opened it in Query Design View, the invisible (Show =
false) fields all scamper down to the end of the list, where they're out
of the way.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

You can select Ascending [Asc] or Descending against multiple fields in the
query design view. Thus you may have
FirstName LastName Subject1 Subject2
Asc Asc Asc
This will sort first by FirstName, then by LastName and finally by Subject1.

To change to sorting first by LastName, then by FirstName and finally by
Subject1 use
LastName FirstName Subject1 Subject2
Asc Asc Asc
Note that this changes the display order.

If you want to retain the original display order use
FirstName LastName FirstName Subject1 Subject2
Asc Asc Asc
and against Show select
Yes Yes No Yes

SQL is easier:
SELECT Student.FirstName, Student.LastName, Student.Subject1
FROM Student
ORDER BY Student.LastName, Student.FirstName
WITH OWNERACCESS OPTION;

:

I have multi user queries set up on our server that return student
standardized test results to inquiring teachers. Each query contains
information for all grade levels. I'm accustomed, when using Excel, to be
able to sort by multiple columns but I'm not seeing how to do this in Access.
For display purposes, I already have 2 sorts built into the Design View of
each query. But I need to be able to sort the results of a filter by 2 or
more columns. How to?