Query numbering in groups in access database ?

[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: http://www.mediafire.com/file/pajlr1...ering.xls/file

[NoellaG]

Hi, I'm afraid Access does not have any ranking functions. Possible solutions:
- If your database sits on SQL-server, then create the query there in a view and link this view to your access file
- Make a query in access and order by first the field you partition by and then the order by field in your ranking function, add a field fldRank with the constant 1. create a report based on this query and in the report you can use the RunningTotal property on the fldRank field to display the rank in the group
- Use VBA to create a working table with all data in the proper order, add the extra ranking field and loop through the dataset to add the correct ranking
Succes

[CJ_London]

not sure if it will meet your needs but access has a partition function https://support.office.com/en-us/art...7-c94278274dc5

[pbaldy]

Some options:

https://www.experts-exchange.com/art...ft-Access.html