Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2008
    Location
    All over, Now in Missouri
    Posts
    73

    Answered: Assign student to a group based on total in query

    I have a class of students up to 120 per week.

    I want to assign each person to a group, to keep it simple it will ba A, B, C, and D and then write it to a control (they will permanently Assigned to the group)

    Starting alphabetically, group A will have 30 students, so will B, C, and D

    txtCTot = DCount to get total of class

    Divide txtCTot by 4 to get number in each group

    Should I create a DAO to read each query record and assign A to the first equal count, B to the second, and so on?
    We tend to look at Linear paths which can lead us to a path of resistance!

  2. Best Answer
    Posted by healdem

    "this should be dooable 'just' using queries.
    as a first approach I'd suggest hard coding the queries, but it could be refined at a later date to allocate the group automatically

    the basic components are
    1) a query to find the number of students (and divide that by 4)
    2) a query to find the top 'n' students [use SELECT TOP] (where n is the number found in query 1) who as yet are not allocated
    3) a query to allocate the top n students found in query 2 into the first available group
    ...
    refine so that query 3) can determine which is the next available group to allocate this block of students to.
    ....the followoing should work but it will need fitting to your design. there's no promises as its not been tested

    but on first pass query 3 could be replaced by 4 queries with the group hard coded OR the group could be supplied by a parameter and keep just one query. if you 'know' you only have 120 students then query 1 can be dispensed with

    query 1):-
    Code:
    select int(count(students) \ 4) as group_size from mytable
    ..replace emboldened values with the name of a column (students) in your table and the name of the table (mytable)
    ..save it, as say GroupSize

    query 2):-
    Code:
    select TOP group_size my_primary_key, column, list from mytable, GroupSize
    where isnull(student_group) or (student_group) NOT IN ("A","B","C","D")
    the my_primary_key is the primary key of the student table
    student_group is the name of the column that contains the student's assigned group
    you dont' actually save query 2, we use it ahs the sub query in the next query. however I'd recommend that you do run it as a separate query to prove its right before using it in anger

    use a sub select to identify which rows are to be updated
    query 3)
    Code:
    UPDATE mytable set student_group = [ID for this group] where my_primary_key = (select TOP group_size my_primary_key, column, list from mytable, GroupSize
    where isnull(student_group) or (student_group) NOT IN ("A","B","C","D") )
    when testing / developing the queries you could NOT use the UPODATE bit till you are happy its selecting the right number of students per group
    you can always reset the group suing
    Code:
    UPDATE mytable set student_group =""
    to start all over again

    ...this is well worth a read if you use sub queries

    the above isnt tested, it may well nto work out of the box, even assuming you do translate the column, table and query names exactly but it shoudl get you in the right direction

    the refinement is to replace the request for the new student group [ID for this group] with a sub select that identifies the next group ID

    then if you wan to refine even further work out a way of chopping and changing the group size as required... ferisntance say you want to build in a bit more logic on identifyiung the number of groups (based on minimum or maximum group sizes.

    BUT there is a tradeoff between the amount of 'intelligence'/auotmation of the allocation process and the amount of time available to develop and test the set. if the person doing the allocation is query savvy Id suggest avoid the automation route as its potentially a PITA.

    things to note
    query 1 will retun the nearest integer divisible by the number of groups (in this case 4). that means that you may need to fine tune the group allocatiosn afterwards as they may be out by a little.
    the
    Code:
    where isnull(student_group) or (student_group) NOT IN ("A","B","C","D")
    tells the SQL engine to return rows which are as yet not allocated to a group OR are allocated to something other than A,B,C OR D.
    you may not need the not in clause, but this handles the case where a group has been wrongly allocated OR is empty ONTOP of the group being NULL.

    thsi makes the assumption that mytable will only contain students that are to be allocated. if say your table contains lots of students some of whom are allocated to other academic years then you need to add a where clause or add to an existign where clause to identfy which academice yeear is to be processed
    Code:
    select int(count(students) \ 4) as group_size from mytable
    WHERE academic_period = "Winter 2015"
    "


  3. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    this should be dooable 'just' using queries.
    as a first approach I'd suggest hard coding the queries, but it could be refined at a later date to allocate the group automatically

    the basic components are
    1) a query to find the number of students (and divide that by 4)
    2) a query to find the top 'n' students [use SELECT TOP] (where n is the number found in query 1) who as yet are not allocated
    3) a query to allocate the top n students found in query 2 into the first available group
    ...
    refine so that query 3) can determine which is the next available group to allocate this block of students to.
    ....the followoing should work but it will need fitting to your design. there's no promises as its not been tested

    but on first pass query 3 could be replaced by 4 queries with the group hard coded OR the group could be supplied by a parameter and keep just one query. if you 'know' you only have 120 students then query 1 can be dispensed with

    query 1):-
    Code:
    select int(count(students) \ 4) as group_size from mytable
    ..replace emboldened values with the name of a column (students) in your table and the name of the table (mytable)
    ..save it, as say GroupSize

    query 2):-
    Code:
    select TOP group_size my_primary_key, column, list from mytable, GroupSize
    where isnull(student_group) or (student_group) NOT IN ("A","B","C","D")
    the my_primary_key is the primary key of the student table
    student_group is the name of the column that contains the student's assigned group
    you dont' actually save query 2, we use it ahs the sub query in the next query. however I'd recommend that you do run it as a separate query to prove its right before using it in anger

    use a sub select to identify which rows are to be updated
    query 3)
    Code:
    UPDATE mytable set student_group = [ID for this group] where my_primary_key = (select TOP group_size my_primary_key, column, list from mytable, GroupSize
    where isnull(student_group) or (student_group) NOT IN ("A","B","C","D") )
    when testing / developing the queries you could NOT use the UPODATE bit till you are happy its selecting the right number of students per group
    you can always reset the group suing
    Code:
    UPDATE mytable set student_group =""
    to start all over again

    ...this is well worth a read if you use sub queries

    the above isnt tested, it may well nto work out of the box, even assuming you do translate the column, table and query names exactly but it shoudl get you in the right direction

    the refinement is to replace the request for the new student group [ID for this group] with a sub select that identifies the next group ID

    then if you wan to refine even further work out a way of chopping and changing the group size as required... ferisntance say you want to build in a bit more logic on identifyiung the number of groups (based on minimum or maximum group sizes.

    BUT there is a tradeoff between the amount of 'intelligence'/auotmation of the allocation process and the amount of time available to develop and test the set. if the person doing the allocation is query savvy Id suggest avoid the automation route as its potentially a PITA.

    things to note
    query 1 will retun the nearest integer divisible by the number of groups (in this case 4). that means that you may need to fine tune the group allocatiosn afterwards as they may be out by a little.
    the
    Code:
    where isnull(student_group) or (student_group) NOT IN ("A","B","C","D")
    tells the SQL engine to return rows which are as yet not allocated to a group OR are allocated to something other than A,B,C OR D.
    you may not need the not in clause, but this handles the case where a group has been wrongly allocated OR is empty ONTOP of the group being NULL.

    thsi makes the assumption that mytable will only contain students that are to be allocated. if say your table contains lots of students some of whom are allocated to other academic years then you need to add a where clause or add to an existign where clause to identfy which academice yeear is to be processed
    Code:
    select int(count(students) \ 4) as group_size from mytable
    WHERE academic_period = "Winter 2015"
    I'd rather be riding on the Tiger 800 or the Norton

  4. #3
    Join Date
    Apr 2008
    Location
    All over, Now in Missouri
    Posts
    73
    Once again thanks for the direction. Your solutions to the problem is always outstanding.

    Peter
    We tend to look at Linear paths which can lead us to a path of resistance!

Posting Permissions

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