Edit SQL in Data Connection to Sort Dropdown

[Hilary]

Hi:

I have a number of dropdown controls pulling their lists from a SQL
database. I have created separate form data connections for each dropdown so
that I can add an order by clause and have my list sorted in ascending order.
This works very well for my main table. I am unable to get the order by
clause to work for my child tables, however. Here is an example of the edited
SQL statement for a field from the main table. This successfully orders the
data:
select "Id","SorterFamily","ScanRate" from "dbo"."SystemConfigs" as
"SystemConfigs" order by "ScanRate"

Here is an example of the edited SQL statement for a field from a child
table. This does not sort the data by the order by clause, instead, it sorts
it by "SystemID":
shape
{select "Id", "SorterFamily" from "dbo"."SystemConfigs" as "SystemConfigs"}
as "SystemConfigs"
append
({select "ID","LightingSystems","SystemID" from "dbo"."LightingSystems" as
"LightingSystems" order by "LightingSystems"}
relate "Id" TO "SystemID") as "LightingSystems"

In both cases, the only thing I have added is the order by clause. The rest
is the SQL generated by the data connections wizard. Anyone have any ideas of
how I can sort a data field from a child table?

Thanks so much!