Сортировка IP-адресов в Access

Введение

Если вы работаете с IP-адресами, вы, вероятно, уже знаете, что это не так просто, как работа с текстом или цифрами. Это связано с тем, что IP-адрес на самом деле представляет собой коллекцию из четырех числовых значений, разделенных точкой (.), где каждое значение является числом от 0 до 255. В следующей таблице показаны данные перед применением любого порядка сортировки.

Сортировка IP-адресов в Access является сложной задачей, так как Access не предоставляет специальный тип данных для хранения IP-адресов. Хотя IP-адрес — это просто коллекция чисел, ip-адреса нельзя хранить в числовом поле. Это связано с тем, что числовое поле поддерживает только одну десятичную точку (.), а IP-адрес содержит три точки (.). Это означает, что адреса должны храниться в текстовом поле.

Так как IP-адреса хранятся в текстовом поле, нельзя использовать встроенные кнопки сортировки в Access для осмысленной сортировки адресов. Кнопки сортировки всегда сортируют значения в текстовом поле в алфавитном порядке, даже если символы являются цифрами. Другими словами, адреса сортируются по первой цифре, затем по второй цифре и т. д., а не по числовым значениям, составляющим адрес. В следующей таблице показаны адреса из предыдущей таблицы, отсортированные в алфавитном порядке в поле IPAddress.

Адреса, начинающиеся с 1, отображаются перед адресами, начинающимися с 2, и т. д. В следующей таблице показаны адреса в правильном порядке по возрастанию.

Чтобы упростить понимание действий, связанных с сортировкой этих адресов, разбиение IP-адресов на четыре числовые части. Адреса должны быть отсортированы по первой части, затем по каждому значению в первой части, затем по второй части и т. д. В таблице показана каждая часть в разных столбцах. Так как столбцы содержат простые числовые значения, становится возможным сортировать столбцы слева направо в порядке возрастания, как показано в следующей таблице.

Сортировка четырех частей по отдельности — это хитрость, стояющая за сортировкой IP-адресов. В следующей процедуре создается запрос, который помимо поля IP-адреса содержит четыре вычисляемых столбца, где каждый столбец хранит часть значений адресов. Первый вычисляемый столбец будет содержать первую числовую часть адреса, второй вычисляемый столбец будет содержать вторую числовую часть адреса и т. д. Вместо сортировки записей по полю IPAddress запрос сортирует записи по четырем вычисляемым столбцам.

Создание запроса

Вы создадите запрос на выборку с именем Sorted IPAddresses, который отображает записи в порядке возрастания IP-адресов. Предположим, что текущая база данных содержит таблицу MachineDetails, которая содержит текстовое поле IPAddress.

1. Откройте вкладку Создание и выберите пункт Конструктор запросов. 

2. Выберите Добавить таблицы (показать таблицу в Access 2013 ) и перетащите сведения о компьютере в верхнюю часть конструктора.

3. Перетащите поля MachineID и IPAddress в сетку конструктора запроса.

4. Теперь можно добавить вычисляемые столбцы. В первом пустом столбце справа введите выражение PartI: Val(Left([IPAddress],(InStr(1;[IPAddress],".") -1))) в строке Поле . Выражение возвращает символы, предшествующие первой точке (.) в поле IPAddress.

   Теперь просмотрите выражение. Так как вы не знаете, сколько цифр составляют первую часть адреса, вы используете функцию InStr для поиска позиции первой точки. Вычитание 1 (для исключения периода) возвращает количество цифр в первой части. Затем вы используете это число с функцией Left для извлечения этого количества символов, начиная с крайнего левого символа, из поля IPAddress. Наконец, вызывается функция Val для преобразования символов, возвращаемых функцией Left , в число. Этот последний шаг необходим, так как базовый тип данных — Text.

5. Сохраните запрос, нажав кнопку Сохранить на панели быстрого доступа. Рекомендуется делать это после каждого промежуточного шага.

6. Добавьте столбец для второй части адреса. В столбце справа от PartI введите PartII: Val(Mid([IPAddress],InStr(1;[IPAddress],".") +1,InStr(InStr(1;[IPAddress],".") +1,[IPAddress],".") -InStr(1;[IPAddress],".") -1)) в строке Поле . Выражение возвращает символы, расположенные между первой и второй точками в поле IPAddress.

   Опять же, просмотрите выражение. Так как вы не знаете, сколько цифр составляют вторую часть адреса или где именно начинается вторая часть (так как вы не знаете, сколько времени занимает первая часть), вы используете функцию InStr для поиска позиций периодов. Затем с помощью функции Mid извлекаются символы, которые следуют за первой точкой, но предшествуют второй. Наконец, вызывается функция Val для преобразования символов, возвращаемых функцией Mid , в число. Этот последний шаг необходим, так как базовый тип данных — Text.

7. Добавьте столбец для третьей части адреса. В столбце справа от PartII введите PartIII: Val(Mid([IPAddress],InStr(InStr(1;[IPAddress],».") +1,[IPAddress],".") +1,InStr(InStr(InStr(1;[IPAddress],".") +1,[IPAddress],".") +1,[IPAddress],".") -InStr(InStr(1;[IPAddress],".") +1,[IPAddress],".") -1)) в строке Поле . Выражение возвращает символы, расположенные между второй и третьей точками в поле IPAddress.

   Опять же, просмотрите выражение. Поскольку вы не знаете, сколько цифр составляют третью часть адреса или где именно начинается третья часть (поскольку вы не знаете, сколько времени находятся первая и вторая части), вы используете функцию InStr для поиска позиций периодов. Затем с помощью функции Mid извлекаются символы, которые следуют за второй точкой, но предшествуют третьей. Наконец, вызывается функция Val для преобразования символов, возвращаемых функцией Mid , в число. Этот последний шаг необходим, так как базовый тип данных — Text.

8. Добавьте столбец для четвертой и последней части адреса. В столбце справа от PartIII введите PartIV: Val(Right([IPAddress],Len([IPAddress])-InStr(InStr(InStr(1;[IPAddress],".") +1,[IPAddress],".") +1,[IPAddress],"."))) в строке Поле . Выражение возвращает символы, следующие за последней точкой.

   Опять же, просмотрите выражение. Ключ заключается в том, чтобы найти расположение третьей точки, а затем извлечь все символы, которые следуют за ней. Так как вы не знаете точно, где находится третья точка, вы вызываете функцию InStr три раза, чтобы найти позицию третьей точки. Затем с помощью функции Len вычисляется количество цифр в четвертой части. Количество возвращаемых цифр затем используется с функцией Right для извлечения этого количества символов из правой части поля IPAddress. Наконец, вызывается функция Val для преобразования символов, возвращаемых функцией Mid , в число. Этот последний шаг необходим, так как базовый тип данных — Text.

9. Задайте для строки Сортировка всех четырех вычисляемых столбцов значение По возрастанию.

   Важно    Строка Сортировка столбца IPAddress должна быть пустой.

   Если вы хотите отсортировать поля по другим значениям полей, в дополнение к адресам, поместите поля слева или справа от всех четырех вычисляемых столбцов. Не размещайте другие поля сортировки между вычисляемыми столбцами.

10. Следующим шагом является скрытие четырех вычисляемых столбцов из таблицы. Но перед этим переключитесь в режим таблицы, чтобы увидеть результат выражений в вычисляемых столбцах. В следующей таблице показаны столбцы, которые вы увидите в режиме таблицы.

    MachineID	Ipaddress	PartI	PartII	PartIII	PartIV
    3	1.198.3.93	1	198	3	93
    4	32.183.93.40	32	183	93	40
    5	104.30.244.2	104	30	244	2
    6	104.244.4.1	104	244	4	1
    2	104.244.253.29	104	244	253	29
    1	123.4.245.23	123	4	245	23

11. Вернитесь в режим конструктора и очистите поле проверка в строке Показать все четыре вычисляемых столбца. Это предотвращает отображение вычисляемых столбцов в режиме таблицы.

12. При необходимости укажите условия для исключения записей из запроса.

13. Переключитесь в режим таблицы, чтобы просмотреть записи в отсортированном порядке. Вы увидите записи, правильно отсортированные по возрастанию IP-адресов.

Дополнительные возможности для отсортированных IP-адресов