All geek questions in one place

MS Access alternative to SQL function ROW_NUMBER ()

I work with MS Access 07 and need a little help.

I have two tables TbProjectTeam and TbProjectList. I need to compare the date the employee was received and the start date of the project. I used the SQL syntax:

 SELECT [TbProjectTeam ].[Surname, name] FROM TbProjectTeam INNER JOIN TbProjectList ON TbProjectTeam .[DateofTurnOut] <= TbProjectList.[DateOfStart] WHERE TbProjectList.[ID] = 1 ORDER BY [Surname, name];

My goal is to replace 1 in the expression TbSeznamUkolu. [ID] = 1 with something like ROW_NUMBER() OVER in SQL . MS Access does not support this function, but, unfortunately, I need to know the row index for all projects. I assume that the corresponding employee will be displayed for each row.

Can anybody help me? Many thanks.

+4
source share
2 answers

MS Access has an AutoNumber data type that sets serial numbers in records. To represent ROW in T-SQL, your dataset must contain an AutoNumber field. Since AutoNumber is numeric, you can perform the functions > and < . The only problem is that the entries will be numbered sequentially as they are added to the table. If you can control the sequence to which they are added, then there is no problem, but if you must add an AutoNumber field to an existing populated table, the sequence may not meet your requirements.

I understand this question is a bit outdated, but I hope this helps.

+1
source

The DCOUNT function, despite the performance issue, has a similar effect:

Just answered a similar answer here: How to show the record number in the MS Access report table? .

+1
source

Source: https://habr.com/ru/post/1502804/

More articles:


All Articles