Sort two columns of a control table

[Renel]

They say a picture is worth a thousand words. Here is the picture:
.

.
The Actresses column is sorted according to the following query:
.

SELECT “ActressName1”, “ActressID” FROM “TAtresses” ORDER BY “ActressName1” ASC

.
The Role column is unsorted and uses the following query:
.

SELECT “TRoles”…“Role”, “TRoles”…“RoleID”, “JTActricesFS”…“jIDRole” FROM “JTActricesFS”, “TRoles” WHERE “JTActricesFS”…“jIDRole” = “TRoles”…“RoleID”

.
Here is my question: is it possible to sort so that the names are sorted alphabetically and the roles are also sorted in order to achieve this result?

Jin Ji Hee - - > Cameo
Choi Myung Bin - - > Main
Nam Ji Hyun - - > Main
Han Ji Eun - - > Secondary
Han So Hee - - > Secondary
Lee Hye Eun - - > Secondary
.

The control table subform uses this query:
.

SELECT “JTActricesFS”.*, “NameActrice1” FROM “JTActricesFS” LEFT JOIN ( SELECT “NameActrice1”, “ActressID” FROM “TActrices” ) “a” ON “a”…“ActressID” = “JTActricesFS”…“jIDActrice” ORDER BY “NameActor1”

[RobertG]

You will need a query, which content is “Actrices” and “Role”. When reading your code I can’t say which tables are needed for this. A screenshot from the relationships would help.

With this 2 fields in the query it is no problem to set “Role” as first field and “Actrices” as second field for sorting.

[Renel]

@RobertG,

I don’t know if this is the information that can help, but here it is:
.
SQL command of the Actresses column
.

.
Roles Column SQL Command
.

.
Database relationship table
.

[RobertG]

Depending on the relationships this one would work for every role. It won’t show rows of TActrices without Roles. Start a Query → Create Query in SQL View… and copy the code in this query.

SELECT "TActrices".*, "JTActricesFS".*, "TRoles".* 
FROM "TActrices", "JTActricesFS", "TRoles" 
WHERE "TActrices"."ActricesID" = "JTActricesFS"."jIDActrice" 
AND "TRoles"."RoleID" = "JTActricesFS"."jIDRole" 
ORDER BY "TRoles"."Role" ASC, "TActrices"."NomActrice1" ASC

[Villeroy]

It is possible to use a SELECt statement in the ORDER BY clause.
PowerFilter.odb (85.5 KB)
See query qOrder_By_ForeignKey and the corresponding form where you can edit the “Data” table sorted by the person names in the list box. The actual value in the persons column is the ID.

[Renel]

Works perfectly. THANKS @RobertG

[Renel]

Villeroy, thanks for sharing. I will analyze your DB, I’m sure it will be useful to me eventually. For now, I take the @RobertG solution.

[UnklDonald]

A couple approaches

Since the Base Query designer allows you to use other Queries as a data source, simply query your SubForm query using the Name and Role in the first two columns then add the Role followed by the Name again so you have 4 columns. Set the last two to Sort Ascending and un-check the Visible property.

Or nest your SubForm query in parentheses, something like:

SELECT “Actress”, “Role” FROM (Your SubForm Query without the ORDER BY clause) ORDER BY “Role” , “Actress”

[RobertG]

@UnklDonald : Did you recognize the field “Actrices” isn’t field of the form? The first field is a list box, which connects to “jIDActriece” instead. Also the second field would be such a list box. Looking at the relations it will be “jIDRole”, because the source of the form will be “JTActricesFS”.