Sort Main Form based upon subform data

[Mmattson]

I have a form that uses two tables (Pupil ID & Admit Withdraw). The list of pupils attending a specific school (Pupil ID table) is the main form. I have a subform which displays the admit/withdraw history (Admit Withdraw table) for that pupil. Everything works fine except that I would like to sort the main form based upon the number of rows of data for that pupil in the subform. In other words, I want the students who have the most number of admits/withdraws to sort to the top.

 

[llkhoutx]

Your form's recordsource table must be linked to the admit/withdraw table, with the latter determining the row you want.

 

[casey]

adding to llkhoutx's reply...

If you add the admit/withdraw table to the main form's recordsource, adjust the join property to show all pupils, add the admit/withdraw field to the query...change it to the expression:
=Count(admit/withdraw) or =Sum(admit/withdraw)

Sort the field > Descending
and Uncheck > Show on that field if you don't want it displayed

I hope I'm not too far off...

 

[Mmattson]

Thanks,

While waiting for the reply I figured out a solution similar to what Casey was saying. I added a 'count' field to the admit/withdraw query and sorted by that.