对每一记录行的多个字段进行比较排名，和条件查询指定字段值的问题

创建一个模块,添加如下自定义函数:

Public Function myOrder(ByVal myid As Long) As Integer
    Dim CW As Integer
    Dim ZS As Double
    Dim i As Integer
    Dim PM As Integer
    Dim rst As Recordset
    Set rst = CurrentDb.OpenRecordset("select * from 生命综合指数记录表 where id=" & myid)
    CW = rst.Fields("首例恶化床位")
    ZS = rst.Fields("床位" & Format(CW, "00"))
    PM = 1
    For i = 1 To 10
        If ZS <= rst.Fields("床位" & Format(i, "00")) And i <> CW Then
            PM = PM + 1
        End If
    Next i
    rst.Close
    myOrder = PM
End Function
Public Function allOrder(ByVal myid As Long, zdcw As Integer) As Integer
    Dim CW As Integer
    Dim ZS As Double
    Dim i As Integer
    Dim PM As Integer
    Dim rst As Recordset
    Set rst = CurrentDb.OpenRecordset("select * from 生命综合指数记录表 where id=" & myid)
    CW = zdcw
    ZS = rst.Fields("床位" & Format(CW, "00"))
    PM = 1
    For i = 1 To 10
        If ZS <= rst.Fields("床位" & Format(i, "00")) And i <> CW Then
            PM = PM + 1
        End If
    Next i
    rst.Close
    allOrder = PM
End Function

SELECT 生命综合指数记录表.ID, 生命综合指数记录表.日期, 生命综合指数记录表.首例恶化床位, MYORDER([ID]) AS 恶化床位的排名, allorder([id],1) AS 床位01, allorder([id],2) AS 床位02, allorder([id],3) AS 床位03, allorder([id],4) AS 床位04, allorder([id],5) AS 床位05, allorder([id],6) AS 床位06, allorder([id],7) AS 床位07, allorder([id],8) AS 床位08, allorder([id],9) AS 床位09, allorder([id],10) AS 床位10
FROM 生命综合指数记录表;