この章ではクエリーを作成する手法を学びます。
クエリーは下記のデータベースファイル上に実装します。
Base練習_TransactionForm.odb
なお、クエリー作成後のデータベースファイルは下記のファイルとなります。実装時間を省きたい方は下記ファイルを開いて第9章に進んで頂いても結構です。
Base練習_Query.odb
品目マスター用クエリー作成
最初に品目マスターテーブル「Item」用クエリーを作成してみましょう。
①Base編集画面左ペインの「データベース」欄より「クエリー」を選択し、右ペイン上部の「タスク」欄で[デザイン表示でクエリーを作成]をクリックします。
②すると下図のように「クエリーデザイン」画面が表示され、さらにダイアログボックス「テーブルまたはクエリーの追加」が表示されます。このダイアログボックスでは検索対象となるテーブルやクエリーを選択します。ここでは「テーブル」を選択し、テーブルリストから「Item」を選択し、[追加]をクリックした後、[閉じる]をクリックします。
③すると下図のように「クエリーデザイン」画面上部にテーブル「Item」の構造を表す図が表示されますので、各列の「フィールド」行にテーブル「Item」の全てのフィールドを指定します。
④ここでアイコン「クエリーの実行」（下図参照）をクリックしてみましょう。
⑤すると下図のように画面最上部に実行結果が表示されます。このように、まだ保存前のクエリーでもアイコン「クエリーの実行」をクリックすることによって、その実行結果を確認することができます。
⑥つぎに、この時点でアイコン「デザインビューのオン／オフ切り替え」（下図参照）をクリックしてみましょう。
⑦すると下図のようにデザインビューが消え、SQL文（赤い破線で囲った部分）が表示されます。アイコン「デザインビューのオン／オフ切り替え」はトグルスイッチになっていますので、どちらのモードも随時切り替えることが可能です。
デザインビューで設定した内容により、BaseのシステムがSQLを自動生成します。SQL(Structured Query Language)とはデータベースの操作や管理を行う言語のことです。このコースでは詳しく解説しませんが、後ほど少しだけSQLの機能を活用します。ここではSQLの基本文法を簡単に説明しておきましょう。
今回自動生成されたSQL文は下記のとおりです。
SELECT “ID”, “Name”, “Unit_ID”, “UnitPrice”, “TaxRate” FROM “Item”
青字で書かれた部分をコマンド、「,」（カンマ）で区切られ、「”」（ダブルクォーテーション）で囲まれた部分をフィールドといいます。上記例は指定したテーブル名がひとつなので省略されていますが、”ID”は”Item”.”ID”のようにテーブル名とフィールドを「.」（ピリオド）で繋いで記述するのが文法上正式な表記法です。
したがって、上記SQL文の意味は『テーブルItemを検索し、フィールドID、Name、Unit_ID、UnitPrice、TaxRateの値を取得せよ』となります。
⑧まずは最もシンプルなクエリーが出来ましたので、ここで「クエリーデザイン」画面を閉じて下さい。
⑨すると下図のようなダイアログボックス「確認」が表示されますので、[はい]をクリックします。
⑩すると下図のようなダイアログボックス「名前を付けて保存」が表示されます。クエリーには既にあるテーブル名と同じ名前は付けられませんので「Item」とは付けられません。そこでクエリー名欄に「クエリー:Item」と入力し、[OK]をクリックします。
品目マスター用クエリーの実行と編集可能モードについて
それでは作成したクエリー「クエリー:Item」を実行してみましょう。
①Base編集画面左ペインの「データベース」欄より「クエリー」を選択し、下図のように右ペイン下部の「クエリー」欄にリストされた「クエリー:Item」をダブルクリックします。
②すると、下図のように「テーブルデータビュー」画面が表示されます。この画面では各レコードの値を変更することができますし、「＋」アイコンが表示された最下行にカーソルを置いて新規データを追加することもできます。ただし、クエリーを使用してデータの更新や新規登録を行うことはあまりお勧めできません。アイコン「データの編集」（下図参照）をクリックして編集不可モードにして使用して下さい。
なお、テーブルのプライマリーキー（この例では「ID」）を取得フィールドに指定してクエリーを作成した場合に編集可能モードとなり、指定しない場合には編集不可モードになります。
③編集不可モードになった事を確認したらテーブルデータビュー」画面を閉じて下さい。
単位名付き品目マスター用クエリーの作成
①クエリー「クエリー:Item」を編集モードで開きます。
②すると下図のように「クエリーデザイン」画面が表示されますので、アイコン「テーブルまたはクエリーの追加」をクリックします。
③するとダイアログボックス「テーブルまたはクエリーの追加」が表示されますので、「テーブル」を選択し、テーブルリストから「Unit」を選択し、[追加]をクリックした後、[閉じる]をクリックします。
④下図のように「TaxRate」列の右隣のフィールド行に「Unit.Name」を指定して、アイコン「クエリーの実行」をクリックします。
⑤すると下図のように画面最上部に実行結果が表示され、Unit_IDに対応した単位名が右端の列に表示されているのが確認されます。
⑥通常、コードに対応した名称は並んでいるのが見やすい表ですので、列の変更を行います。右端の列「Name」をドラッグし、列「Unit_ID」と列「UnitPrice」の境目にドロップして下さい。
（注）行のドラッグ＆ドロップがうまくできない場合もありますが、何度かトライしてコツを掴んで下さい。
⑦「Name」列を「Unit_ID」列の右にドラッグ＆ドロップして再度アイコン「クエリーの実行」をクリックした結果が下図です。
⑧つぎに列名を整理しましょう。「Name」列が２個所あったらこれを使用する人が何の名称か解りませんね。ID以外の各列のエイリアス行に適切な見出しを設定します。
⑨エイリアス行に適切な見出しを設定し、もう一度アイコン「クエリーの実行」をクリックした結果が下図です。
⑩下図は自動生成されたSQL文です。これで取得する各フィールドとエイリアスとの関係がAS句でよく分ると思います。また、WHERE句でテーブル「Item」と「Unit」との間でリレーションシップを図っていることも理解して頂けると思います。
（注）下図は読みやすいように適宜改行処理したSQL文ですが、クエリー上で編集し、保存した後は残念なことに改行情報が失われてしまいます。今後さらに複雑なSQL文を構成し、しかも読みやすい形のまま記録したい方にはWriterや他のテキストエディター上で読みやすい形で原文を記録し、それをクエリーの編集画面上にコピペする方法をお勧めします。
⑪ここでクエリー「クエリー:Item」を保存し、終了して下さい。これで見やすいクエリーが完成しました。
部門名付き担当者マスター用クエリーの作成
①クエリー「クエリー:Item」の作成手順を参考にしてクエリー「クエリー:Staff」を作成して下さい。
検索対象となるのはテーブル「Staff」とテーブル「Department」です。
②下図が完成した「クエリー:Staff」を編集モードで開いてアイコン「クエリーの実行」をクリックした状態です。
③下図は自動生成されたSQL文です。これでクエリーは完成しましたが、今回はもうひとつ改善を試みてみましょう。上図の「性別」列の値が「m」か「f」で表示されていますが、これを「男性」と「女性」のように表示するようにします。
④「Gender」列の右の列のフィールド行に下記のコードを入力します。
CASE “Gender” WHEN ‘m’ THEN ‘男性‘ ELSE ‘女性‘ END
これはSQLに組み込み可能な式のひとつで「CASE式」といいます。「CASE」と「END」で囲まれた中に記述する構文ですが、「CASE」句には条件となるフィールド、「WHEN」句には条件となる値、「THEN」句には条件となる値が真の場合のフィールドの値、「ELSE」句には条件となる値が偽の場合のフィールドの値を記述します。
つまり、式全体の意味は『フィールド「Gender」の値が「m」の場合は「男性」、そうでない場合は「女性」』となります。
（注１）コード入力時、単語間に半角スペースを挿入する場合は[Sift]+[Space]を使用して下さい。
（注２）フィールド行に入力した式のフィールド名を「”Gender”」のようにダブルクォーテーションで囲って入力したはずなのに、保存後に「[Gender]」のように勝手に変更され、ブラケットで囲われている事がありますが、そのままで問題ありません。この場合もSQL上はダブルクォーテーションで囲われたまま保存されています。
⑤下図が式を設定し、アイコン「クエリーの実行」を再度クリックした状態です。なお、変更前にフィールド「Gender」の値を表示していた列は「表示」行のチェックを外して非表示にしてありますが、不要な列なので保存後に自動的に削除されます。
⑥下図は自動生成されたSQL文です。CASE式は緑色で表示されています。
⑦ここでクエリー「クエリー:Staff」を保存し、終了して下さい。
トランザクション用クエリーの作成
ここまで学習を進めてこられた方はクエリー作成法にだいぶ慣れてきたことと思います。これまでの作成法の説明を参考にして、次はトランザクション用クエリー「クエリー:Slip1」を作成して下さい。
クエリー名を「クエリー:Slip1」と、末尾に数字「1」を付けたのには訳があります。その訳は後で述べることにして、まずは基本となるクエリーを作成しましょう。下記の作成要件を守って作成してみて下さい。
＜「クエリー:Slip1」作成要件＞
・テーブル「Department」以外の全てのテーブルを検索対象とします。
・できるだけ受注伝票のイメージでフィールドを並べます。
・IDなど、コード類の右隣には名称が表示されるようにします。
・「エイリアス」行と「並べ替え」行はまだ設定しないで下さい。
（注）実装作業をすると気づく事ですが、「フィールド」行でドロップダウンリストからフィールドを選ぼうとしてもフィールド名がリストの下方に位置し、容易に表示されない場合があります。これは使用するテーブル数が多い場合、画面で処理しきれなくなったためです。このような場合は先に「テーブル」行でテーブルを指定した後で「フィールド」行でドロップダウンリストを開くと、指定したテーブルに関するフィールドがリストに表示されますので、容易に選択できます。また場合によっては「フィールド」行にフィールド名を直接入力する事も可能です。
①下図が各列の「フィールド」行にフィールドを設定し、アイコン「クエリーの実行」をクリックした状態です。なお、クエリーデザイン画面中央のテーブル設計デザイン領域は見やすいように配置し直しています。
②ここで「クエリー:Slip1」と命名して保存し、クエリーを終了して下さい。
③下図はクエリー「クエリー:Slip1」を実行した状態です。受注伝票IDごとに薄緑色の線で囲んでみました。「ID」列から「Slip.Note」列までが伝票のヘッダー部、「Item_ID」列から「SlipDetail.Note」列までが伝票の明細部です。同じ伝票内のヘッダー部は明細数分、同じデータが表示されています。
④クエリー「クエリー:Slip1」を編集モードで開いて下さい。次に金額と消費税をの計算をしましょう。
⑤一番右の空いている列の「フィールド」行に下記の式を入力します。
“UnitPrice” * “Quantity”
これは「単価×数量」の意味ですが、「*」などの演算記号はCalcの演算記号と同じです。この列の「エイリアス」行には「Amount」と入力します。
⑥さらに右の列の「フィールド」行に下記の式を入力します。
“UnitPrice” * “Quantity” * “TaxRate”
これは「単価×数量×消費税率」の意味です。左隣の列で金額を計算しているので、できれば、式を「“Amount” * “TaxRate”」としたい所ですが、この時点でエイリアス「Amount」はフィールドとして認識してくれませんので、上記のような式にしました。この列の「エイリアス」行には「Tax」と入力します。
⑦下図が２つの列に式とエイリアスを設定し、アイコン「クエリーの実行」をクリックした状態です。
⑧厳密に言うと消費税計算では小数点以下を切り捨てますので、式を変更します。関数「CEILING()」で式を囲ってやると計算結果の端数を切り捨ててくれます。下記が変更後の式です。
CEILING(“UnitPrice” * “Quantity” * “TaxRate”)
⑨追加した２列を「SlipDetail.Note」列の左に移動し、再度アイコン「クエリーの実行」をクリックした状態が下図です。
⑩この時点で「エイリアス」行に適切な名称を設定しておきましょう。特に「Name」や「Note」のように、同じフィールド名になっている列はユニークな名称にしておいて下さい。「エイリアス」行を設定してアイコン「クエリーの実行」をクリックした状態が下図です。
⑪ここで再びクエリー「クエリー:Slip1」を保存して終了して下さい。
⑫実は作成したクエリー「クエリー:Slip1」には税込金額の列がありません。もうひとつ列を作成して、下記の式を設定すれば確かに解決するのですが、これではあまりにも式が冗長すぎます。
“UnitPrice” * “Quantity” + CEILING(“UnitPrice” * “Quantity” * “TaxRate”)
⑬そこで次に新たなクエリーを作成し、既に作成したクエリーをあたかもテーブルのように検索対象にする手法を紹介します。こうすると、既成のクエリーで命名したエイリアスもフィールド名として利用することが可能になります。
⑭Base編集画面左ペインの「データベース」欄より「クエリー」を選択し、右ペイン上部の「タスク」欄で[デザイン表示でクエリーを作成]をクリックします。
⑮すると下図のように「クエリーデザイン」画面が表示され、さらにダイアログボックス「テーブルまたはクエリーの追加」が表示されますので、「クエリー」を選択し、クエリーリストから「クエリー:Slip1」を選択し、[追加]をクリックした後、[閉じる]をクリックします。
⑯「クエリーデザイン」画面上部にクエリー「クエリー:Slip1」の構造を表す図が表示されますので、ここで試みに一番左端の列の「フィールド」行に「クエリー:Slip1.*」を入力してアイコン「クエリーの実行」をクリックしてみましょう。
⑰すると下図のように画面上部に全てのフィールドの結果が表示されます。クエリーではテーブル名やクエリー名の次に「*」を指定すると全てのフィールドの値が検索されて表示されます。
⑱ここでフィールドの「*」指定を説明したのは手法のひとつを紹介したかったからです。今回は「クエリー:Slip1」の全てのフィールドを再度あらためて各列で指定することにします。
⑲下図が全てのフィールドを各列で指定し、アイコン「クエリーの実行」をクリックした状態です。これで「クエリー:Slip1」と同じ結果が得られるようになりました。
⑳ここで「クエリー:Slip2」と命名して保存し、クエリーを終了して下さい。クエリー名の末尾に数字を付けたのはテーブルを検索対象にしたクエリーと、クエリーを検索対象にしたクエリーを区別するためで、名前の付け方にルールはありません。
㉑あらためてクエリー「クエリー:Slip2」を編集モードで開いて下さい。
㉒空白列の「フィールド」行に下記の式を入力し、「エイリアス」行に「税込金額」と入力して下さい。
“Amount” + “Tax”
㉓再びアイコン「クエリーの実行」をクリックした状態が下図です。「クエリー:Slip1」ではエイリアスだった「Amount」は「クエリー:Slip2」ではフィールド名になったため、式内で使用することができるようになりました。「Tax」も同様です。
㉔下図は「税込金額」列を「明細備考」列の左に移動し、他の列も適切なエイリアスを設定した状態です。フィールド名「〇〇〇_ID」のエイリアスは表示列を短くするため「ID」としました。ただし、このあと並べ替えを指定する「Item_ID」を除いています。
残念なことにクエリーを検索対象にしたクエリーは日付の書式がデフォルトに戻ってしまうので、下図のように実行結果の画面で列「販売日」を右クリックし、コンテキストメニューから「列の書式」を選択します。
㉕すると、ウインドウ「フィールドの書式設定」が表示されますので、第4章のテーブル「Slip」作成の項で説明した要領で書式を変更して下さい。
㉖下図が列「販売日」の書式を変更した後の状態です。なお、現在のところ設定した書式は保存されず、実行の度に元に戻ってしまうようです。
㉗次に下図のように「受注伝票」列および「Item_ID」列の「並べ替え」行で「昇順」を指定します。この状態でアイコン「クエリーの実行」をクリックすると「受注伝票」列が昇順に、同一受注番号内では「Item_ID」列が昇順に並べ変えられます。
㉘自動生成されたSQL文を見てみる下図のとおりORDER BY句で並べ替えを指定しています。ちなみに昇順は「ASC」（ASCENDINGの略）、降順は「DSC」（DESCENDINGの略）のパラメーターで指定します。
㉙最後に各金額を集計するクエリーを作成してみましょう。新たに[デザイン表示でクエリーを作成]から始め、クエリー「クエリー:Slip1」を検索対象にします。
㉚受注伝票ID毎に金額、消費税、税込金額を集計するよう関数設定し、アイコン「クエリーの実行」をクリックした状態が下図です。
㉛集計クエリーを作成する際、下記の点を守って下さい。
・選択するフィールドはグループ化した時に同じ行で表示されるものに限ること。
・グループ化しないフィールドは指定しないこと。
・並べ替えはしないこと。
㉜下図は自動生成されたSQL文です。集計はSUM関数で行い、グループ化はGROUP BY句で行っていることが分ります。
㉝うまく集計されたので、「クエリー:Slip3」と命名して保存し、クエリーを終了して下さい。
Back> 7.トランザクション入力用フォーム作成
Next> 9.レポート作成