Sort subform by value in related table

[Peter Scott]

Hello. I have a table Schedule, with columns RoomID, TimeSlotID, and
SessionID. I made a form with a list box for selecting RoomID, and a
subform showing TimeSlotID and SessionID. So far so good. But the
ordering of the records in the subform is not what I want.

I found out how to enable OrderBy for the subform, but I do not want to
sort by TimeSlotID. I have another table, TimeSlots, with columns ID and
SortingOrder. TimeSlots.ID and Schedule.TimeSlotID are related. I want
to sort by TimeSlots.SortingOrder. But I can't figure out how. The
OrderBy property seems to require an expression and not a query. I was
able to replace the record source of the subform with a query that joined
Schedule and TimeSlots, and then I got the records sorted, only problem
was, I couldn't change any of them because my source was a query and not
a table.

I want my subform to sort on (SELECT TimeSlots.SortingOrder FROM TimeSlots
WHERE TimeSlots.ID = <subform>.TimeSlotID), but I can't figure out how.
Any thoughts?

 

[KARL DEWEY]

Have you thought about adding a sort-order field to the table used in the
subform?

 

[John W. Vinson]

I want my subform to sort on (SELECT TimeSlots.SortingOrder FROM TimeSlots
WHERE TimeSlots.ID = <subform>.TimeSlotID), but I can't figure out how.
Any thoughts?

Base the Subform on a query joining the subform's recordsource table to
TimeSlots, and sorting by SortingOrder.

John W. Vinson [MVP]

 

[Peter Scott]

Base the Subform on a query joining the subform's recordsource table to
TimeSlots, and sorting by SortingOrder.

I tried that, but it would no longer let me edit the fields in the
subform. I used a query of

SELECT Schedule.*, TimeSlots.SortingOrder
FROM Schedule,TimeSlots WHERE Schedule.TimeSlotID = TimeSlots.ID;

 

[Peter Scott]

Have you thought about adding a sort-order field to the table used in the
subform?

I don't want to do that because I would then have to duplicate the
information from the TimeSlots.SortingOrder column there and keep it in
sync.

 

[John W. Vinson]

I tried that, but it would no longer let me edit the fields in the
subform. I used a query of

Use a JOIN rather than a WHERE clause:

SELECT Schedule.*, TimeSlots.SortingOrder
FROM Schedule INNER JOIN TimeSlots ON Schedule.TimeSlotID = TimeSlots.ID
ORDER BY SortingOrder;

If (as there should be!) there is a unique Index on Timeslots.ID - it *is* the
primary key of Timeslots, is it not? - the query should be updateable.

John W. Vinson [MVP]

 

[Peter Scott]

Use a JOIN rather than a WHERE clause:

SELECT Schedule.*, TimeSlots.SortingOrder
FROM Schedule INNER JOIN TimeSlots ON Schedule.TimeSlotID = TimeSlots.ID
ORDER BY SortingOrder;

If (as there should be!) there is a unique Index on Timeslots.ID - it *is* the
primary key of Timeslots, is it not? - the query should be updateable.

Bingo. I come from the Oracle world and haven't had to use <THING> JOIN
before. Thanks!