Page 1 of 5 12345 LastLast
Results 1 to 15 of 64
  1. #1
    erickemboi is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Sep 2020
    Posts
    73

    Access query to promote students to next class


    Hi everyone,
    I am new to access. I am making a student database. I have a combo box for:
    Grade one
    Grade two
    Grade three
    Grade four
    I want to make a query or code to promote students to next grade every year let's say January 1 every year.
    Students in grade 1 to go to grade 2 and those in grade 2 to go to grade 3 and those in grade 3 to go to grade 4.
    Those in grade 4, hide their records or delete them.
    Assist me with query or code.
    The table is named Students and combo box field GRADE
    THANKS.

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Those are the actual combo values and the combo only has one column? If yes I'd say somewhat difficult and not worth the effort when it would be far simpler to fix the data.
    You should have at least a corresponding numeric field for those values. Could simply be an incremental auto number id field but a number field would be much better (where 1 relates to one, 2 to two and so on. If using that, you should have the autonumber id as a primary key field also, but definitely have the 1/one, 2/two field.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    No please?
    Use a query to increment the grade by one, after running a query to mark/delete grade 4 students. I would mark them and just retrieve non marked records in your dB.í
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  4. #4
    Gicu's Avatar
    Gicu is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    With a properly constructed table you wouldn't even need that as you can calculate the grade based on the EnrollmentDate (first date the student started with the school).

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  5. #5
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Quote Originally Posted by Gicu View Post
    With a properly constructed table you wouldn't even need that as you can calculate the grade based on the EnrollmentDate (first date the student started with the school).

    Cheers,
    Can you explain how that would work for a student in grade 3 who came to the school from another school, say in the middle of a year, or even at the start of a school year? Wouldn't that mean that if they started December 31 in grade 3 and the calculation gets run Jan 01 they automatically get bumped up?

    The whole question seems a bit odd, which makes me think this is really about something else. Are we at the point where advancement is a done deal regardless of anything else?
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  6. #6
    erickemboi is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Sep 2020
    Posts
    73
    Thanks. But it is not working. I will appreciate if you assist me

  7. #7
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Post some sample data from the table that this query would affect. Make sure you include field names from the query in that post. So far, you've given very little to go on.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  8. #8
    Gicu's Avatar
    Gicu is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    I was thinking mostly like treating grade as a "pseudo" age. In a school db I used to work with we even had a function to get the grade from the DOB of the student - the grades or text to accommodate for request for having a K(indergarten):
    Code:
    Function ConvertBirthdayToGrade(Birthday As Date, SchoolYear As Variant) As String
    Dim intGrade As Integer
    On Error Resume Next
    
    If IsNull(SchoolYear) Then SchoolYear = Format(Date, "yyyy")
    
    intGrade = CInt(Left(SchoolYear, 4)) - Year(Birthday) - 5
    ConvertBirthdayToGrade = IIf(intGrade >= 12, 12, intGrade)
    
    If intGrade <= 0 Or intGrade > 12 Then
        'ConvertBirthdayToGrade = Null
        ConvertBirthdayToGrade = ""
    ElseIf intGrade = 0 Then
        ConvertBirthdayToGrade = "K"
    End If
    End Function
    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  9. #9
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I guess nobody fails a grade anymore. Add that to no longer teaching cursive writing and let's see what we get in 20 years.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  10. #10
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    My old dive buddy from La, used to tell me that if the kids played up in class their parents would get 'Crazy Money' to try and get them back in line.
    So guess what some of the kids would do?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  11. #11
    erickemboi is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Sep 2020
    Posts
    73
    This is nice. Unfortunately, my database is almost complete and I wouldn't want to start over because well, it is hectic based on vba codes and macros on forms and reports. In our country, students are not allowed to repeat a grade. That is why I needed a query whereby a school principal can just click a button to promote all students concurrently. Any help will be highly appreciated.
    Thanks

  12. #12
    erickemboi is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Sep 2020
    Posts
    73
    Correct. In our country, all students are promoted to the next grade

  13. #13
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    You have several suggestions and requests for more info. I think the ball is in your court?
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  14. #14
    erickemboi is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Sep 2020
    Posts
    73
    @Gicu how do you call this function. Kindly assist me with sample database

  15. #15
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Can you upload your database?
    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

Page 1 of 5 12345 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 02-06-2017, 03:29 AM
  2. Replies: 1
    Last Post: 11-23-2016, 07:42 PM
  3. Replies: 2
    Last Post: 07-25-2016, 08:12 AM
  4. Replies: 1
    Last Post: 10-29-2015, 08:36 AM
  5. Is there a way to promote HOTKEYS
    By Z1nkstar in forum Access
    Replies: 3
    Last Post: 07-10-2014, 08:50 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums