pointing to the last column that's not null 2

[suna]

i have a table i.e salary
the column names are GRADE, level (numeric), T1 (money), T2 money), T3 (money)...until T38 (money).

my problem is, how to retrieve only the value of column T1 and the value of the last column T which doesn't have a null value. (the T column sometimes will only be filled until T7 or T20 etc)

Thanx!

 

[MatsHulten]

Sounds to me that you should redesign your table, and break it up to two different tables, where you store grade and level in the first, and all the T columns you need in another, with the primary key in the first table as the foreign key in the second.

Otherwise you can use the IsNull() function. It returns true if the value passed to it is NULL.

Good Luck!
-Mats Hulten

 

[MichaelRed]

Matt is correct re the design. On the other hand, if this is beyond your control or ability, it will take some effort.

If I had this to do, I would make a new table, with the grade, T1, and TMax.

Then I would make a relatively simple prcedure to loop through the current table to populate the new table.

The following is oth untested and WRITTEN for Ms. Access. Your use of the Term "MOney" indicates that your db is NOT an MS Access db, so other issues may also be involved in the instantation of the tables/records. The approach should work in any db.

Public Function basMaxT()

    Dim dbs As CurrentDb
    Dim rstAll As Recordset
    Dim rstMinMax As Recordset

    Dim Idx As Integer
    Dim Max As Currency

    Set dbs = CurrentDb
    Set rstAll = dbs.OpenRecordset("tblSalary", dbOpenDynaset)
    Set rstMinMax = dbs.OpenRecordset("tblSalaryMinMax", dbOpenDynaset)

    While Not rstAll.EOF

        For Idx = 0 To rstAll.Fields.Count - 1

            If (left(rstAll.Fields(Idx).Name) = "T") Then
                If (IsNumeric(rstAll.Fields(Idx))) Then
                    Max = rstAll.Fileds(Idx)
                 Else
                    Exit For
                End If
            End If

        Next Idx

        'Here, we APPARENTLY have the last one which has a VALUE.
        With rstMinMax
            .AddNew
                !Grade = rstAll!Grade
                !Level = r4stAll!Level
                !TMax = Max
            .Update
        End With

    Wend

End Function

MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over