Сортування IP-адрес в Access

Вступ

Якщо ви працюєте з IP-адресами, ви, напевно, вже знаєте, що це не так просто, як робота з текстом або числами. Це відбувається тому, що IP-адреса – це дійсно набір із чотирьох числових значень, розділених крапкою (.), де кожне значення – це число від 0 до 255. У таблиці нижче наведено дані перед застосуванням будь-якого порядку сортування.

Сортувати IP-адреси в Access дуже складно, оскільки в Access не передбачено спеціальний тип даних для зберігання IP-адрес. Хоча IP-адреса – це лише колекція номерів, IP-адреси не можна зберігати в числовому полі. Це відбувається тому, що числове поле підтримує лише одну десяткову кому (.), тоді як IP-адреса містить три крапки (.). Це означає, що адреси потрібно зберігати в текстовому полі.

Оскільки IP-адреси зберігаються в текстовому полі, у програмі Access не можна використовувати вбудовані кнопки сортування для змістовного сортування адрес. Кнопки сортування завжди сортують значення в текстовому полі в алфавітному порядку, навіть якщо символи є числами. Іншими словами, адреси сортуються за першою цифрою, потім за другою цифрою тощо, а не за числовими значеннями, які складають адресу. У таблиці нижче наведено адреси з попередньої таблиці, відсортовані в алфавітному порядку в полі IPAddress.

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

Щоб спростити розуміння кроків сортування цих адрес, IP-адреси буде розбито на чотири числові частини. Адреси потрібно сортувати за першою частиною, потім за кожним значенням у першій частині, потім за другою частиною тощо. У таблиці кожна частина відображається в іншому стовпці, тому що стовпці містять прості числові значення, за зростанням можна сортувати стовпці зліва направо, як показано в таблиці нижче.

Сортування чотирьох частин окремо – це хитрість сортування IP-адрес. У наступній процедурі створюється запит, який, окрім поля IP-адреси, містить чотири обчислювані стовпці, у яких кожен стовпець зберігає частину значень адреси. Перший обчислюваний стовпець міститиме першу числову частину адреси, другий обчислюваний стовпець міститиме другу числову частину адреси тощо. Замість сортування записів за полем IPAddress запит сортуватиме записи за чотирма обчислюваними стовпцями.

Створення запиту

Буде створено вибірковий запит з іменем Відсортовані 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 , на число. Цей останній крок необхідний, оскільки базовий тип даних – "Текст".

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 , на число. Цей останній крок необхідний, оскільки базовий тип даних – "Текст".

7. Додайте стовпець для третьої частини адреси. У стовпці праворуч від partII введіть PartIII: Val(Mid([IPAddress],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 , на число. Цей останній крок необхідний, оскільки базовий тип даних – "Текст".

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

   Знову перегляньте вираз. Клавіша полягає в тому, щоб знайти розташування третього періоду, а потім видобути всі символи, що йдуть за ним. Оскільки ви не знаєте точно, де розташовано третій період, ви тричі викликаєте функцію InStr , щоб знайти положення третього періоду. Потім за допомогою функції Len можна обчислити кількість цифр у четвертій частині. Кількість повернутих цифр використовується з функцією Right , щоб видобути таку кількість символів із правої частини поля IPAddress. Нарешті, ви викликаєте функцію Val , щоб перетворити символи, повернуті функцією Mid , на число. Цей останній крок необхідний, оскільки базовий тип даних – "Текст".

9. Установіть для рядка Сортування всі чотири обчислювані стовпці значення За зростанням.

   Увага!    Рядок Сортування стовпця IPAddress має бути пустим.

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

10. Далі потрібно приховати чотири обчислювані стовпці з таблиці даних. Але перш ніж це зробити, перейдіть у вікно табличного подання даних, щоб побачити результат виразів в обчислюваних стовпцях. У таблиці нижче показано стовпці, які відображатимуться у вікні табличного подання даних.

    MachineID (Ідентифікатор комп'ютера)	IPAddress (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-адрес