Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Jan 2004
    Posts
    115

    Unanswered: Complicated sorting query

    If this possible without using code, I will be very lucky

    I would like to have the table students sorted as follows

    Look for the first student on a group and sort it Ascending based on the dates
    List all additional students ascending based on the dates

    However, I want all groups to be together. (To see the first student and all additional students for that group)

    I have attached a sample DB
    In addition I have attached an Excel sheet with an example of how this DB should look after the sort.

    Thanks
    Tom

    Sum additional information
    A group starting date is when the first student joins it.
    All students that join a group will have a latter DateJoiend
    Attached Files Attached Files
    Last edited by Tommiller; 11-04-04 at 12:30.

  2. #2
    Join Date
    Sep 2004
    Location
    Charlotte, NC
    Posts
    164
    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.

    TD
    Attached Files Attached Files

  3. #3
    Join Date
    Jan 2004
    Posts
    115
    Hi,
    I very appreciate your help.

    Your sample is doing the professional job.

    However, I will have to configure this type of sort order for about 15 forms/tables

    Does this mean that I will have to create 2 queries for each form?

    Thanks

    Tom

  4. #4
    Join Date
    Sep 2004
    Location
    Charlotte, NC
    Posts
    164
    Hi Tom,

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

    I hope this gets you what you want.

    TD
    Attached Files Attached Files

  5. #5
    Join Date
    Jan 2004
    Posts
    115
    Hi TD,

    I just start testing your sample.

    You did a great job.
    However, the first thing I realized is that you canít edit any field?

    Do you have a solution?
    Thanks

  6. #6
    Join Date
    Jan 2004
    Posts
    115
    Hi everyone,

    I was able to live with edit problem.

    However, I just realized a bigger problem; Iím unable to delete any record using the form.

    Can anyone please advise me?

    Thanks
    Tom

  7. #7
    Join Date
    Sep 2004
    Location
    Charlotte, NC
    Posts
    164
    Hi Tom,

    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?

    TD

  8. #8
    Join Date
    Jan 2004
    Posts
    115
    This is a good question, never thought about it.

    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)

    Please let me know if thatís possible.

    Thanks again
    Tom

  9. #9
    Join Date
    Jan 2004
    Posts
    115
    I assume that you will be able to find a solution by your self. However, I just want to share with you the information from Microsoftís site.

    You probably know all of the information thatís written there, but there might be changes.

    Please see bellow
    http://support.microsoft.com/default...b;en-us;328828

    Thanks again
    Tom

  10. #10
    Join Date
    Sep 2004
    Location
    Charlotte, NC
    Posts
    164
    Hi Tom,

    I've got a solution for the record updating and deleting, but I'm working on a solution to delete all of the other group records if you delete the original group member's record.

    TD
    Last edited by buckeye_td; 11-17-04 at 17:11. Reason: typo

  11. #11
    Join Date
    Jan 2004
    Posts
    115
    Thank you

    Looking forward

    Tom

  12. #12
    Join Date
    Jan 2004
    Posts
    115
    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)

    BTW
    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)

    Your help is very appreciated

    Thanks
    Tom

  13. #13
    Join Date
    Sep 2004
    Location
    Charlotte, NC
    Posts
    164
    Hi Tom,

    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.

    Hope this helps.

    TD
    Attached Files Attached Files

  14. #14
    Join Date
    Jan 2004
    Posts
    115
    Needles to say, you did an expellant job.
    Thank you

    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.

    THANKS
    THANKS
    THANKS

    Tom

  15. #15
    Join Date
    Sep 2004
    Location
    Charlotte, NC
    Posts
    164
    This will give you an updateable query. You'll need 2 queries.

    First, create a query called qryGroupCreation. Here is the code for it:

    SELECT Min(Students.DateJoiend) AS GroupCreated, Students.GroupID
    FROM Students
    GROUP BY Students.GroupID
    ORDER BY Min(Students.DateJoiend), Students.GroupID;


    Next create a Student Query. Here is your code for it:

    SELECT DISTINCTROW Students.StudentRecordID, Students.GroupID, Students.StudentID, Students.DateJoiend, Students.FirstName, Students.LastName, Students.ParentsNames, Students.Address, Students.City, Students.StateOrProvince, Students.PostalCode, Students.PhoneNumber, Students.EmailName, Students.Major, Students.StudentNumber, Students.Notes
    FROM Students INNER JOIN qryGroupCreation ON Students.GroupID = qryGroupCreation.GroupID
    ORDER BY qryGroupCreation.GroupCreated, Students.GroupID, Students.StudentID;


    Then change the record source for your form to this Student Query.

    The records are updateable.

    By the way, now that you have put in the logic of the original student in a group having a StudentID value of 0, this thing could be written different to ignore the minimum date joined for grouping.

    TD
    Last edited by buckeye_td; 11-18-04 at 16:36.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •