Part4

●

データの集計

 

 

・

・

・

・

 

 

４−１

データを抽出して合計を求める

 

　データベースに集めたデータをさまざまな角度から集計すると、利用価値が高まります。

　たとえば、請求書番号テーブルのフィールド[発行日]と請求明細(マスター)テーブルのフィールド[金額]から、○○会社の×月の請求総額を求めることなどができます。

 

 

? データの集計方法

　データベースを構成するいろいろなテーブルのデータの中から、設定した条件に合うデータを取り出してそれを集計します。

 

1◆集計の手順

　条件に合うデータを取り出すためにはクエリを使い、合計などの集計計算はフォームやレポートで行います。(図4.1参照)。

　フォームはおもに画面表示を行うための役割をもちますが、表示したフォーム(伝票や集計表の形になっている)をそのまま印刷することもできます。

　レポートはおもに印刷のための役割をもちます。クエリで集めたデータの一覧や、合計などの計算結果を印刷するには、レポートが便利です。

　また、サブフォームを使ってレポートをフォームに組み込み、明細一覧表付きのフォームとすることも可能です。

　レポートについては、本書の姉妹編『仕事に使える Access2000活用ガイド 応用編』で解説します。

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

キーワード

■レポート　印刷を目的としたフォーム

 

 

�A顧客別のデータを集める

　Part3までで作成したデータベースでは、請求金額のデータは請求明細(マスター)テーブルに記録されています。このデータベースから顧客ごとのデータを集計するために、顧客番号に相当するID番号によって必要なデータを集めます。しかし、請求書の発行先の顧客名は請求書番号テーブルに記録されているので、請求明細(マスター)テーブルを見ただけでは判別ができません。したがって、顧客別の請求明細を知るためには、2つのテーブルから必要なデータを取り出さなければなりません。テーブルからデータを取り出すことを、抽出といいます。

 

1　新規クエリを作成する

　クエリでテーブルからデータを抽出します。まず、顧客別のデータを集めるために、顧客名が記録されている請求書番号テーブルをもとにしたクエリを作成します。

 

●新規クエリの作成

 

>操作

図4.2　データベースウィンドウでクエリ一覧を表示する

　　　�@[新規作成]をクリックする

図4.3　[クエリの新規作成]ダイアログボックスが開く

　　　�Aデザインビューを選択して[OK]をクリックする

図4.4　新規のクエリが開いて、テーブルの表示ウィンドウが開く

　　　�B[テーブル]タブをクリックしてテーブル一覧を表示する

　　　�C請求書番号テーブルを選択して[追加]をクリックする

　　　アテーブルがクエリに表示される

　　　�D[閉じる]をクリックしてテーブルの表示ウィンドウを閉じる

 

 

　　

ac7h577z　　　　　　　　　　　　　　　　　　　AC7H578Z

 

 

 

 

 

 

 

 

 

 

 

 

 

AC7H579Z

 

●フィールドリストの登録

　次に、フィールドをフィールドリストに登録します。操作の詳細は、230ページの「�Bクエリで複数のテーブルを連結する」で解説してあります。

　ここでは、請求書番号テーブルの[請求書番号]、[発行日]、[顧客名]、[消費税率]の4つのフィールドを登録します。フィールド一覧の先頭[請求書番号]を選択し、[Shift]キーを押しながら[↓]キーを押してまとめて4つのフィールドを選択できます。

 

>操作

図4.5�@登録するフィールド名を選択する

　　　�Aフィールドリストの先頭へドラッグアンドドロップする

図4.6アフィールドが登録される

 

　

AC7H580Z

AC7H581Z

 

●結果の確認

　データシートビューに切り替えて、データが抽出されたようすを見てみましょう。図4.7のように、すべての請求書番号のデータが表示されます。確認できたら、デザインビューに戻します。

 

　AC7H582Z

 

2◆クエリで顧客名を指定する

　顧客別のデータを抽出したいのですから、抽出条件として顧客名を指定します。フィールド[顧客名]の抽出条件として何か顧客名を入力すると、その顧客のデータ一覧が抽出されて、表示されます。

　クエリの名前は「選択クエリ」となっていますが、必要なデータを選択して一覧表示するのでこのような名前になっているのです。「選択」といことばは、データの抽出と同じ意味と考えればよいでしょう。

 

>操作

図4.8�@抽出条件の欄に顧客名を入力する

図4.9　データシートビューに切り替えて抽出結果を確認する

ア 指定した顧客名のデータ一覧が表示される

イ データを見やすくするためにウィンドウサイズを大きくする

 

　

AC7H583Z　　　　　　　　　　　　　　　　　　　　　AC7H584Z

 

　データシートビューでウィンドウサイズを変更すれば、デザインビューの表示サイズも変化します。ここでデータ確認のために縦方向に少し広げているので以後のデザインビューでも縦に少し大きくなっています。（［抽出条件］［または］の空白行が増す）

　データシートビューでデータ確認のためにウィンドのウサイズを変更すれば、そのつどデザインビューでも変更されるので、その都度、適切に変更してください。

 

 

確認できたら、デザインビューに戻します。顧客名は、図4.10イのようにダブルクォーテーション”で囲まれています。コンピュータが扱う文字列データは、ダブルクォーテーションで囲むという約束ごとがあります。Accessの場合は、ユーザーが文字列のデータを入力するときにダブルクォーテーションを省略しても、アプリケーション側で文字列であることを判断して、自動的にダブルクォーテーションが追加されます。

抽出の条件式を正しく記述すると、

 

＝”しろくま楽器”

 

となりますが、先頭の「＝」も省略して入力できます。ただし、複雑な抽出条件を記述した場合に＝を省略すると、思わぬ結果になることもあります。絶対に省略できない場合もあるので注意が必要です。

 

 

　　　

AC7H585Z　　　　　　　　　　　　　　　　　　　　　　　AC7H586Z

 

3　クエリに請求明細を付加する

　請求明細を表示して、最終的な目的である請求金額の合計計算の準備をしておきます。請求明細は請求明細（マスター）テーブルに記録されています。したがって、クエリに請求明細（マスター）テーブルを追加します。

 

●テーブルの追加

　ショートカットメニューの[テーブルの表示]で追加します。

 

>操作

図4.11�@選択クエリのウィンドウで右クリックしてショートカットメニューを表示する

　　　�A[テーブルの表示]を選択する

図4.12　テーブルの表示ウィンドウが開く

　　　�B[テーブル]タブをクリックしてテーブル一覧を表示する

　　　�C請求明細(マスター)テーブルを選択して[追加]をクリックする

　　　アテーブルがクエリに表示される

　　　�D[閉じる]をクリックしてテーブルの表示ウィンドウを閉じる

 

 

 

 

 

 

 

 

　AC7H587Z

 

AC7H588Z

AC7H589Z

 

 

 

 

 

 

　テーブルを追加登録したとき、2つのテーブルに同じフィールド名があれば、自動的にフィールドが結合されます(図4.12アフィールド間に接続線が表示される)。フィールドが結合されなかったり(同図イ)、結合を削除してしまった場合は、322ページで解説する「●フィールドの結合」でフィールド［請求書番号］の結合を設定します。

 

●フィールドリストへの追加

　請求明細（マスター）テーブルのフィールド［行番］、[請求書番号]、[数量]、[商品ID]をフィールドリストに登録します。

 

>操作

図4.13�@登録するフィールド名を選択(反転表示)する

　　　�Aフィールドリストの空いているフィールドにドラッグアンドドロップする

図4.14�Bスクロールバーを操作して追加したフィールドを確認する

　　　アフィールドが追加される

　　　イ ウィンドウサイズは適切に変更する

 

 

 

AC7H590Z

AC7H591Z

 

●結果の確認

　データシートビューに切り替えて確認してみましょう。図4.15アのように同じ顧客名のデータが抽出されていることがわかります。またデータの件数は23です(同図イ)。

 

AC7H529Z

 

 

 

●フィールドの結合

　データシートビューに切り替えたとき、図4.16アのようにレコード数が移乗に多く表示されたら、それはフィールドの結合がリレーションシップの設定によって自動設定されなかったか、まちがって削除してしまったため（図　イ）と考えられます。

 

AC7H594Z

AC7H593Z

 

　上記の現象は、図4.14の請求書番号テーブルのフィールド[請求書番号]と、図4.15の請求明細(マスター)テーブルのフィールド[請求書番号]が、別のものとして扱われているために起きたものです。2つのフィールド[請求書番号]は同じものであることをクエリで指示しないと、目的のデータ抽出が行われません。

　両方の[請求書番号]が同じであることをクエリに指定します。これを、「フィールドの結合」といいます。

 

>操作

図4.17�@フィールド名をドラッグアンドドロップで結合する

図4.18ア接続線が表示される

図4.19　データシートビューに切り替えて抽出データを確認する

　　　イデータ件数が正しく抽出されている

図4.20�Aスクロールバーを操作して隠れているフィールドも確認しておく

 

図4.19、図4.20の抽出条件は顧客名「しろくま楽器」です。データのならびは操作の順序、システムの状態などで変化するので、図と同じにならない場合があります。データシートビューのウィンドウ表示サイズが小さくて隠れているフィールドがあればスクロールバーを操作して確認しておく

 

 

 

　　　　

AC7H595Z　　　　　　　　　　　　　　　　　　　AC7H596Z

 

AC7H597Z

 

●フィールド結合の解除

　まちがってフィールド結合を設定したり、自動的に設定されたフィールド結合が期待したものと違っている場合は、次の操作でリレーションシップの設定によって結合も解除できます。

 

>操作

図4.21�@接続線（斜線の部分）を右クリックしてショートカットメニューを開く

　　　�A[削除]をクリックする

図4.22ア接続が解除され接続線が消える

 

　　

AC7H599Z　　　　　　　　　　　　　　　　　AC7H598Z

 

 

 

　AC7H600Z

 

●フィールド間の計算

　フィールド[金額]を追加して、目的の合計金額を求めます。「＝数量×単価」はすでに請求明細クエリで計算してあるので、請求明細クエリのフィールド［金額］を作成中のクエリに追加します。ここでもフィールド［行番］は自動的に結合されますが、自動的に接続されなかった場合は図4.23イのように接続してください。

 

>操作

図4.23　テーブルの表示ウィンドウを開く

　　　�@[クエリ]タブをクリックする

　　　�A[請求明細クエリ]を選択して[追加]をクリックする

　　　アクエリが表示される

　　　イフィールド[行番]が結合される

　　　�B[閉じる]をクリックしてウィンドウを閉じる

 

　　　

　　　AC7H601Z

 

　次に、フィールド[金額]をフィールドリストに登録します。

 

 

 

 

 

 

 

>操作

図4.24�Cフィールド[金額]をドラッグアンドドロップでフィールドリストに追加する

図4.25ウフィールド[金額]が登録される

図4.26　データシートビューに切り替える

　　　�Dスクロールバーを操作して[金額]を表示する

　　　エ金額が表示されている

 

AC7H602Z

 

AC7H603Z

 

AC7H604Z

 

これでクエリの完成です。このクエリをもとに、フォームなどを利用して総合計を集計します。次項でフォームを使って集計する方法、さらに日付(期間)を指定した月合計などの方法を解説します。

　クエリ完成の仕上げとしてフィールドリストに登録したフィールドの中から、まちがって登録したフィールド、利用するつもりで使用しなかったフィールドなど不要なフィールドを削除しておいたほうがよいので、237ページの「●列の削除」で解説した方法で削除しておきましょう。

 

 

4◆クエリを保存して閉じる

　クエリを保存して閉じます。ここでは、クエリを「顧客別の集計」とします。

 

>操作

図4.27�@[×]ボタンをクリックしてクエリを閉じる

図4.28ア保存確認のダイアログボックスが表示される

　　　�A[はい]をクリックする

図4.29イ[名前を付けて保存]ダイアログボックスが開く

　　　�B適切な保存名(クエリ名)を入力する

　　　�C[OK]をクリックする

図4.30　データベースウィンドウに戻る

　　　ウ保存名(クエリ名)が表示される

 

 

 

 

AC7H606Z

 

AC7H607Z

　　　　　　　　　　　　　　　　　　　AC7H608Z

 

 

 

 

 

 

 

キーワード

■抽出　テーブルやクエリからデータを抜き出す(参照する)こと。特定のデータだけを抜き出すための条件を抽出条件という

■フィールドの結合　2つのテーブルやクエリ間に同じ目的のフィールドが存在する場合、同じ目的のフィールドであることを明示するためにつながりを設定すること

 

�B抽出したデータを集計する

　前項で作成したクエリをコントロールソースにした新しいフォームを作成し、明細金額を集計して表示するようにしてみましょう。これまでに解説したことの応用なので、操作方法の詳細は省略します。

 

1　テキストボックスに集計値を表示する

　新しいフォームを作成して、集計値を表示するためにテキストボックスを挿入します。コントロールの立体表示を「下線付き」、フォントサイズを「18」文字配置を「右」に指定し、コントロール名を「集計金額」として作成します。

 

●新しいフォームの挿入

>操作

図4.31�@[フォーム]をクリックする

　　　�A[新規作成]をクリックする

図4.32ア[フォームの新規作成]ダイアログボックスが開く

　　　�B[▼]ボタンをクリックしてテーブル一覧を表示する

　　　�C[顧客別の集計]を選択する

図4.33�D[OK]をクリックすると新しいフォームが開く

 

 

　　　

AC7H609Z　　　　　　　　　　　　　　　　　　　AC7H610Z／AC7H611Z

 

　　　　　　　　　　　　　　　　　　　　　　　　　

 

 

 

 

 

 

 

 

 

●テキストボックスの挿入

　新規のフォームを開いたとき、フィールドリストも同時に開くことがあります。以前に表示を消していると開きません。使用しない場合は、操作のじゃまにならないようにウィンドウの［×］をクリックして閉じておきましょう。必要なときはツールバーの［フィールドリスト］ボタンで表示に戻すことができます。

 

>操作

　　　ア フィールドリスト、ここでは使用しないので［×］をクリックして閉じる

図4.34�@適切な位置にテキストボックスを挿入する

図4.35　ウィザードが起動する

�Aコントロールの立体表示は[下線付き]を選択する

�Bフォントサイズは[18]を選択する

�C文字配置は[右]を選択する

�D[次へ]をクリックする

図4.36�Eテキストボックス名の入力画面になるまで[次へ]をクリックする

図4.37�Fコントロール名「集計金額」を入力する

　　　�G[完了]をクリックする

図4.38イテキストボックスが挿入される

 

 

 

 

 

 

 

 

　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　AC7H612Z

 

 

 

 

 

 

　　AC7H613Z

 

　

AC7H614Z　　　　　　　　　　　　　　　　　　　AC7H615Z

 

 

 

　

AC7H616Z

 

 

AC7H617Z

 

 

 

 

 

 

 

●ラベルだけを移動

　テキストボックスを挿入すると、同時に標準用のラベルも挿入されます。ラベルだけを別の位置へ移動させます。

 

>操作

図4.39�@移動するコントロールの左上の■にマウスポインタを合わせる(マウスポインタが手の形に変わる)

図4.40�A任意の位置へドラッグする

図4.41アマウスボタンを放したときの位置へ移動する

 

 

　　　　　　　　　　　　　　　　　

　　　　　　　　　　　　　　　　　AC7H619Z

 

 

　

AC7H620Z　　　　　　　　　　　　　　　　　　AC7H621Z

 

2◆合計を求める計算式を入力する

　フィールドのデータを合計するにはSum関数を使います。Sum関数についてはサブフォームで合計した値をフォームに参照表示させる方法として「�ASum関数で合計を求める」(268ページ)で解説しましたが、ここではフォームに設定したレコードソースのフィールドのデータを合計するので、フォームで直接合計を求めます。計算式は次のようになります。

 

＝Sum([金額])

　この計算式を、フォームに挿入したテキストボックスのプロパティ[コントロールソース]に入力します。

 

●関数の入力

　入力は、プロパティウィンドウのコントロールソースの欄に直接入力する方法とズームを使う方法などがありますが、ここではコントロールソースの欄に直接入力する方法で行うことにします。

 

>操作

図4.42　テキストボックスのプロパティウィンドウを表示する

　　　　�@コントロールソースに「＝SUM[金額]」と入力する

図4.43ア関数名を大文字で入力しても小文字混じりに変換される

図4.44�Aプロパティ[書式]を「通貨」に設定する

 

 

　AC7H622Z

 

 

　　　　　

AC7H623Z　　　　　　　　　　　　　　　　AC7H624Z

 

 

●集計結果の確認

　フォームビューに切り替えて、結果を確かめてみましょう。図4.45アのように、集計結果は、選択状態(反転表示)で表示されます。これは、コントロールを1つしか挿入していないからです。次にタイトルや顧客名を表示させるテキストボックスを挿入し、最初に選択されるコントロールをプロパティ[タブ移動順]で変更します。

 

 

AC7H625Z

 

3◆タイトルや顧客名を表示する

　集計値だけではなく、フォームの標準や集計する顧客名を明示するために、テキストボックスを挿入します。

 

>操作

図4.46　デザインビューに切り替える

　　　�@ラベルを挿入して、「顧客別　請求金額」を入力する(プロパティ[フォントサイズ]を「18」に設定)

　　　�Aテキストボックスを挿入して、コントロール名を「顧客名」にする(プロパティ[コントロールソース]を「顧客名」に、[編集ロック]を「はい」に、[タブ移動順]を「0」に設定)

図4.47アレコード数は、集計したレコード数(データの件数)を表す

 

 

 

 

 

 

AC7H626Z

AC7H627Z

 

 

フォームを保存して閉じます。フォーム名は「顧客別の集計フォーム」とします。

 

�C抽出条件に日付や期間を指定する

　データの抽出条件に日付を指定することができます。また、「いつから・いつまで」というように期間を指定することも可能です。

 

1　抽出条件に日付を指定する

　顧客別の集計クエリのフィールドリストには、フィールド[日付]があります。このフィールドに抽出条件として日付を入力して、その日のデータだけを抽出することができます。327ページの「�B抽出したデータを集計する」では顧客名だけを指定したので、これに加えて日付を指定します。指定した顧客のデータから、指定した日付のデータだけが抽出されるようになります。

　日付を表す文字列は、半角の#記号で囲んで指定します。

 

　　　　　　　　　#2003/05/10#……2003年5月10日を指定

 

　年は、西暦の4桁「2003」、またはその下2桁「03」を入力します。4桁を入力すると、自動的に2桁に変換されます(2000年は「00」、2001年は「01」となる)。月日は2桁で入力します。

　前項で作成したクエリをデザインビューで開いて、日付を指定してみましょう。次のように操作します。

 

>操作

図4.48　データベースウィンドウイでクエリ一覧を表示する

　　　�@クエリ[顧客別の集計]を選択して[デザイン]をクリックする

図4.49　クエリがデザインビューで開く

　　　�Aフィールド[発行日]の[抽出条件]欄に日付を入力する(ここでは「#2003/05/10#」を入力)

図4.50　[ビュー]ボタンをクリックしてデータシートビューに切り替えると指定した日付のデータが一覧表示される

 

顧客名も指定してあるので、顧客名(ももくり堂楽器)の指定日(2003/05/10)のデータだけが抽出されます。確認後は、保存して閉じておきます。

 

　　　　　　　

　　　　　　　AC7H628Z

 

 

 

 

 

 

 

 

 

 

AC7H629Z／AC7H630Z

　　　　　　　　　　　　　　　　　　　　　

AC7H631Z

 

●フォームでの表示を確認する

　クエリを保存して閉じ、フォームに日付ごとの集計ができていることを確かめてみましょう。

 

>操作

図4.51　データベースウィンドウでフォーム一覧を表示する

　　　�@[顧客別の集計フォーム]を選択して[開く]をクリックする

図4.52　フォームが開く

　　　ア日付指定した顧客別の集計値が表示される

　　　イ合計した件数(レコード数)

 

確認したらフォームを閉じます。

 

　　

AC7H632Z　　　　　　　　　　　　　　　　　　　　AC7H633Z

 

 

●Accessの2000年対応

　西暦年数を下2桁で入力すると、次のように21世紀に対応して解釈されます。これはAccess97およびAccess2000で有効な機能です。

　表４．１　も　トル

 

 

2◆期間を指定する

　「何日〜何日まで」といった期間を指定するには、比較演算子とAnd演算子を組み合わせて抽出条件を指定します(表4.2参照)。

 

表4．2　　Ｐ３３４と同じ

 

　　表

 

 

 

 

 

 

　たとえば、「２００３年５月1０日から２００３年５月３１日まで」を指定するには、次の条件式になります。

 

　　＞＝＃２００３／０５／１０＃　Ａｎｄ　＜＝＃２００３／０５／３１＃

　　　　　　　　　　↓　　　　　　　↓　　　　　　　↓

　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　２００３年５月３１日以前（２００３年５月３１日を含む）

　　　　　　　　　　　　　　　　　　　　　　「なおかつ」（ここでは〜「から」と解釈してもよし）

　　　　　　　　　２００３年５月１０日以降（２００３年５月１０日を含む）

 

 

●ズーム機能による条件式の入力

　長い式を抽出条件の欄に入力するときは、ズーム機能を使うと操作しやすくなります。顧客別の集計クエリをデザインビューで開いて操作します。

 

>操作

図4.53�@入力欄を右クリックしてショートカットメニューを表示する

　　　�A[ズーム]を選択する

図4.54　ズームウィンドウが開く

　　　ア[フォント]をクリックし、表示の文字サイズを変更できる

図4.55�B数式を目的の内容に書き換える

　　　�C[OK]をクリックする

図4.56イ抽出条件の欄に入力される

 

 

 

 

 

 

 

 

 

AC7H634Z

AC7H635Z

AC7H636Z

AC7H637Z

 

 

●抽出結果の確認

　データシートビューに切り替えて、指定した期間のデータが抽出されていることを確認します。図4.57アのように4月のデータが抽出されます。

　確認したら、デザインビューに戻します。フォームで期間の集計値を見るには、クエリを閉じてからフォームを開きます。

 

AC7H638Z

 

●一覧形式ののデータ表示をもっと大きな文字で表示したい

　テーブルやクエリの既定値より大きな文字で表示することができます。この操作はテーブルなどをすべて閉じた状態で行います。開いていると設定できなかったり、変更が反映されません。設定後にテーブルやクエリを開くと、指定したフォントサイズでデータが表示されます。

 

>操作

図4.58�@[ツール]→[オプション]を選択する

図4.59ア[オプション]ダイアログボックスが開く

�A[データシート]タブをクリックする

�B[サイズ]欄の[▼]ボタンをクリックして目的のサイズを指定する

�C[OK]をクリックする

 

 

AC7H639Z

 

 

AC7H640Z

 

 

●データ表示を日付順に並べ替えて表示する

　クエリの抽出結果は、データの入力されている順に表示されます。これを日付順の昇順または降順に並べ替えて表示することもできます。

 

>操作

図4.60�@[並べ替え]欄をクリックして[▼]ボタンを表示し、昇順または降順を指定する

　　　　ア 並べ替えが設定される

 

AC7H641Z

AC7H642Z

 

 

 

 

 

3◆期間の総計を求める

　現在は、顧客名の指定と期間の指定の両方の条件でレコードが抽出され、合計が求められています。このうち顧客名の指定を解除すると、期間の総計(すべての顧客の合計)を求めることができます。

　集計結果は、顧客別の集計フォームを開いて確認します。

 

>操作

図4.61　顧客別の集計クエリをデザインビューで開く

　　　�@入力してある顧客名を削除する

図4.62　データシートビューに切り替える

　　　ア指定期間のすべての顧客名の一覧が表示される

 

 

　　　　　　　　　　　　　　�@

　　　　　　　　　　　　　　　　

　　　　　　　　　　　　　　　　AC7H643Z

　　　

　　　AC7H644Z

 

　　　

　　　AC7H645Z

 

 

 

キーワード

■比較演算子　2つのデータを比較(代将るいは一致)するために使う記号

■And演算子　2つの条件をともに満たす