I need an idea of changing my query to accomplish and fix this problem.
Structure of my many-to-many relationship.
Each class can have multiple students and each student can belong to multiple classes.
FYI, The StudentID for the first member of a group 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
On the form you will see a feature to add existing student to current class.
However, if I add the original student to a class, then all students that were referred by that student should also be added. (In other words, all students for that group)
The bigger problem is, that if a student refers another student and I add the new student while working on class 2, that new student will not be added to the group of class 1. (When in fact, the rule of my DB is that every student you refer will be in group for all classes.)
Do I have to re-design the DB? Or can anyone find a good query to fix this?
Please let me know if this can be fixed,
Since I’m using this query for several many-to-many and I can’t move now.
Some additional information
Every student also has a GroupID and I want all groups to be together. (To see the first student and all additional students for that group)
The query looks for the first student on a group and sort it Ascending based on the dates joined.