情報分析演習 Access 第 3 回 注意 : この回の操作演習は 第 2 回で作成したアドレス帳の内容をもとに実施します クエリ query [1] 疑問, 質問.(question の形式張った語 ) [2] 疑い, 疑惑, 疑念 [3] 疑問符 [4] 問い合わせ ( ランダムハウス英和辞典より ) データベースでは 問合せや照会の意味で テーブルのデータからある条件に見合うものを引っ張り出し それに対して作業を行うこと Access で作成できるクエリの種類 選択クエリ 1 つまたは複数のテーブルのデータを取得し 結果をデータシートに表示する また データの合計 カウント 平均等を計算できる パラメータクエリ レコードの抽出条件や フィールドに挿入する値等のデータを入力する独自のダイアログボックスを表示するクエリ クロス集計クエリ 合計 カウント 平均等の集計値を 1 行ごとに表示し データを一連の項目別に集計するクエリ アクションクエリ 1 つの操作で多くのレコードを変更するクエリ 削除 更新 追加 テーブル作成の 4 つの種類がある SQL クエリ SQL ステートメントを使って作成するクエリ クエリのデザインビューで開くことができるもの 選択クエリ / クロス集計クエリ / アクションクエリ データシートビュークエリで開くことができるもの 選択クエリ / クロス集計クエリ ワイルドカード文字 文字 * 任意の数の文字と一致 意味使用例例の意味 港区 * 港区で始まる文字列 ( 港区三番町など ) と一致する ( 文字列の先頭または最後文字に使用可 )? 任意の1 文字と一致 港区? 番町 港区三番町 港区 1 番町と一致 する [ ] 角カッコ内の任意の 1 文字と一致! 角カッコ内の文字以外と一致 - 範囲内の任意の文字と一致 広野 [ 様殿君 ] 広野 [! 様 ] a[a-c]d 広野様 広野殿 広野君と一致する 広野殿 広野君と一致する 広野様には一致しない [a-c] は a,b,c を表している aad,abd,acd と一致する # 任意の数字と一致 1###9 12329 など1で始まり9で終わる 5 桁の数字と一致する 比較演算子 演算子意味例例の意味 = 同じ = abc abc と同じ文字列 < より小さい <500 500 より小さい数値 > より大きい >500 500 より大きい数値 <= 以下 <=500 300 以下の数値 >= 以上 >=#2006/04/01# 2006 年 4 月 1 日 以降 <> 違う <> abc abc ではない文字列 1
データ型による値の表記 リレーションシップ Access データベースのリレーションシップ機能 目的の異なるテーブルを複数作成した場合 テーブル間にリレーションシップを定義すると 関連するデータを組み合わせて使うことができる クエリ フォーム およびレポートで 複数のテーブルのデータを一度に表示することができる データ型値の表記例 日付 # #2006/04/01# 文字列 abc 数値 100(100 は数字だが 100 は 100 という文字列 である ) 参照整合性 リレーションシップの種類 ( リレーションシップは同じデータ型同士で設定する ) 種類説明備考 1 対多参照元レコード 1 つに対して参照先にたくさんのレコードがある 1 対 1 参照元レコード 1 つに対して参照先に 1 つのレコードがある もっとも使用される 双方が主キーの場合 データ型 参照整合性 フィールドの連鎖更新 用途 2 つのテーブル間で結び付けられた内容に矛盾が起きないように機能が制限される 1 側のレコードがないと 関連する 多 側のレコードは入力できない 1 側のレコードを単独で削除できない 関連する 多 側のレコードは単独で削除できる 2 つのテーブル間で結び付けられた一方の内容を変更すると 関連付けられた内容も変更される レコードの連鎖削除 1 側のレコードを削除すると 関連する 多 側のレコードも同時に削除される 2
実施する内容 操作演習 3 クエリとリレーションシップについて 1 選択クエリの作成 2 選択クエリで選択したフィールドへの抽出条件の設定 ( 前回作成した nexxxxx のアドレス帳データベースを開く ) ウィザードを使用してクエリを作成 ( リボン 作成タブ ] から ) その他 [ クエリウィザード ] を選択 選択クエリウィザード OK テーブル : 住所録を選択し 氏名 住所 生年月日 グループのフィールドを選択する 次へ クエリで集計を行うかどうかのオプションは [ 各レコードの全てのフィールドを表示する ] を選択 クエリ名 : 電話番号なし住所 を入力して完了 住所録テーブルのクエリ 電話番号なし住所 の選択結果が表示される クエリの内容をデザインビューで確認 ウィザードで作成したクエリの設定内容を確認するためにクエリのビューをデータシートビューからデザインビューに変更する 選択クエリで ウィザードで指定したフィールドが項目として選択されていることを確認する (1 で作成したクエリ 電話番号なし住所 のデザインビューから ) グループの絞込み フィールド : グループの抽出条件のところに 1 を入力して実行 ( 結果 の 実行 ボタンをクリック ) 住所の絞込み ( デザインビューに戻して ) グループの 1 を削除し 住所に * 東京 * と入力し ( 東京以外でも可 ) 実行 ( 同様に他の条件も試してみてください ) 選択クエリの操作 / リレーションシップ 前回実施の 演習 アドレス帳の作成 (1) で作成したデータが前提 住所録テーブルに 20~30 件くらいのデータ入力済み 入力必須項目 :ID, 氏名 住所 生年月日 グループ クエリで集計を行うかどうかのオプションは [ 集計する ] を選択すると選択した項目の集計値を計算可能 抽出条件の * 東京 * は 東京という文字をどこかに含む の意味 同様に *1991*] 等の指定が可能 抽出条件を含めた状態でクエリとして保存可能 3
3 リレーションシップの作成 ( 準備作業 ) テーブル 住所録 のフィールド 年賀状の送付 の 設定確認 ( デザインビューから ) 年賀状の送付フィールドのデータ型が Yes/No 型になっているかを確認 新規テーブルの作成 ( デザインビューから ) フィールド名 : 受信簿 ID, データ型 : オートナンバー型 フィールド名 : 住所録 ID, データ型 : 数値型 フィールド名 : 年賀状 2010 データ型 :Yes/No 型 フィールド名 : 年賀状 2011 データ型 :Yes/No 型 ( 主キー : 受信簿 ID) テーブル名 : 受信簿 作成したテーブル 受信簿 へのデータ入力 ( データシートビューから ) 住所録 ID の入力 ( テーブル 住所録 の住所録 ID で入力した番号まで入力する ) 2009 年と 2010 年の年賀状受信のチェックボックスを適当に ON にする ( それぞれ 2009 年と 2010 年に年賀状をもらった人という想定で入力する ) リレーションシップの作成 リボン[ データベースツール ] [ リレーションシップ ] クリック [ リレーションシップ ] [ テーブルの表示 ] 受信簿 テーブルを追加 住所録の住所録 ID を受信簿の住所録 ID のところまでドラッグする ( リレーションダイアログボックスで ) 作成をクリック を 住所録テーブルと受信簿テーブルの住所録 ID どうしを結びつける 参照整合性は レコードの入力や削除を行っても テーブル間のリレーションシップが維持されるようにするための規則 ( 今回の使用なし ) 4
4 リレーションシップを用いたクエリの実行 デザインビューからクエリを作成する ( リボン [ 作成タブ ] から )[ クエリデザイン ] を選択 テーブルの表示ダイアログボックスで住所録と受信簿を選択 ([ 追加 ] をクリック ) フィールドに以下のものを設定する 住所録テーブル : 氏名 住所録テーブル : 年賀状の送付 受信簿テーブル : 年賀状 2010 受信簿テーブル : 年賀状 2011 保存するクエリ名 : 年賀状受信履歴 抽出条件の設定 2009 年と 2010 年の両方に年賀状受信を抽出条件に設定 ( yes と入力 ) し クエリ実行 実行結果に基づき 年賀状の送付チェックボックスを ON にする 今 2012 年の年賀状を準備中という想定での練習です ( 後の授業で それから時間が経過したという想定で 演習を行います ) 5
操作演習 4 実施する内容 引き続きデータベース :My アドレス帳を用いてレポート作成の方法を習得 1 レポートの作成 単一テーブルを用いた場合 ウィザードを使用する場合 テーブル 住所録 の一覧表を作成 ( 操作演習 3 で作成した nexxxxx のアドレス帳データベースを開く ) ( リボン [ 作成タブ ] から )[ レポートウィザード ] を選択 ( レポートウィザードより ) テーブル 住所録 を選択し グループ 住所録 ID 氏名 郵便番号 住所 生年月日 年賀状の送付 のフィールドを選択する グループレベルの設定は特に指定しないで [ 次へ ] レコードを並べ替える方法の指定 1: グループ 2: 住所録 ID ( いずれも並べ替え順は昇順にする ) 6
2 レポートの作成 単一テーブルを用いた場合 ウィザードを使用する場合 レポートの印刷形式 : 単票形式 スタイル :Access2007 レポート名 : 住所録 作成したレポートが表示される ( 印刷プレビューになっている ) レポートの印刷形式の違い 単票形式 ( 続き ) 表形式 帳票形式 7
( 応用問題 ) 複数のテーブルのデータを用いた ウィザードを用いたレポートの作成 データベースneXXXXX のアドレス帳に次の内容のレポートを作成してください ( ウィザードを用いて作成してかまわない ) 対象テーブル テーブル 住所録 から以下の項目を選択 グループ 住所録 ID 郵便番号 氏名 住所 生年月日 年賀状の送付 続けてテーブル 受信簿 から以下の項目を選択 年賀状 2010 年賀状 2011 スタイル Access2007 を選択レポート名は既に作成したレポートと異なる名前で登録 作成したレポートについて 必要なレイアウト修正 ( 項目が途中で途切れず表示されているか等 ) を行なう 8
情報戦略基礎演習 Access(2) 参考資料 作成したリレーションシップの修正方法 ( 例 ) テーブル 住所録 のフィールド 住所録 ID と テーブル 受信簿 のフィールド 住所録 ID のリレーションシップを作成しようとしたのに 誤ってテーブル 住所録 のフィールド 住所録 ID と テーブル 受信簿 のフィールド 受信簿 ID のリレーションシップを作成してしまった場合 1 誤って作成したリレーションシップの削除 誤って作ってしまったリレーションシップの削除 リレーションシップの ([ データベースツール ] [ リレーションシップ ] から削除したいリレーションシップの線を右クリックし [ 削除 ] をクリック 2 正しいリレーションシップの作成 当初作成しようとしていた ( 正しい ) リレーションシップの作成 住所録の住所録 ID を受信簿の住所録 ID のところまでドラッグする ( リレーションダイアログボックスで ) 作成をクリック 9
すでにリレーションシップを作成したフィールドのデータの型を修正する方法 ( 例 ) テーブル 受信簿 のフィールド 住所録 ID のデータの型を 数値型 に入力するはずが入力ミスで テキスト型 にしてしまっていた場合で そのフィールドにリレーションシップを作成してしまっていた場合 1 作成したリレーションシップの削除 すでにリレーションシップを作成したフィールドのデータの型を修正する場合には 一旦作成されているリレーションシップを削除する必要がある リレーションシップの削除 リレーションシップの ([ データベースツール ] [ リレーションシップ ) から削除したいリレーションシップの線を右クリックし [ 削除 ] を左クリック リレーションシップを作成した項目間のデータの型は一致している必要があります 一致していない場合には修正を行なわないと そのリレーションシップを用いたクエリ等の実行に影響を及ぼします 2 テーブルのフィールドのデータの型の修正 テーブルデザインビューからクエリを修正する ( データベースウィンドウから ) テーブル : 受信簿を選択する ( テーブル : 受信簿のデザインビューから ) フィールド : 住所録 ID のデータの型を テキスト型 から 数値型 に変更し保存する 3 リレーションシップの再作成 リレーションシップの作成 住所録の住所録 ID を受信簿の住所録 ID のところまでドラッグする ( リレーションダイアログボックスで ) 作成をクリック 10