Access で IP アドレスを並べ替える

概要

IP アドレスを使用する場合は、テキストや数字を操作するほど簡単ではないことは既にわかっています。 これは、IP アドレスは、実際にはピリオド (.) で区切られた 4 つの数値のコレクションであり、各値は 0 から 255 までの数値であるためです。 次の表は、並べ替え順序が適用される前のデータを示しています。

Access では IP アドレスを格納するための特別なデータ型が提供されないため、Access での IP アドレスの並べ替えは困難です。 IP アドレスは数値のコレクションにすぎませんが、IP アドレスを数値フィールドに格納することはできません。 これは、数値フィールドが 1 つの小数点 (.) のみをサポートしているのに対し、IP アドレスには 3 つのピリオド (.) が含まれているためです。 つまり、アドレスをテキスト フィールドに格納する必要があります。

IP アドレスはテキスト フィールドに格納されるため、Access の組み込みの並べ替えボタンを使用して、意味のある方法でアドレスを並べ替えることはできません。 並べ替えボタンは、文字が数字であっても、常にアルファベット順にテキスト フィールド内の値を並べ替えます。 つまり、アドレスは、アドレスを構成する数値ではなく、最初の数字、次に 2 番目の数字で並べ替えられます。 次の表は、IPAddress フィールドのアルファベット順に並べ替えられた前のテーブルのアドレスを示しています。

1 で始まるアドレスは、2 で始まるアドレスの前に表示されます。 次の表は、 正しい 昇順のアドレスを示しています。

これらのアドレスの並べ替えに関する手順を理解しやすくするために、IP アドレスを 4 つの数値部分に分割します。 アドレスは最初の部分で並べ替え、次に最初の部分の値ごとに並べ替え、次に 2 番目の部分で並べ替える必要があります。 次の表に示すように、各部分が異なる列に表示され、列には単純な数値が含まれているため、左から右の列を昇順で並べ替えることができます。

4 つの部分を個別に並べ替えるのは、IP アドレスの並べ替えの背後にあるトリックです。 次の手順では、IP アドレス フィールドに加えて、各列がアドレス値の一部を格納する 4 つの計算列を含むクエリを作成します。 最初の計算列はアドレスの最初の数値部分を保持し、2 番目の計算列はアドレスの 2 番目の数値部分を保持します。 IPAddress フィールドでレコードを並べ替える代わりに、クエリは 4 つの計算列でレコードを並べ替えます。

クエリを作成する

レコードを IP アドレスの昇順で表示する Sorted IPAddresses という名前の選択クエリを作成します。 現在のデータベースに、IPAddress という名前のテキスト フィールドを含む MachineDetails という名前のテーブルがあるとします。

1. [クエリ デザイン > 作成] をクリックします。 

2. [ テーブルの追加 ] ([テーブルを Access 2013に表示 ] ) を選択し、[ マシンの詳細 ] をデザイン ビューの上部セクションにドラッグします。

3. MachineID フィールドと IPAddress フィールドをクエリ デザイン グリッドにドラッグします。

4. これで、計算列を追加する準備ができました。 右側の最初の空白列に、式 PartI: Val([IPAddress],(InStr(1,[IPAddress],") を入力します。フィールド行の -1)))。 式は、最初のピリオド (.) の前の文字を返します。 を IPAddress フィールドに入力します。

   次に、式を確認します。 アドレスの最初の部分を構成する桁数がわからないの で、InStr 関数を使用して最初の期間の位置を見つけます。 (ピリオドを除外するために) 1 を減算すると、最初の部分の桁数が返されます。 次に、この数値を Left 関数と共に使用して、左端の文字から始まる多くの文字を IPAddress フィールドから抽出します。 最後に、 Val 関数を呼び出して 、Left 関数によって返される文字を数値に変換します。 基になるデータ型が Text であるため、この最後の手順が必要です。

5. クイック アクセス ツール バーの [保存] をクリックして、クエリを保存します。 各中間ステップの後でこれを行うことをお勧めします。

6. 2 番目のアドレス部分の列を追加します。 PartI の右側の列に、「 PartII: Val(Mid([IPAddress],InStr(1,[IPAddress],")」と入力します。+1,InStr(InStr(1,[IPAddress],".")+1,[IPAddress],".")-InStr(1,[IPAddress],".")-1))をフィールド 行に表示します。 式は、IPAddress フィールドの最初のピリオドと 2 番目のピリオドの間にある文字を返します。

   ここでも、式を確認します。 アドレスの 2 番目の部分を構成する数字の数や、2 番目の部分の開始位置が正確にわからないため (最初の部分の長さがないため)、 InStr 関数を使用して期間の位置を検索します。 次に 、Mid 関数を使用して、最初のピリオドの後の 2 番目のピリオドより前の文字を抽出します。 最後に、 Val 関数を呼び出して、 Mid 関数によって返される文字を数値に変換します。 基になるデータ型が Text であるため、この最後の手順が必要です。

7. 3 番目のアドレス部分の列を追加します。 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 フィールドの 2 番目から 3 番目のピリオドの間にある文字を返します。

   ここでも、式を確認します。 アドレスの 3 番目の部分を構成する数字の数や、3 番目の部分の開始位置が正確にわからないため (1 番目と 2 番目の部分の長さがわからないため)、 InStr 関数を使用して期間の位置を検索します。 次に 、Mid 関数を使用して、2 番目のピリオドに続くが 3 番目のピリオドより前の文字を抽出します。 最後に、 Val 関数を呼び出して、 Mid 関数によって返される文字を数値に変換します。 基になるデータ型が Text であるため、この最後の手順が必要です。

8. 4 番目と最後のアドレス部分の列を追加します。 PartIII の右側の列に、「PartIV: Val(Right([IPAddress],Len([IPAddress])-InStr(InStr(InStr(1,[IPAddress],")」と入力します。+1,[IPAddress],".")+1,[IPAddress],"."))[フィールド] 行に表示されます。 式は、最後のピリオドに続く文字を返します。

   ここでも、式を確認します。 重要なのは、3 番目のピリオドの場所を見つけて、その後に続くすべての文字を抽出することです。 3 番目の期間がどこにあるか正確にはわからないの で、InStr 関数を 3 回呼び出して、3 番目の期間の位置を見つけます。 次に 、Len 関数を使用して、4 番目の部分の桁数を計算します。 返される桁数は Right 関数と共に使用され、IPAddress フィールドの右側の部分からその多くの文字が抽出されます。 最後に、 Val 関数を呼び出して、 Mid 関数によって返される文字を数値に変換します。 基になるデータ型が Text であるため、この最後の手順が必要です。

9. 4 つすべての計算列の [並べ替え] 行を [昇順] に設定します。

   重要    IPAddress 列の [並べ替え] 行は空白のままにする必要があります。

   他のフィールド値で並べ替える場合は、アドレスに加えて、 4 つの計算列の左または右にフィールドを配置します。 計算列の間に他の並べ替えフィールドを配置しないでください。

10. 次の手順では、データシートから 4 つの計算列を非表示にします。 ただし、その前にデータシート ビューに切り替えて、計算列の式の結果を確認します。 次の表は、データシート ビューに表示される列を示しています。

    MachineID	IPAddress	パルティ	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. デザイン ビューに戻り、[4 つすべての計算列の行を表示する] の [チェック] ボックスをオフにします。 これにより、計算列がデータシート ビューに表示されなくなります。

12. 必要に応じて、クエリからレコードを除外するための条件を指定します。

13. データシート ビューに切り替えて、並べ替えられた順序でレコードを表示します。 IP アドレスの昇順で正しく並べ替えられたレコードが表示されます。

並べ替えられた IP アドレスのその他の用途