Advanced Sort

[Angyl]

In the Excel database I have, my employer is able to ADVANCED sort data first
by column, THEN by the data that is in that column in a specific order he
sets up.

Is that possible in Access?

For example, I've imported that same table into Access and one of the
columns we're using in a query has rows with either an E, a V, a P, or a Q in
it. Another column has the same values. So we would want to SORT by column
1 first then by Es by Vs by Ps and by Qs in that column and THEN by column 2
by Es and Vs and Ps and Qs.

Is that kind of advanced sort possible in Access 07?

 

[John W. Vinson]

In the Excel database I have, my employer is able to ADVANCED sort data first
by column, THEN by the data that is in that column in a specific order he
sets up.

Is that possible in Access?

For example, I've imported that same table into Access and one of the
columns we're using in a query has rows with either an E, a V, a P, or a Q in
it. Another column has the same values. So we would want to SORT by column
1 first then by Es by Vs by Ps and by Qs in that column and THEN by column 2
by Es and Vs and Ps and Qs.

Is that kind of advanced sort possible in Access 07?

I'm not sure I understand your sort logic, but yes: you can sort by up to 10
fields and any of those fields can be calculated fields, such as

IIF([fieldname] LIKE "*[EVPQ]*, 1, 2)

to sort records containing E, V, P or Q in [fieldname] before records without
those characters.

 

[KARL DEWEY]

Build a Sort/Translation table with two fields --
Status Sort
E 1
V 2
P 3
Q 4
Join two instances of this table in your query on the fields (columns). Add
the Sort
field from each instance of the Sort table.
Your query would look like this ---
SELECT YourTableName.*, Sort_Table.Sort, Sort_Table_1.Sort
FROM (YourTableName LEFT JOIN Sort_Table ON YourTableName.Column1 =
Sort_Table.Status) LEFT JOIN Sort_Table AS Sort_Table_1 ON
YourTableName.Column2 = Sort_Table_1.Status
ORDER BY Sort_Table.Sort, Sort_Table_1.Sort;