パラメータクエリ

Accessでクエリを作るとき、抽出条件に値をセットすると、その値を含むレコードだけを抽出することができます。
抽出条件に「[]」(半角角括弧)をセットするとクエリを実行したときにダイアログが表示され、値を入力することができます。このような使い方をパラメータクエリと言います。
括弧内に文字を入れるとそれが表示されます。例えば「[商品コードを入力]」とするとダイアログ内に「商品コードを入力」と表示されます。

Accessで「循環参照」が発生したら

Accessのクエリを使うと、通常はフィールド名がそのまま表示されますが、別名を付けることができます。「別名: フィールド名」のようにコロンを使います。
しかし、別名を元のテーブルのフィールド名と同じにしようとすると次のようなエラーが出ます。

クエリ定義のSELECTで指定されている別名'xxxxx'が循環参照を発生させています。

別の名前にすればいいのですが、どうしても同じにしたい場合はどうすればいいでしょうか。

次のようなテーブル「yourtable」があったとします。

id name height
1 First Smith 175.1
2 Second Smith 176.2

「クエリを使ってheightを四捨五入したい。フィールド名はheightにしたい。」というような場合を考えます。

「height: Round([height])」とすると「クエリ定義のSELECTで指定されている別名'height'が循環参照を発生させています。」とメッセージが出ます。
フィールド名の「height」と別名の「height」が重複しているということです。

この場合はフィールド名にテーブル名を付加します。
「height: Round([yourtable].[height])」のようにします。
実行すると次のようになります。

id name height
1 First Smith 175
2 Second Smith 176

値は四捨五入されています。一方、フィールド名は別名の「height」になっています。
SQLだと次のようになります。

SELECT yourtable.id, yourtable.name, Round([yourtable].[height]) AS height
FROM yourtable;

SQLでデータの不一致を探す

次のようなテーブル「height」があったとします。名前と身長のデータです。
Suzukiが二つ入力されています。データが同じならば問題はないかもしれませんが、身長が異なります。
SQLを使って、このようなデータの不一致を探す方法を紹介します。Access2007で確認しました。

id namae takasa
1 Suzuki 180
2 Tanaka 175
3 Takahashi 170
4 Suzuki 185

最大値と最小値の差を使う

サブクエリを使います。
二つ以上レコードがあって異なる身長が入力されているということは最大値と最小値に差があるということです。
まずnamae毎のtakasaの最大値と最小値を求めます。

SELECT namae, Max(takasa) AS maxtakasa, Min(takasa) AS mintakasa
FROM height
GROUP BY namae;

この結果を利用し、最大値と最小値の差がゼロより大きいレコードを抽出します。
上のSQLをFROM句に使います。セミコロンは削除します。

SELECT namae
FROM(
    SELECT namae, Max(takasa) AS maxtakasa, Min(takasa) AS mintakasa
    FROM height
    GROUP BY namae
)
WHERE maxtakasa - mintakasa > 0;

分散を使う

異なる身長が入力されているということは分散がゼロより大きいということです。
分散は、平均との差の二乗の平均なので、異なる値があれば必ずゼロより大きい値になります。

SELECT namae
FROM height
GROUP BY namae
HAVING Var(takasa)>0;

Accessのクエリを他人に渡す方法

データベースから別のテータベースにクエリをコピーするには、コピー&ペーストでできますが、他人にクエリを渡すのは意外に面倒です。
データベースそのものをメールに添付して送信する方法がありますが、ここではSQLそのものを送信する方法を書きます。
SQLはクエリのデザインを文字列で表現したものですので、メールの本文に書いて送信することができます。

受け渡す側

  1. 渡したいクエリを開く。
  2. メニューの ホーム-表示-表示-SQLビュー を選択。
  3. SQLが表示される。これをコピーしてメール等で送信する。

受け取る側

  1. メニューの 作成-その他-クエリデザイン を選択。
  2. 「テーブルの表示」ダイアログが表示されたら「閉じる」をクリック。
  3. メニューの デザイン-結果-表示-SQLビュー を選択。
  4. 「SELECT;」と表示されている部分に、コピーしたSQLを貼り付ける(右クリックして 貼り付け など)。
できるポケット 仕事に使えるAccessクエリの便利ワザがマスターできる本 改訂版 2007/2003/2002対応

できるポケット 仕事に使えるAccessクエリの便利ワザがマスターできる本 改訂版 2007/2003/2002対応

SQLで重複を除いてカウントする

Accessで次のようなテーブル「member」があったとします。

id name roomid weight date
1 Ito 1 60 2013/1/1
2 Abe 1 62 2013/1/1
3 Sato 2 61 2013/1/1
4 Eda 2 68 2013/1/1
5 Ito 1 65 2013/7/1

ある学校の体重の管理簿だと考えてください。
roomidが教室番号です。weightが体重で、dateがそれを量った日です。

このテーブルを使って教室毎の人数を数えてください。

単純に数えるには次のようになります。 (さらに…)

SQLで順位を付ける方法

次のようなテーブル「member」があったとします。

id name height
1 Ochiai 179
2 Tanaka 172
3 Sato 177
4 Suzuki 176
5 Inoue 175
6 Ueno 179
7 Endo 178
8 Ito 180
9 Yamamoto 175
10 Aoki 170

これに身長(height)の高い順に順位を付けます。Access2007で確認しました。

SQLは次の通りです。

SELECT (
    SELECT Count(member.id)
    FROM member
    WHERE member.height > member1.height
    ) + 1 AS rank, member.name, member.height
FROM member , member AS member1
WHERE member.id = member1.id
ORDER BY member.height DESC;

「member AS member1」として同じテーブル「member1」を作ります。
「SELECT Count(member.id) FROM member WHERE member.height > member1.height」として、自分より身長の高い者の数を数えています。これに1を加えることで順位になります。自分より身長の高い者がいない場合は0で、これに1を加えて「1」、一人いる場合は1で、これに1を加えて「2」となります。

結果は次の通りです。

rank name height
1 Ito 180
2 Ueno 179
2 Ochiai 179
4 Endo 178
5 Sato 177
6 Suzuki 176
7 Yamamoto 175
7 Inoue 175
9 Tanaka 172
10 Aoki 170

同じ身長の場合、同じ順位になっています。

ExcelからAccessのデータを利用する方法

ExcelのワークシートにAccessのデータベースのテーブルを取り込む方法です。
単なるインポートではなく、リンクなので、更新すればデータベースと同じ状態になります。
Excel2007とAccess2007で確認しました。

取り込み

  1. データ-外部データの取り込み-Accessデータベースを選択。
  2. 「データファイルの選択」ダイアログでデータベース(mdbなど)を選択して開く。
  3. 「テーブルの選択」ダイアログでテーブルを選択してOK。
  4. 「データのインポート」ダイアログで、テーブル、既存のワークシートまたは新規ワークシートを選択してOK。新規ワークシートを選択したときは、新しいワークシートが挿入され、A1セルを左上にした状態で取り込みます。

更新

Access側でデータの変更をした場合、Excel側でデータの更新をしないと変更が反映されません。
更新は次の通りです。

  1. データ-接続-すべて更新-更新

テーブル上で右クリック-更新でも同じです。

自動的に更新

Excel側の更新を忘れると致命的なことになるかもしれません。
Excelファイルを開いたときに自動的に更新される方法です。

  1. テーブルツール-デザイン-外部のテーブルデータ-更新-接続のプロパティを選択。
  2. 「接続のプロパティ」ダイアログの「使用」タブでファイルを開くときにデータを更新するにチェックしてOK。

Accessで経過時間を計算する

Accessで次のようなテーブルがあったとします。(テーブル名:tabletime)

開始時刻 終了時刻
8:45:00 9:00:00
9:00:00 12:00:00
9:30:00 11:20:00

ここで次のような問題を考えます。

開始時刻から終了時刻までの経過時間を求めよ。ただし1時間単位とし1時間未満の端数は切り上げる。

(さらに…)

Accessのナビゲーションウィンドウでテーブルやクエリを整理する

Access2007ではテーブルやクエリをナビゲーションウィンドウで操作します。
Accessだけでなく、ExcelやWordなど、今までにないユーザーインターフェースです。
使い勝手が分からないので、初期設定のままにしておくと、テーブルやクエリが縦にずらっと並んで探しにくくなります。
そんなときは、ユーザー設定でグループ化することで探しやすくすることができます。
使用目的に関連するテーブルやクエリをグループ化します。 (さらに…)

AccessやExcelで「?」や「*」を検索する方法

Accessで半角の「?」(クエスチョンマーク)を検索するにはどうしたらよいでしょうか。
実はAccessで検索する場合に「?」を使うと「任意の1文字」を表す、いわゆるワイルドカードになってしまいます。そのため、普通に検索すると、すべての文字がヒットしてしまい、使えません。
「?」という文字そのものを検索するには、角括弧で挟み「[?]」を検索する文字列に指定します。
「*」(アスタリスク)もワイルドカードになってしまうので、「[*]」を使います。

Excelの場合もワイルドカードになってしまいますので、工夫が要りますが、方法が違います。
半角チルダを使い、「˜?」「˜*」とします。

Wordの場合は、ワイルドカードとして使用しなければ、通常通り検索できます。 (さらに…)

古い記事

タグ

カテゴリー

最近の投稿

最近のコメント

固定ページ

アーカイブ

stabucky

写真

メタ情報