У вас есть 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 , Если вы хотите сделать больше, чем просто получить номер строки, вам может потребоваться рассмотреть возможность переноса данных на другой механизм базы данных.
Для дополнительных ссылок это может быть полезно .