Создать поле номера строки в запросе доступа 2010 [дубликат]

Вызовите из вашего запроса следующую функцию.

Public Function GetNextNum(str As String) As Long
    num = num + 1
    GetNextNum = num
End Function

Предостережение заключается в том, что у вас должен быть хотя бы один параметр (даже если он вам не нужен), иначе функция будет только один раз и возвращает 1 для всех строк.

Перед запуском запроса установите глобальную переменную num в 0.

Вам нужна только одна функция для получения очень быстрого и даже «группового» счетчика строк с автоматическим сбросом счетчика или без него.

См. комментарии в строке для типичного использования:

Public Function RowCounter( _
  ByVal strKey As String, _
  ByVal booReset As Boolean, _
  Optional ByVal strGroupKey As String) _
  As Long

' Builds consecutive RowIDs in select, append or create query
' with the possibility of automatic reset.
' Optionally a grouping key can be passed to reset the row count
' for every group key.
'
' Usage (typical select query):
'   SELECT RowCounter(CStr([ID]),False) AS RowID, *
'   FROM tblSomeTable
'   WHERE (RowCounter(CStr([ID]),False) <> RowCounter("",True));
'
' Usage (with group key):
'   SELECT RowCounter(CStr([ID]),False,CStr[GroupID])) AS RowID, *
'   FROM tblSomeTable
'   WHERE (RowCounter(CStr([ID]),False) <> RowCounter("",True));
'
' The Where statement resets the counter when the query is run
' and is needed for browsing a select query.
'
' Usage (typical append query, manual reset):
' 1. Reset counter manually:
'   Call RowCounter(vbNullString, False)
' 2. Run query:
'   INSERT INTO tblTemp ( RowID )
'   SELECT RowCounter(CStr([ID]),False) AS RowID, *
'   FROM tblSomeTable;
'
' Usage (typical append query, automatic reset):
'   INSERT INTO tblTemp ( RowID )
'   SELECT RowCounter(CStr([ID]),False) AS RowID, *
'   FROM tblSomeTable
'   WHERE (RowCounter("",True)=0);
'
' 2002-04-13. Cactus Data ApS. CPH
' 2002-09-09. Str() sometimes fails. Replaced with CStr().
' 2005-10-21. Str(col.Count + 1) reduced to col.Count + 1.
' 2008-02-27. Optional group parameter added.
' 2010-08-04. Corrected that group key missed first row in group.

  Static col      As New Collection
  Static strGroup As String

  On Error GoTo Err_RowCounter

  If booReset = True Then
    Set col = Nothing
  ElseIf strGroup <> strGroupKey Then
    Set col = Nothing
    strGroup = strGroupKey
    col.Add 1, strKey
  Else
    col.Add col.Count + 1, strKey
  End If

  RowCounter = col(strKey)

Exit_RowCounter:
  Exit Function

Err_RowCounter:
  Select Case Err
    Case 457
      ' Key is present.
      Resume Next
    Case Else
      ' Some other error.
      Resume Exit_RowCounter
  End Select

End Function

У вас есть 5 доступных методов.

Только отчеты - Running Sum

Если вы используете эту информацию для отчетов Access, есть простой способ, который не требует VBA или фантазии SQL , Просто добавьте текстовое поле с набором источника управления =1, затем установите Running Sum на Over All, выполнив.

Остальные методы, перечисленные ниже, применяются к формам / таблицам данных / наборам записей

Коррелированный подзапрос

Вы можете выполнить коррелированный подзапрос. Это решение полностью самодостаточно, но не очень общее. Это было бы похоже на это:

SELECT
  (
    SELECT COUNT(*)
    FROM Employees AS x
    WHERE x.EmployeeID <= e.EmployeeID
    ORDER BY x.EmployeeID
  ) AS RowNumber,
  e.EmployeeID
FROM Employees AS e;

Обратите внимание, что из-за коррелированных подзапросов производительность будет быстро уменьшаться по мере увеличения количества записей в таблице. Возможно, вам придется настроить предложение ORDER BY, чтобы получить желаемое присвоение номера, если оно не должно зависеть от EmployeeID, но что-то еще (например, HireDate)

Функция VBA для поддержания счета, forward-only recordset

Этот метод может работать намного быстрее, но может использоваться только один раз; и, конечно, не в формах / таблицах, потому что функции VBA постоянно оцениваются по мере навигации. Таким образом, это необходимо только при чтении набора записей в прямом режиме. Использование стандартного модуля VBA:

Private Counter As Long

Public Function ResetRowNumber() As Boolean
  Counter = 0
  ResetRowNumber = (Counter = 0)
End Function

Public Function GetRowNumber(PrimaryKeyField As Variant) As Long
  Counter = Counter + 1
  GetRowNumber = Counter
End Function

Чтобы затем использовать в запросе:

SELECT 
  GetRowNumber([EmployeeID]) AS RowNumber,
  EmployeeID
FROM Employees
WHERE ResetRowNumber();

Обратите внимание на трюк использования WHERE для неявного вызова функции ResetRowNumber , Обратите внимание, что это будет работать только до тех пор, пока активен только один запрос; имеющие несколько запросов, которые принимают номера строк, приведут к неправильным результатам. Однако реализация очень проста и намного быстрее.

Функция VBA для поддержания счета и сохранения назначения

Это дороже, чем предыдущий метод, но все же может быть дешевле, чем коррелированный подзапрос решение для достаточно большой таблицы. Это имеет то преимущество, что полезно использовать форму / таблицу, поскольку, когда число выдано, оно выдаётся снова. Опять же, в стандартном модуле VBA:

Private NumberCollection As VBA.Collection

Public Function ResetRowNumber() As Boolean
  NumberCollection = New VBA.Collection
  ResetRowNumber = (NumberCollection.Count = 0)
End Function

Public Function GetRowNumber(PrimaryKeyField As Variant) As Variant
  On Error Resume Next

  Dim Result As Long
  Result = NumberCollection(CStr(PrimaryKeyField))
  If Err.Number Then
    Result = 0
    Err.Clear
  End If

  If Result Then
    GetRowNumber = Result
  Else
    NumberCollection.Add NumberCollection.Count + 1, CStr(PrimaryKeyField)
    GetRowNumber = NumberCollection.Count
  End If
  If Err.Number Then
    GetRowNumber = "#Error " & Err.Description
  End If
End Function

Важно, чтобы входной параметр PrimaryKeyValue ссылался на столбец с недопустимыми значениями (который должен быть по определению столбцом первичного ключа). В противном случае у нас не было бы способа узнать, какой номер мы должны выдать, если он уже был выдан записи. SQL похож на предыдущий метод:

SELECT 
  GetRowNumber([EmployeeID]) AS RowNumber,
  EmployeeID
FROM Employees
WHERE ResetRowNumber();

Как и в предыдущем методе, это полезно только для одного запроса за раз. Если вам нужно несколько запросов, вам нужно в два раза больше слоя; сбор для ссылки на сбор запроса, а затем на проверку коллекции этого запроса. Это может стать немного волосатым. Вы также можете получить больше ударов с помощью Scripting.Dictionary, так что это альтернативный взгляд.

Обратите также внимание, что функция теперь возвращает Variant из-за того, что она может столкнуться с непредвиденными ошибками. Поскольку функцию можно вызвать несколько раз, возможно, сотни или даже тысячи раз, мы не можем открывать окно сообщения, поэтому мы можем имитировать встроенные функции и возвращать #Error, что несовместимо с базовым тип Long, который мы действительно используем.

Обновление до SQL Server или других RDBMS

Доступ - это инструмент феноменальный RAD для создания приложения, ориентированного на данные. Однако вы не обязательно привязаны к использованию своего механизма базы данных. Вы могли бы просто перенести свои данные на одну из свободных RDBMS, ссылку с использованием ODBC и продолжать использовать ваше приложение Access, как и прежде, и воспользоваться преимуществами SQL, включая функцию окна ROW_NUMBER(), которая делает это намного легче, чем VBA , Если вы хотите сделать больше, чем просто получить номер строки, вам может потребоваться рассмотреть возможность переноса данных на другой механизм базы данных.

Для дополнительных ссылок это может быть полезно .