スポンサーサイト

上記の広告は1ヶ月以上更新のないブログに表示されています。
新しい記事を書く事で広告が消せます。

集計クエリー(その5)

今回が集計クエリーの最後になります。最後まで頑張っていきましょう。

1.注文書テーブルで1997/06/20以降で1回の注文で100個以上注文した分又は、1997/06/20以降で単価が300円以上の商品を注文した分の明細を表示。

 注文書複合条件1

ちょっと条件が複雑なので、整理します。条件は大きく2つに分けられます。

(1997/06/20以降) AND (1回の注文で100個以上注文した分)
日付の条件と数量の条件はAND条件なので、抽出条件欄の横に並べます。

(1997/06/20以降) AND (単価が300円以上の商品を注文した分)
これも日付と単価の条件はAND条件なので、抽出条件欄の横に並べます。

①と②の関係はOR条件なので、縦に並べます。ここでよく間違えるのは②の(1997/06/20以降)を省略してしまう事です。あくまで①と②は独立して判断されるので、②で日付の条件を省略してしまったら、日付に関係なく、単価300円以上のものが全て抽出されてしまいます。

以下が結果になります。

注文書複合条件1(結果)

2.注文書テーブルでひと月の注文合計が1万円以上の会社を金額の降順に並べる。

注文書複合条件2 

①まずひと月単位でグループ化する必要があります。単に「日付」でグループ化すると、年月日でグループ化されてしまいますので、Format関数を用いて年月単位のグループ化にします。

②グループ化を階層化します。同一年月で括り、更に会社名が同じものでグループ化します。

③「演算」でSUM関数を用い、月合計を求めます。

④求めた月合計が1万以上を抽出します。

⑤月合計の降順に並べます。

以下が結果になります。

注文書複合条件2(結果) 

3.注文書テーブルで種類毎に数量の合計、単価の平均、金額の合計及び金額比率(全体に対するその種類の金額の割合。小数点第2位で四捨五入)を金額合計の多い順に表示する。

注文書複合条件3

まず種類でグループ化します。数量の合計、単価の平均、金額の合計を求めます。ついでに並び替えを降順にしておきます。ここまでは今までの知識で出来ると思います。今回のポイントは金額比率を求める方法です。

金額比率を求めるためには種類毎の金額を全体の金額合計で割る必要があります。注文書テーブル全体の金額合計を求めるにはサブクエリーを使います。サブクエリーを使うにはSQLの知識が多少必要になります。

(SELECT SUM([数量]*[単価]) FROM 注文書)

上記の意味は注文書テーブル全体から[数量]*[単価]つまり金額の合計を求めるという事です。サブクエリーはSQL文必ずかっこで囲みます。

小数点以下第2位で四捨五入し、パーセンテージを求めるためには金額比率のプロパティを開き、以下の様に設定します。

注文書複合条件3プロパティ 

以下が結果になります。

20110207_7.jpg 

どうでしたか。最後のサブクエリーを使った例はかなり難易度が高い部類に入ると思いますが、このサブクエリーを使いこなせるようになると一段と応用力が増します。特に分析計のレポートを出力しなければならない場合は大いに役立つでしょう。

クエリーに関してはまだまだ様々なパターンがありますが、会得するには演習を積み重ねていくしかありませんので、自分なりに条件をいろいろ想定して結果を確認して見て下さい。

次回からはマクロについて見ていきます。
スポンサーサイト

テーマ : データベース
ジャンル : コンピュータ

集計クエリー(その4)

今回も引き続き、集計クエリーの演習を行います。条件が複合的になりますので、少し難しくなります。

1.注文書テーブルの97年7月における”飲料”及び”乳製品”各々の注文金額合計を求める。

 注文金額合計

グループ化、演算、Where条件が組み合わされた例です。順に見ていきます。

①日付の抽出条件の「97年7月」を設定するために、日付の集計欄を「Where条件」にします。97年7月ということは1997/07/01から1997/07/31の事ですから、以下の様にBetween関数を用いて指定します。日付型のフィールドは「#」で囲みます。
Between #開始日付# and #終了日付#

②商品の種類が"飲料"及び"乳製品"という条件もありますので、①と同様に種類の集計欄に「Where条件」を指定します。"飲料"と"乳製品"はどちらでもいいので「OR」で繋ぎます。①と②はAND条件になりますので、抽出条件欄は並列に記述します。もし①と②がOR条件でしたら抽出条件欄を段をずらすだけです。

③種類毎の集計値を見たいので、種類でグループ化します。

④最後に注文金額合計を求める部分ですが、集計欄を「演算」にし、[単価]と[数量]を掛けてそれのSUMをとり、金額合計を求めます。

以下が結果になります。

注文金額合計(結果) 

2.注文書テーブルで注文金額合計が10,000円以上の商品を抽出する。

注文合計で抽出 

1.ではWhere条件で抽出条件を指定しましたが、演算した結果を抽出条件にすることも可能です。商品でグループ化し、その金額合計が10,000円以上で抽出しています。

以下が結果になります。

注文合計で抽出(結果) 

3.注文書テーブルで単価が200円以上の商品の注文合計が10,000円以上の商品を抽出する。

注文合計で抽出2 

抽出条件のAND条件の例です。単価をWhere条件にして「>=200」と指定します。

以下が結果になります。

注文合計で抽出2(結果) 

4.注文書テーブルの注文数量の多かった順に、商品を並べ替える。

注文数量で抽出 

商品でグループ化し、その単位で数量の合計を取り、それを降順に並べます。

以下が結果になります。

注文数量で抽出(結果) 

次回は集計クエリーの演習の最後になります。

テーマ : データベース
ジャンル : コンピュータ

集計クエリー(その3)

前回に引き続き集計クエリーの演習をします。

1.成績表テーブルの学期毎に各学科の平均点を求める。但し、Nullは0点とみなす。

 学期毎の平均点

まず学期でグループ化します。データ中にNULL値が混在していると計算できないので、nz関数を用いて0に変換します。その上でavg関数を用いて平均を求めます。

以下が結果となります。

学期毎の平均点(結果) 

2.成績表テーブルの学期毎に各学科の最高点を求める。

学期毎の最高点

1.と同様まず学期でグループ化します。集計欄を「最大」にして最高点を求めます。

以下が結果となります。

学期毎の最高点(結果) 

3.成績表テーブルの1学期の国語の最高点を取った人の名前を表示する。

国語の最高点 

まずは抽出条件欄に「1」を設定し、国語の点数の降順に並べます。これで国語の点数の高い順に並びますが、最高点の人のみ表示するには「トップ値」プロパティを「1」に設定します。更に氏名を表示するために名簿テーブルと連結して「氏名」を抽出します。「トップ値」プロパティはACCESS独自の機能で「トップ5%」等の設定も可能です。

以下が結果となります。

国語の最高点(結果) 

4.成績表テーブルの学期毎の各学科の受験者数を求める。

学期毎学科毎の受験者数

学期でグループ化し、各学科毎の「カウント」を取るとNULLデータは集計値に含まれません。従って、未受験者は対象外となります。

以下が結果となります。

学期毎学科毎の受験者数(結果) 

5.成績表テーブルの1学期の受験者数を求める。

1学期の受験者数 

1学期で抽出し、レコードの件数自体をカウントしたい場合はCount関数を使ってCount(*)とします。Count([国語])とした場合はNULLデータがはじかれるので18件となります。

以下が結果となります。

1学期の受験者数(結果) 

次回では注文書テーブルを使い、更に複雑な集計クエリーに挑戦してみましょう。クエリーの習得するためには数をこなす事が大切です。

テーマ : データベース
ジャンル : コンピュータ

集計クエリー(その2)

今回は注文書テーブルを基に、集計クエリーの例題を解いていきます。

1.注文書テーブルの「商品」毎に、数量の合計を求める。


 商品毎の数量合計

まずは商品毎に集計するので、商品フィールドの集計欄を「グループ化」にします。数量フィールドの集計欄は合計」にします。

以下表示結果です。

商品毎の数量合計(結果) 

2.注文書テーブルの「商品」毎に、金額(「数量」×「単価」)の合計を求める。

商品毎の金額合計 

単一のフィールドを基にした「合計」は1.のような指定方法でいいのですが、複数のフィールドを組み合わせた計算など多少複雑な計算の場合は、集計欄は「演算」を指定し、フィールド欄に関数を組み合わせた指定をします。

[数量]*[単価]で金額を求め、SUM関数でその金額の合計を計算します。

以下表示結果です。

商品毎の金額合計(結果) 

3.注文書テーブルの年月毎に金額の合計を求める。

年月毎の金額合計 

グループ化するフィールドに単に「日付」を指定した場合は、年月日毎にグルーピングされてしまいます。年月毎にグルーピングしたい場合は、format関数で表示書式を"yyyy/mm"と指定します。

以下表示結果です。

年月毎の金額合計(結果) 

次回も引き続き集計クエリーについて見てみます。

テーマ : データベース
ジャンル : コンピュータ

演算クエリー(その2)

ACCESSのクエリーではフィールドを抽出する段階でそのフィールドに対して演算を施すことが出来ます。

1.注文書テーブルの「単価」×「数量」で金額を計算する。

 金額の計算

最も単純な例としてはフィールド同士で算術演算子を用いて四則演算を行うことです。当然のことながら、演算対象になるフィールドは数値型でなければならないし、0除算などを行えばエラーになります。

以下が結果になります。

金額の計算(結果) 

2.注文書テーブルの「単価」と「数量」で計算した金額の全体の平均値を求める。

金額の全体平均 

ACCESS標準の関数を使用することも出来ます。主に以下の様な関数があります。

SUM():合計
AVG():平均
MIN():最小
MAX():最大

以下が結果になります。

金額の全体平均(結果) 

計算結果に端数が生じた場合、端数処理のためにformat()関数を用いる事が出来ます。

format(任意の有効な式,"書式を表す文字列"):第1引数にはフィールド名が指定されることもありますし、この例のように計算式が指定されることもあります。第2引数には出力書式を指定します。数値型のよく使われる書式としてはこの例のような3桁毎のカンマ区切りで先頭の0を抑制したものが使われます。小数点以下は自動的に四捨五入されます。

金額の全体平均2 

結果は以下の様になります。

金額の全体平均2(結果) 

3.成績表テーブルのNULL値を0に変換して、各行の教科合計を求める。

成績の教科合計

データ内には点数が入力されていない(テストを受けていない人と見なす。0点は実際に受験して結果0点であることを示す。)人がいて、そのデータはNULL値となっています。このまま演算処理をすると、エラーになります。その為、0に変換して合計を求める必要があります。

NZ():NULL値を0に変換する関数。

以下が結果になります。

成績の教科合計(結果) 

次回では再度集計クエリーについて例題を解いていきます。

テーマ : データベース
ジャンル : コンピュータ

最新記事
全記事表示リンク

全ての記事を表示する

月別アーカイブ
カテゴリ
お問い合わせ
まずはこちらのフォームより御一報下さい。

名前:
メール:
件名:
本文:

検索フォーム
RSSリンクの表示
アクセスランキング
[ジャンルランキング]
コンピュータ
859位
アクセスランキングを見る>>

[サブジャンルランキング]
ソフトウェア
108位
アクセスランキングを見る>>
ブログランキング

FC2Blog Ranking

上記広告は1ヶ月以上更新のないブログに表示されています。新しい記事を書くことで広告を消せます。