Query numbering in groups in access database ? (1 Viewer)

[dongtrien]

in sql server there is a group problem statement: DENSE_RANK() OVER (ORDER BY TABLUONGNV3.HOVATEN) AS STT
How to write in sql access issues in groups? I'm not writing well: (Select Count (*) FROM [TABLUONGNV3] as Temp WHERE [Temp].[MANHANVIEN] < [TABLUONGNV3].[MANHANVIEN]) + 1 AS STT

SELECT (Select Count (*) FROM [TABLUONGNV3] as Temp WHERE [Temp].[MANHANVIEN] < [TABLUONGNV3].[MANHANVIEN]) + 1 AS STT, TABLUONGNV3.HOVATEN, TABNHANVIEN.IDNV, TABLUONGNV3.MACANV, TABNHANVIENCA.CANV, TABLUONGNV3.N01, TABLUONGNV3.N02, TABLUONGNV3.N03, TABLUONGNV3.N04, TABLUONGNV3.N05, TABLUONGNV3.N06, TABLUONGNV3.N07, TABLUONGNV3.N08, TABLUONGNV3.N09, TABLUONGNV3.N10, TABLUONGNV3.N11, TABLUONGNV3.N12, TABLUONGNV3.N13, TABLUONGNV3.N14, TABLUONGNV3.N15, TABLUONGNV3.N16, TABLUONGNV3.N17, TABLUONGNV3.N18, TABLUONGNV3.N19, TABLUONGNV3.N20, TABLUONGNV3.N21, TABLUONGNV3.N22, TABLUONGNV3.N23, TABLUONGNV3.N24, TABLUONGNV3.N25, TABLUONGNV3.N26, TABLUONGNV3.N27, TABLUONGNV3.N28, TABLUONGNV3.N29, TABLUONGNV3.N30, TABLUONGNV3.N31, TABNHANVIENCA.TIENMOTCA, TABLUONGNV3.TONGLUONG, TABLUONGNV3.PHAT, TABLUONGNV3.TAMUNG, TABLUONGNV3.THUCNHAN, TABLUONGNV3.GHICHU 
FROM TABNHANVIENCA INNER JOIN (TABNHANVIEN INNER JOIN TABLUONGNV3 ON TABNHANVIEN.IDNV = TABLUONGNV3.MANHANVIEN) ON TABNHANVIENCA.IDCANV = TABLUONGNV3.MACANV 
WHERE TABLUONGNV3.LUONGTHANG = #06/01/2019#
ORDER BY TABLUONGNV3.HOVATEN;

You see the attached file: View attachment Query numbering.xls

 

[arnelgp]

you will not be able to do that without AutoNumber field.

 

[dongtrien]

AutoNumber field is not available for this case

 

[arnelgp]

create this function in a Module:

Public Function fnRank(sField As String, vFieldValue As Variant, sTable As String) As Integer
    Dim iCount As Integer
    Dim sWhere As String
    Dim sSQL As String
    Dim sHolder As String
    
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    
    sField = "[" & sField & "]"
    sField = Replace(sField, "[[", "[")
    sField = Replace(sField, "]]", "]")
    
    sTable = "[" & sTable & "]"
    sTable = Replace(sTable, "[[", "[")
    sTable = Replace(sTable, "]]", "]")
    
    If IsNull(vFieldValue) Then
        Exit Function
    End If
    
    If VarType(vFieldValue) = vbString Then
        sWhere = sField & "=" & Chr(34) & vFieldValue & Chr(34)
    End If
    If VarType(vFieldValue) = vbInteger Or _
        VarType(vFieldValue) = vbSingle Or _
        VarType(vFieldValue) = vbDouble Or _
        VarType(vFieldValue) = vbLong Or _
        VarType(vFieldValue) = vbByte Then
        sWhere = sField & "=" & vFieldValue
    End If
    
    sSQL = "select " & sField & " from " & sTable & " order by " & sField & " asc;"
    
    Set db = CurrentDb
    Set rs = db.OpenRecordset(sSQL, dbOpenSnapshot)
    With rs
        .FindLast sWhere
        iCount = 1
        sHolder = .Fields(sField).value
        While Not .BOF
            If sHolder <> .Fields(sField).value Then
                sHolder = .Fields(sField).value
                iCount = iCount + 1
            End If
            .MovePrevious
        Wend
        .Close
    End With
    Set rs = Nothing
    Set db = Nothing
    fnRank = iCount
End Function

your sql:

SELECT fnRank("HOVATEN",[HOVATEN],"TABLUONGNV3") AS STT,
    TABLUONGNV3.HOVATEN, TABNHANVIEN.IDNV, 
TABLUONGNV3.MACANV, TABNHANVIENCA.CANV, 
TABLUONGNV3.N01, TABLUONGNV3.N02, TABLUONGNV3.N03,
TABLUONGNV3.N04, TABLUONGNV3.N05, TABLUONGNV3.N06, 
TABLUONGNV3.N07, TABLUONGNV3.N08, TABLUONGNV3.N09, 
TABLUONGNV3.N10, TABLUONGNV3.N11, TABLUONGNV3.N12, 
TABLUONGNV3.N13, TABLUONGNV3.N14, TABLUONGNV3.N15, 
TABLUONGNV3.N16, TABLUONGNV3.N17, TABLUONGNV3.N18, 
TABLUONGNV3.N19, TABLUONGNV3.N20, TABLUONGNV3.N21, 
TABLUONGNV3.N22, TABLUONGNV3.N23, TABLUONGNV3.N24, 
TABLUONGNV3.N25, TABLUONGNV3.N26, TABLUONGNV3.N27, 
TABLUONGNV3.N28, TABLUONGNV3.N29, TABLUONGNV3.N30, 
TABLUONGNV3.N31, TABNHANVIENCA.TIENMOTCA, 
TABLUONGNV3.TONGLUONG, TABLUONGNV3.PHAT, 
TABLUONGNV3.TAMUNG, TABLUONGNV3.THUCNHAN, 
TABLUONGNV3.GHICHU 
FROM TABNHANVIENCA INNER JOIN (TABNHANVIEN INNER JOIN TABLUONGNV3 ON TABNHANVIEN.IDNV = TABLUONGNV3.MANHANVIEN) ON TABNHANVIENCA.IDCANV = TABLUONGNV3.MACANV 
WHERE TABLUONGNV3.LUONGTHANG = #06/01/2019#
ORDER BY TABLUONGNV3.HOVATEN;

 

[dongtrien]

I write in the C# language your case guide can I apply to the Visual Studio C# language ? I think using SQL to group and number numbers is more convenient

 

[Pat Hartman]

The problem with the way you are doing the query is that you have included detail data which you don't want to "count". A query can solve this but you will need to summarize the data so that the query returns only a single record for each value of IDNV (which I am assuming is unique - that is what arnel was referring to when he said you needed an autonumber. You need a UNIQUE identifier but it doesn't have to be an autonumber.) Once you assign the rank, you can join the rank query back to the details query if you need the details.

 

[dongtrien]

Do you have any solution to solve this problem ?

 

[Pat Hartman]

I told you how to fix the issue. you do it in two steps. Rank the grouped data in query 1 and then join that query back to the detail data to pick up the details.