I never saw the Excel attachment that would show how you expected to see the sort order, but I took your DB and based upon your explanation, I added a query called Group Creation that Groups all of the GroupID fields and selects the Min of the DateJoined field, which I name 'GroupCreated'. I then link this query to your existing query based upon the GroupID and my first sort is on the 'GroupCreated' field. I've put the revised DB in the attached zip file.
I was ready to write back and say yes you will need to do a separate GroupCreation query to link to your StudentsQuery for each set of tables and forms, thus 2 queries for each group, but then I thought there must be some other way. Voila! Check out the attached database which has a single query to accomplish what you wanted by linking a second instance of the Student table to the Student table (don't worry, there is only one Student table).
Sorry I missed your previous post. The reason that you can't edit or delete the records in the query is because they are grouped and although it looks like individual records, there are actually multiple occurences of the record within the query, which are then grouped by the minimum value of the Date of the GroupID. I think I can help you accomplish what you want to do, but I need to know a couple of things. You mentioned that you want to edit or delete records. Would you ever delete the record of the original person to join the group? If you did delete this record, what would you want the Group Creation date for sorting purposes to be based on; the date from the record that was deleted if it was the earliest, or the earliest date of the remaining records for this GroupID?
First, I made one change and Iím not allowing any Edits on this form, only deletions. (This probably doesnít make a difference)
Regarding the deletions, If I ever delete the record of the original person that joined the group, I also would like to delete all members that joined that group after that.
(It looks strange, but thatís what it is)
I just want to mention that Iím also using some conditional formatting on this form.
(Since I donít know yet your solution for the record updating and deleting, I donít know if the conditional formatting will conflict with the solution)
Can you post the solution for the record updating and deleting?
I would like to have something to start with.
(Since most of the time I will not delete the record of the original person to join the group)
Attached please find the solution. I attached the Form to a query that sorts by the Minimum Join Date for the group, but does not include the Minimum Join Date as an output of the query, thereby allowing it to be updated. The Minimum Join Date is derived from a table that is updated as you add groups. Additionally I have added a query that finds Groups that no longer have their original student record and this result is used to delete the other student records associated with this group. I've added buttons to the bottom of your form to accomodate this and I also have the queries run prior to opening the form when you first open the database.
Take a look at all of the underlying queries and macros and come back to me with any questions you may have. In the future, you should consider designing something like this a little differently. You should probably consider having a separate table for the students, the group, and the group members, with the group members table related to the group and student tables. That way you would only enter the student info once even if the student is in multiple groups and if you wanted to delete the group, through referential integrity you could then have all group members records deleted. The group memebers table would only contain the GroupID, which would relate to the Group Table, the StudentID, which would relate to the Student table, and a JoinDate which would be based on when the Student joined the Group. But thats for future development.
Needles to say, you did an expellant job.
Regarding the design of the DB, originally my DB was designed to have the group members in a sub table. (Again, this sample is actually not the same information as my DB)
However, since I have over 20 tables with basically the same type of structure. I start creating the additional 20 sub tables. Plus additional 20 forms, plus additional 20 reports and so on and so on. (When in fact, most of the time the original person that joined the group wont refer a lot of other members)
So I decided to redesign the DB and have all of them in the same table, form, and report.
Now back to your solution.
First I would like to mention something new, when I add the first member to a group, the StudentID will automatically be 0 then for each new member the computer will add the heist StudentID for that group + 1
In other words, Iím identifying the original person by StudentID = 0
And If StudentID <> 0 that means that this Student is not an original
Now, if I have to use your new created tables for each of my tables, it is going to have a big infect on my DB, since I already created most of my forms and reports.
(It happens to be that I didnít worry about the sorting order until now. I never though that Iíll get in to problems)
Regarding the record updating and deleting, Iíll rather leave this on a side for now and just have a query thatís updateable.
Do you think that you can provide 1 or 2 or even 3 queries per table that will allow me to delete a record?
I want to repeat it again, I very appreciate your hard work and Iíll probably use it at another time. But for now it might be a little late.