Microsoft Access Club >初心者講座 >CDアルバム管理ソフト >このページ >次頁 - 前項


クエリの作成と解説_2 : CDアルバム管理ソフト11



前回は、「テーブル作成クエリ」と「削除クエリ」についてお話しましたが、これらはちょっとマイナーなクエリでした。今回、主役の集計クエリクロス集計クエリを取り上げます。

Accessの計算機能は、おせいじにも優れているとは言えません。
テーブルには計算機能は無く、フォームやレポート、モジュールといったところはお寒い限りですね。一人クエリだけが気を吐いている感じです。

クロス集計クエリ

「クロス集計クエリ」を作成しましょう。いろいろなバリエーションがありますので、代表的なものを今回作成します。

作成の注意点

2つ以上のテーブルを基に作成する場合、それらのテーブルで構成された選択クエリを最初に作成しておく必要があります。

但し、今回は、tbl_mainテーブルのみで作成しますので、事前に選択クエリを作成することは不要です。 なお、選択クエリを事前に作成してから、それを元にクロス集計クエリを作成されてもいいですよ。

作成するクロス集計クエリは、アーティスト別に入力月ごとの所蔵CD枚数を一覧表示します。加えて、CD枚数の合計も表示させます。

新規作成

クエリの新規作成には、付属の"ウィザード"を利用して下さい。下記の手順で進んで下さいね。。

  1. 新規作成をクリックすると、各種クエリの選択画面が現れます。

  2. "クロス集計クエリ"を選択して下さい。

  3. 次に、どのテーブル又はクエリを基に作成しますかと尋ねてきますので、先ほど作成した「qry_mainsub」を指定して下さい。

  4. 次に、行見出しとして使うフィールドを尋ねてきますので、「アーティスト名」を選択して下さい。行見出しとは、左もしくは右に位置するフィールドです。これによって、グループ化及び並び替えが自動的に行われます。最大3フィールドまで指定できます。

  5. 次に、列見出しとして使うフィールドを尋ねてきますので、「入力日」を選択して下さい。

  6. "日付/時刻型"を列見出しとして選択すると、次にグループ化の"単位"を尋ねてきます。

  7. 今回のサンプルでは、"月"を選んで下さい。"四半期"がよければそちらをどうぞ。

  8. 次に、"集計フィールド"と"集計項目"を尋ねてきますので、「タイトル名」と「カウント」を選んで下さい。

  9. "行ごとに集計値を表示しますか"に対しては、チェック済みにして下さい。列の合計が行ごとに表示されます。

  10. これで終了です。適当な名前を付けて下さい。ちなみに私は「qry_cross」と命名しました。

下記のようになりましたか?

 

式1: Format([入力日],"oooo")  :  AccessTips--No.43奇妙な日付/時刻 書式の一例

 

 

アレンジ

これはあくままで基本形なので、下記のように少しアレンジします。

  1. 各アーティストごとに曲数の合計を追加。

  2. データ空白月には"0"を表示。

  3. 「アーティスト名」のコンボボックス指定の解除。

  4. 対象年月をWhere条件として、データ抽出。

1 について -- 作成したクエリをデザインビューで開きます。「tbl_main」の「全曲数」をクエリフィールドへドラッグし、集計は"合計"、行列の入れ替えは"行見出し"を指定します。

2 について -- クエリでの"0"表示の方法は、

  1. "0"を表示したいクエリフィールドのプロパテを開きます。
  2. 「書式」に 0;-0;0;0 を記述して下さい。

 

  1. この意味は、「正の数値である場合はそのまま表示し、負の数値の場合は数値の前に"-"を、

  2. ゼロの場合は"0"を、NULL値の場合も"0"を表示しなさい」と云う意味です。

  3. 但し、このクエリを基にフォームを作成した場合は、フォームのテキストボックス等に上記書式を設定する必要があります。

3 について -- クエリフィールド「アーティスト名」のプロパティを開きます。「ルックアップ」の「表示コントロール」で、"テキストボックス"を選択し変更します。

4 について -- 表示するデータの対象期間を設定することを意味します。数年間に亘るデータを格納している場合を想定して下さい。

この場合は、月単位で集計を行っていますので、数年間に渡る合計値が表示されてしまいます。これを避ける意味からも、抽出条件を設定する必要があります。また、抽出条件設定しデータを表示されるには、2パターンあります。

  1. パラメータクエリを利用しクエリから実行(直接、クエリを開く場合を云います)する方法

  2. フォーム(フォームのテキストボックス等を利用することを云います)から実行する方法

 

パラメータクエリの利用

パラメータクエリを利用する場合は、

  1. 新たにクエリフィールドに「入力日」を追加し、集計を"Where"とします。

  2. qry_cross_parameter を利用のこと。

  3. "抽出条件"に下記のように記述します。

  4. これでクエリを実行すると入力を求める下記のようなダイアログメッセージが表示されます。

  5. Between [開始年月日を入力して下さい。] And [終了年月日を入力して下さい。]

 

抽出フォームの利用

次にフォームを利用する方法は、

  1. フォームウィザードを利用して新規フォームを作成します。

  2. qry_crossr を利用のこと。

  3. 出来上がったフォームのフッダー等に下記サンプルのようにテキストボックスを配置します。

  4. フォームの元になっているクエリのフィールドに「入力日」を追加し、集計を"Where"とします。

  5. "抽出条件"を下記のように記述します。

  6. Between [Forms]![frm_cross]![日付1] And [Forms]![frm_cross]![日付2]

  1. 日付1及び日付2はフォーム(frm_cross)のテキストボックス名です。

  2. コマンドボタン(標題は 対象年月日の指定(&A))を作成し、その「クリック時」イベントに"全レコード表示"マクロを設定して下さい。

クロス集計クエリだけでなくクエリ全般に言えることですが、データを抽出してこそクエリの価値が倍増します。よって、常に Where条件の設定 を念頭に置いてクエリに望んで下さい。

補足として…

作成したクロス集計クエリをデザインビューで開いて下さい。式1のフィールドのプロパティを開くと、自動的に"クエリ列見出し"に下記のような設定がされていますね。

"1月","2月","3月","4月","5月","6月","7月","8月","9月","10月","11月","12月"

これは、

データが無い月があっても、列見出しを1月から12月まで規則正しく整列させる方法

なんですが、

  • クエリの抽出と組み合わせると、抽出の指定を行っても行わなくても、1月から12月まで列表示がされてしまう欠点

があります。この点ご注意下さい。

 集計クエリ

集計クエリは、グループ化を行って四則計算や平均値、最大、最小値などの計算を行うんですが、このグループ化と云う 言葉の意味が分かりにくいですね。

実例を挙げて云いますと、月、火、水曜日にA、B、Cの人間がハンバーガーを数個づつ購入したと仮定します。テーブルで表すと下記のようになりますね。

ID 日付 購入者 購入個数
1 2003/03/01 A 2
2 2003/03/01 B 5
3 2003/03/01 C 10
4 2003/03/02 A 5
5 2003/03/02 C 9
6 2003/03/03 A 1
7 2003/03/03 B 6

これを、曜日を基準にしたグループ化で下記のように「集計クエリ」を作成します。

このクエリは、曜日ごとに何個のハンバーガーを購入したのかをあらわします。購入者はクエリフィールドに入れません。また、購入個数は「合計」を選択します。

クエリ結果は下記のようになります。

曜日

購入個数

6

5

8

試しに、購入者をクエリフィールドに追加して曜日と同じくグループ化を設定すると、テーブルデータと同じ内容になります。当然ですよね。「合計の購入個数」の「集計」欄は最大、最小、カウントと云った集計関数も選択できますので、各自チャレンジして下さい。

ちなみに以前作成しました入力フォームで「アーティスト名」のコンボボックスを作成する際に"カウント"を利用しています。ご存知ですね?但し、このCDソフト内では、今のところ「集計クエリ」を利用する予定はありません。悪しからず。。。

紙面も少なくなってきたので今日もこの辺で失礼しますが、クエリの働き役割は多方面に及んでいますので、充分に説明し切れません。ゆうに一冊の解説書にもなってしまいます。"裏方の"クエリがフォーム、レポートの成否に関わっていますので、多くの実例に接して早く自分のものにして下さい。

 

次回は、ファイルを開いたときによく表示される"スタートアップフォーム"とアプリケーションの目次である"インディックスフォーム"の作り方をお話します。お楽しみに……!
 

ページの先頭へ 前ページへ戻る


クエリの作成と解説_2 : CDアルバム管理ソフト11


 

-Microsoft Access Club-