CD管理台帳(アクセス)の作成(その2 テーブル、クエリーの作成)

MS-Officeのアクセスによる「CD管理台帳」の作成を、「その1 概要設計」、「その2 テーブル、クエリの作成」、「その3 ユーザーインターフェイス の作成」の3回に分けてブログとしてみました。

これは「その2 テーブルとクエリの作成」のブログです。

【テーブルの設計・作成】
CDデータとして登録するものは、
「CD番号」「ジャンル」「曲名」「作曲者」「指揮者」「ソリスト」「楽団」「楽器」(ソリストが使用している楽器)「演奏年月」「製造者」「購入年月」「備考」「参考」の13項目としました。
このデータが主テーブル「主CDTB」を構成するフィールドとなります。

作成するテーブルは、「主CDTB」「主CDTB-TEMP」「CD印刷TB」のいう基本とするものの他、コンボボックスからデータ入力するため「ジャンルTB」「作曲者TB」「指揮者」「独奏者」「楽団」「楽器」という6つの補助テーブルが必要となります。
「主CDTB-TEMP」はCD登録時に使用するテンポラリーテーブルで、ここに入力したデータに間違いない場合にのみ「主CDTB」へ追加登録します。これにより登録途中の間違ったデータから「主CDTB」を隔離できます。
「CD印刷TB」は印刷用で検索抽出したレコードを保存表示し、印刷するためのテンポラリーテーブルです。
45-tabe

1.主CDTBの作成
「CD番号」「ジャンル」「曲名」「作曲者」「指揮者」「ソリスト」「楽団」「楽器」(ソリストが使用している楽器)「演奏年月」「製造者」「購入年月」「備考」「参考」の13項目がフィールドとなります。ここで「ジャンル」「作曲者」「指揮者」「ソリスト」「楽団」「楽器」は各々のテーブルとリレーションしますので、数値データです。文字データは「曲名」「備考」「参考」です。演奏や購入の年月日は日付データが望ましいと思いますが、ここでは文字データとしました。なお「備考」は作曲者や指揮者などでコンボボックスに登録されてないデータを書き込み、ここから検索できるようにします。

以下具体的に「主CDTB]テーブルの設定内容について書きます。
<「ID」>
「ID」を主キーフィールドします。オートナンバー型とし自動的に一つずつ増えます。30-tabe

<「CD番号」>
「CD番号」フィールドはCD識別用で、各CDにこの番号を表示します。
データの形は数値型で、当然小数点以下は不必要。
また「CD番号」なければレコードの意味をなさない最重要なフィールドなので「値要求」を「はい」として、未記入では登録できないようにしています。
37-tabe

<「ジャンル」>
「ジャンル」は数値型ですが、私の場合そんなに多くの分類はないので「バイト型」としています。
「ジャンル」は検索条件に入れていますので、「値要求」を「はい」として、未記入では登録できないようにしています。
38-tabe

<「作曲者」「指揮者」「独奏者」「楽団」「楽器」>
すべて同じ設定すので「作曲者」を例に記載します。
データ型は「数値型」で「整数型としました。これらのフィールドはすべて検索条件に入れていますので、「値要求」を「はい」として、未記入では登録できないようにしています。44-tabe

<「曲名」「備考」>
双方とも同じ設定です。データ型は短いテキスト(255バイト)としてます。これらの二つのフィールドも検索条件に入れていますので、「値要求」を「はい」として、未記入では登録できないようにしています。「備考」はコンボボックスに登録されてない作曲家や指揮者名を記入し、検索を可能としているフィールドです。
39-tabe

<「参考」「演奏年月」「購入年月」製造者」>
これらはテキスト型です。検索フィールではありませんの記入が無くてもOKです。「値要求」は「いいえ」です。これらはすべて「短いテキスト」で良いと思います。「参考」が長いテキストなってますが255文字もあれば十分です。また「演奏年月」「購入年月」はデータ型を「日付/時刻」とするのが正しいと思います。
41-tabe

2.主CDTB-TEMPの作成
「主CDTB-TEMP」テーブルは「主CDTB」とまったく同じに作ります。32-tabe

3.CD印刷TBの作成
「CD印刷TB」は検索抽出されたデータを印刷するためのものです。
印刷するフィールド(項目)は「CD番号」「ジャンル」「曲名」「作曲者」「指揮者」「ソリスト」「楽団」「楽器」「備考」としました。「CD番号」以外はすべて文字ですので「短いテキスト」の形にしています。
33-tabe

4.ジャンルTBなどその他のテーブルの作成
「ジャンルTB」「楽器TB」「楽団TB」「作曲者TB」「独奏者TB」はすべて同じ構造のテーブルなので、「ジャンルTB」で代表します。主キーの「ID」と「ジャンル名」という二つのフィールドで構成されています。主キー「ID」はオートナンバー型で長整数、インクリメント(自動作成)を使いました。「ジャンル名」は短いテキストです。31-tabe

34-tabe

【クエリの設計・作成】
クエリはテーブルデータを加工したり修飾するために使います。テーブルデータ検索したり、並び替えたり、異なるテーブルのデータを結合したりできます。
CD管理台帳ではクエリで選択抽出、テーブルへデータ(レコード)追加、テーブルからデータ(レコード)削除と並び替えに使っています。
コンボボックスへの登録は随時に行ってますので、そのままではコンボボックスからの登録に見落としや間違いが生じ易くなっています。これを昇順に並べるクエリが一番多く5個あります。
10-kennsaku

1.昇順並べ替えクエリの作成
「楽器昇順Q]「楽団昇順Q」「作曲者昇順Q」「指揮者昇順Q」「独奏者昇順Q]は皆同じ形ですので、「楽器昇順Q」で述べます。
「楽器TB」楽器名で昇順に並び替えるだけです。
11-kennsaku

2.削除クエリ作成
「登録中止」、「印刷中止」が削除クエリです。
それぞれ「主CDTB-TEMP」、「CD印刷TB」テーブルからすべてのレコード(データ)を削除します。
下図は「印刷中止」クエリをデザインビューで表示したものです。
テーブル「主CDTB-TEMP」で選択クエリを作り、削除クエリとします。
12-kennsaku

3.追加クエリ作成
「追加登録」と「印刷」が追加クエリです。
双方とも同じ動作ですが、「追加登録」クエリでは「主CDTB-TEMP」の全レコードを「主CDTB」に追加登録します。一方「印刷」クエリでは「検索Q」結果を「CD印刷TB」テーブルに追加します。下図は「追加登録」クエリをデザインビューで開いたものです。
13-kennsaku

4.検索Qクエリ作成
①リレーション設定
クエリ作成に先立ち(若しくは作成時)「主CDTB」と「ジャンルTB」などのリレーションを設定します。
「主CDTB」の関連するフィールド(ジャンル)と「ジャンルTB」などの「ID」とを結合させます。これによりクエリを使って、データベースは人間にわかり易い表示とすることができます(Ex.ジャンルの内容が数値ではなくテキストで表示される)。
22-リレーションシップ構造

②抽出条件作成
次に抽出条件を検索画面(フォーム)に設定した内容で検索されるように設定します。検索画面などフォームの作成は3回目になります。前後してわかり難いと思います。実際の作成ではフォームを作りながらクエリを作るなどテーブル以外は同時進行で行いました。
<「曲名」と「備考」の抽出条件式>
テキストデータなので部分一致で抽出します。また記入が無い場合は無視します。
{ Like “*”&[Forms]![CD検索]![曲名]&”*” }
1-kennsaku

<「作曲者」「指揮者」などコンボボックス値での抽出条件式>
数値なので完全一致で抽出します。空白であれば無視します。
{ IIf(IsNull([Forms]![CD検索]![楽団]),([主CDTB]![楽団]),([Forms]![CD検索]![楽団])) }
2-kennsaku

③表示フィールド設定
「主CDTB」テーブルの全データ、「ジャンルTB」など全テーブルのデータを利用可能にしておく
14-kennsaku

カテゴリー: アクセス, データベース, Office(オフィス) タグ: , , パーマリンク

コメントを残す

以下に詳細を記入するか、アイコンをクリックしてログインしてください。

WordPress.com ロゴ

WordPress.com アカウントを使ってコメントしています。 ログアウト / 変更 )

Twitter 画像

Twitter アカウントを使ってコメントしています。 ログアウト / 変更 )

Facebook の写真

Facebook アカウントを使ってコメントしています。 ログアウト / 変更 )

Google+ フォト

Google+ アカウントを使ってコメントしています。 ログアウト / 変更 )

%s と連携中