I'm having a brain cramp this afternoon.
I have a treeview on one of my forms which is currently taking forever to load (this may be a server issue but I'm currently unable to verify this). Currently the code develops an initial recordset based on the Act_FY (fiscal year) field in the attached table. As it loops through that recordset, it creates the parent node, then calls creates another recordset which based on the same table but with filters Act_FY = rs!Act_FY and Parent_ID is nULL, sorted by the Act_Start (date) field. As it loops through this recordset, it creates the activity Node, and then checks for sub-activities, Parent_ID = rs2!Parent_ID. This all works, but is slow.
What I would like to do is create a single query, that returns all of these records in a single recordset, sorted by Act_Start, but with the caveat that if an activity has a parent, it should first be sorted by the parent Act_Start.
It is possible that a parent activity could either precede or follow one of it's child activities, although the child activities will generally precede the parent. If there are two activities (which do not have a parent) that start on the same day, there is no specific priority of the order they should appear in.
I've attached a sample database with the table that I'm working with and 10 records. Given these 10 records, I would like the query result to look like:Sort-Activites.mdb