VB.NET/Access - check if the first value of each row is less than 13 and then add text to each corresponding row in a datatable

[Chet.Horton]

I am trying to determine which students are eligible for promotion by checking how many classes they have attended against a known number of class and if they are add Eligible for promotion in the last value of each of the corresponding rows.

My end goal is to compare each persons total classes and check them against the requirements for each promotion. Each number below is the amount of time in grade at that level before they are eligible for promotion. I haven't tried getting the other belt levels yet. I have just been trying to get White Belt to Yellow Belt working.

Yellow Belt 12 Classes
Orange Belt 48 Classes
Green Belt 64 Classes
Purple Belt 144 Classes
Ikkyu 96 Classes
Nikyu 96 Classes
Sankyu 96 Classes
Shodan 192 Classes


I want this....


TotalClasses LastName FirstName Rank PromotionStatus

5 Smith John White Belt
10 Johnson Sam White Belt
15 Jackson George White Belt Eligible for Yellow Belt
5 Adkins Jim White Belt
50 Harris Max Yellow Belt Eligible for Orange Belt



but I get this


TotalClasses LastName FirstName Rank PromotionStatus

5 Smith John White Belt Eligible for Yellow Belt
10 Johnson Sam White Belt Eligible for Yellow Belt
15 Jackson George White Belt Eligible for Yellow Belt
5 Adkins Jim White Belt Eligible for Yellow Belt


Here is the code....

' RUN QUERY
Access.ExecQuery("SELECT COUNT(StudentID) as TotalClasses, FirstName, LastName, Rank FROM Attend GROUP BY FirstName, LastName, Rank")

' REPORT & ABORT ON ERRORS
If NoErrors(True) = False Then End




Access.DBDT.Columns.Add("PromotionStatus", GetType(System.String))

For Each row As DataRow In Access.DBDT.Rows
For i = 0 To Access.DBDT.Rows.Count - 1
If (row.Field(Of Integer)(0)) > 12 Then

Access.DBDT.Rows(i)("PromotionStatus") = "Eligible for Yellow Belt"

End If
Next



Next


' FILL DATAGRID
dgvTestDue.DataSource = Access.DBDT

Continue reading...