Sort subform by value in related table

P

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?
 
K

KARL DEWEY

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

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]
 
P

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;
 
P

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.
 
J

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]
 
P

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!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top