Access VBA質問箱 IV

当質問箱は、有志のボランティア精神のおかげで成り立っています。
問題が解決したら、必ずお礼をしましょうね。
本サイトの基本方針をまとめました。こちら をご一読ください。

投稿種別の選択が必要です。ご注意ください。
迷惑投稿防止のため、URLの入力を制限しています。ご了承ください。


789 / 2272 ツリー ←次へ | 前へ→

【10106】集計方法について あおぎん 07/12/18(火) 16:51 質問[未読]
【10108】Re:集計方法について 小僧 07/12/19(水) 9:58 回答[未読]
【10110】Re:集計方法について あおぎん 07/12/19(水) 16:22 質問[未読]
【10111】Re:集計方法について 小僧 07/12/20(木) 13:27 回答[未読]
【10113】Re:集計方法について あおぎん 07/12/21(金) 15:12 お礼[未読]
【10112】Re:集計方法について 小僧 07/12/20(木) 13:30 発言[未読]
【10114】Re:集計方法について あおぎん 07/12/21(金) 17:50 お礼[未読]

【10106】集計方法について
質問  あおぎん  - 07/12/18(火) 16:51 -

引用なし
パスワード
   いつもお世話になっています。

社員の健康診断を外部に委託しており、その結果が下記のようなデータで届くのですが

T_結果
所属コード社員コード 氏名 性別  年代 コース BMI判定 肝機能 ・・・    
1      1     あ   男   40  B   肥満   要精密検査  
1      2     い   女   30  B   標準   要精密検査  
2      3     う   男   30  A   やせぎみ 異常なし


※判定項目は、以下 脂質、糖代謝、胃部X線など15項目ほど。
※BMI判定以外は 異常なし、観察、要経過観察、要精密検査、要治療、治療中、要再検査 のどれかが判定として入っています。

このデータをもとに、各判定項目で「要精密検査」判定が出た人に対して、医師への紹介状を作成する必要があります。
そこで、ユニオンクエリで各判定項目を種別として表示させ、判定=”要精密検査”で抽出した「Q_紹介状のもと」により R_紹介状 を作成しています。
(社員コードでグループ化し、各社員1枚ずつ紹介状が出るようにしています。)

Q_紹介状のもと
社員コード  種別  判定
1   肝機能  要精密検査
2   肝機能  要精密検査
2   糖代謝  要精密検査
3   胃部X線 要精密検査
3   脂質   要精密検査

ここで、社員ごとに 要精密検査が必要な種別を表示させる必要もあるのですが・・
レポートの 詳細セクション に 種別テキストを配置すればとりあえず表示されるのですが、配置の都合やスペースの都合(要精密検査が4項目あると1枚に収まりきらない)があって、できれば、次のようなデータを取得できたらなと思っています。

社員コード 項目1 項目2
1     肝機能
2     肝機能 糖代謝
3     胃部X線 脂質

今の私の知識だと
Q_紹介状のもと で
社員コード 種別  判定    番号
1   肝機能  要精密検査   1
2   肝機能  要精密検査   1
2   糖代謝  要精密検査   2
3   胃部X線 要精密検査   1
3   脂質   要精密検査   2

こんな感じで、社員コードごとに番号を付けることが出来たら、クロス集計で目的のデータが取得できそうなのですが・・。

データの集計方法もしくは、別の方法で目的のレポートを完成できる方法がありましたらご教示をお願いします。

(Win ・Access ともに2000)

【10108】Re:集計方法について
回答  小僧  - 07/12/19(水) 9:58 -

引用なし
パスワード
   ▼あおぎん さん:
こんにちは。


>Q_紹介状のもと で
>社員コード 種別  判定    番号
>1   肝機能  要精密検査   1
>2   肝機能  要精密検査   1
>2   糖代謝  要精密検査   2
>3   胃部X線 要精密検査   1
>3   脂質   要精密検査   2
>
> こんな感じで、社員コードごとに番号を付けることが出来たら、
> クロス集計で目的のデータが取得できそうなのですが・・。

SELECT Q_紹介状のもと.社員コード,
    Q_紹介状のもと.種別,
    Q_紹介状のもと.判定,
    "番号_" & DCount("*","Q_紹介状のもと","社員コード=" & [社員コード] & " AND 種別 >='" & [種別] & "'") AS 番号
FROM Q_紹介状のもと;

のような SQL で結果を求める事は可能です。


ですが、元々の Q_紹介状のもと 自体が
ユニオンクエリを使っていらっしゃる事、
クエリの中に定義域集計関数(今回は DCount関数) を使うことによって
速度面でのパフォーマンスは期待できないかもしれません。
(お使いのPCの性能にもよりますが…)

件数が少なければ問題はないのですが、
もし大量な件数の中から何枚ものレポートを発行しなければいけない場合は
抽出条件が変わる度に何回も再計算しなければいけない為、
クエリでなく、いったんワークテーブルに格納する方が良いかもしれませんね。


※クロス集計クエリを使った際、フィールド名が「1」「2」のようですと
 不安定な動作になる事がありますので、
 「番号_1」「番号_2」の様になる様に修正しております。

【10110】Re:集計方法について
質問  あおぎん  - 07/12/19(水) 16:22 -

引用なし
パスワード
   ▼小僧 さん:
こんにちは。
いつもお世話になっています。
回答ありがとうございます。


>SELECT Q_紹介状のもと.社員コード,
>    Q_紹介状のもと.種別,
>    Q_紹介状のもと.判定,
>    "番号_" & DCount("*","Q_紹介状のもと","社員コード=" & [社員コード] & " AND 種別 >='" & [種別] & "'") AS 番号
>FROM Q_紹介状のもと;
>
>のような SQL で結果を求める事は可能です。

さっそくやってみましたら、無事データは取得できました。ありがとうございます。
出来たのに、質問なのですが、これは Q_紹介状のもと をもとに別クエリを作成するということですよね?
Q_紹介状のもと を作るついでに番号を付けられるのかと思ってそちらで記述したら、カウントはされるんですが、番号_12と番号_5という謎な数値を取得しました^^;。できそうなのにだめなんですね。


>ですが、元々の Q_紹介状のもと 自体が
>ユニオンクエリを使っていらっしゃる事、
>クエリの中に定義域集計関数(今回は DCount関数) を使うことによって
>速度面でのパフォーマンスは期待できないかもしれません。
>(お使いのPCの性能にもよりますが…)
>
>件数が少なければ問題はないのですが、
>もし大量な件数の中から何枚ものレポートを発行しなければいけない場合は
>抽出条件が変わる度に何回も再計算しなければいけない為、
>クエリでなく、いったんワークテーブルに格納する方が良いかもしれませんね。

おっしゃるとおり、かなり動きが悪いです。
一気に表示されず、上から、だららららら〜(表現わかりにくいですね^^;)って感じで段階的に表示されます。
ワークテーブルに格納する方がいいのかもしれません。

とはいえ、ワークテーブルを利用する手法に不慣れなので再度質問を・・。

ワークテーブルを作成する場合

フォームにコマンドボタンを配置して テーブル作成クエリを走らせようと思ってます。
で、作成したテーブルを元にした選択クエリでR_紹介状を作成するという手順でよいのですよね?

で、

SELECT Q_紹介状のもと.社員コード,Q_紹介状のもと.種別,・・・"番号_" & DCount("*","Q_紹介状のもと","社員コード=" & [社員コード] & " AND 種別 >='" & [種別] & "'") AS 番号  INTO WT_紹介状用 FROM Q_紹介状のもと

こんな感じのSQLを書いて走らせてみたのですが、
取得する番号が変になりました。
番号_12とか番号_5とか不正な数値を取得しているみたいです。
テーブル作成時に番号を取得せずに、番号なしのワークテーブルを作って、そこからクエリで番号を取得する方法じゃないとだめなのでしょうか?

できればあまりクエリを増やしたくないなーと思いまして・・。
わけがわからなくなってくるので。

それと関連して質問なのですが、テーブル作成クエリ等はVBAにコードをかけば走らせることが出来ますよね?
それに対して、選択クエリを作成してそれをレポートソースにセットしてレポートを開くというようなことをVBAでやった場合は、クエリの実体が必要ですよね?(わかりにくくてすみません。)
つまり、できるだけ整理に努めてますが、それでもクエリがごちゃごちゃしているので、上記のような例でもクエリの実体(?)を作らずにすませられないのかなーと思って質問してみました。


長々とすみません^^;

ワークテーブル作成手順でこういう手順のほうが適正というようなことがありましたら、教えていただけると嬉しいです。
テーブルの構成方法やクエリの作り方とか、ある程度勉強はしてみたものの中々うまく作れません。
かなり無駄なクエリを作ってるような気がします・・・。

よろしくお願いします。

【10111】Re:集計方法について
回答  小僧  - 07/12/20(木) 13:27 -

引用なし
パスワード
   ▼あおぎん さん:
こんにちは。

> 番号_12と番号_5という謎な数値を取得しました^^;。

肝機能、糖代謝、脂質 とどれが一番大きいのか、パッと見は解りませんが、

  Debug.Print "肝機能" < "糖代謝" ⇒ True
  Debug.Print "肝機能" < "脂質"  ⇒ True
  Debug.Print "脂質" < "糖代謝"  ⇒ True

の結果より、肝機能 < 糖代謝 < 脂質 の順に大きいと内部的に判断されています。
仮にこの値を 肝機能=1、糖代謝=2、脂質=3 とします。


社員コード  種別
1      肝機能(1)
2      脂質(3)
1      糖代謝(2)
1      脂質(3)

というデータがあると
>> DCount("*","Q_紹介状のもと","社員コード=" & [社員コード] & " AND 種別 >='" & [種別] & "'")

上記のカウントは

・社員コードが同一であって
・種別が小さいもの

をカウントしている結果


社員コード  種別 番号
1      1   社員コード:1 で 種別:1以上の数 …3個
2      3   社員コード:2 で 種別:3以上の数 …1個
1      2   社員コード:1 で 種別:2以上の数 …2個
1      3   社員コード:1 で 種別:3以上の数 …1個

のような考え方です。


> Q_紹介状のもと を作るついでに番号を付けられるのかと思って

番号_12と番号_5 という様な数字と取ってきたという事は
条件がもっと複雑になってるはずですね。


> 判定=”要精密検査”で抽出した

とあるので、漠然としか解りませんが、

DCount("*","元クエリ","社員コード=" & [社員コード] & " AND 種別 >='" & [種別] & "' AND 判定 = '要精密検査'")

の様になるかと思われます。


以下長くなるのでスレッドを分けますね。

【10112】Re:集計方法について
発言  小僧  - 07/12/20(木) 13:30 -

引用なし
パスワード
   ▼あおぎん さん:
長くなったので以下に分けます。


ワークテーブル、クエリについて
いくつか疑問やご質問をされていましたが、
その前に少し説明させて下さい。


ツールバーの [ツール] ⇒ [オプション] ⇒ [表示]タブに

システムオブジェクト というチェックボックスがあるので、
それにチェックをつけて、データベースウィンドウのテーブル欄をご覧になって下さい。
MSys〜 というテーブルが出現したかと思われます。

その中のMSysObjects というテーブルを開いてみると
各種オブジェクトが格納されています。

Name と Type から色々推測できると思いますが、
例えば Type=1 はテーブル、Type=-32768 はフォームのようですね。

さて、Type=5 に関してなのですが、
通常のクエリの他 ~TMP から始まるものがありませんでしょうか。

クエリの実態として存在していなくても
例えばコンボボックスの値集合ソースなどにSQL文を選択すると
Access上では名前をつけてクエリとして管理しています。


VBAにてアクションクエリやテーブル定義クエリを発行する分には
こちらに登録される事はないかと思われます。


またもう1点、はっきりと確証がある話ではないので申し訳ないのですが
大量のデータを持つテーブルを削除してもMDBの容量があまり変わらず、
最適化をするとファイルサイズが小さくなる事を体験された事はありませんか?

こちらについてはユーザには見えない「削除FLG」みたいなものが存在し
MDBの最適化をするタイミングで削除FLGの立っているレコードを
消去しているのではないかと思われます。


> テーブル作成クエリを走らせようと思ってます。

この様な仕様ですと、R_紹介状 が開かれるたびに
同じ名前のテーブルが削除 ⇒ 作成される事になり
また上記の推測から最適化されていない状態ですと
同一のName、Typeを持つレコードが
システムテーブル:MsysObjects上にいくつも存在する事になります。

これが原因なのかははっきりとは解りませんが
同一の名前のオブジェクトを何回も作ったり消したりしていると
MDBファイル自体の破損に繋がる可能性が高いです。


今回の様な場合は R_紹介状 を開くたびに
ワークテーブルのフィールド名が変わる事はなさそうですので
あらかじめ WT_紹介状用 を用意しておき、

WT_紹介状用 の 全レコード削除 ⇒ 必要なデータの挿入

の方が安定した運用ができるかと思われます。


番号以外のデータをいったん WT_紹介状用 に格納し、
(その際にオートナンバーでも良いので主キーをかならず設定して下さい)

社員コードでソートしたレコードセットを Loop で回しながら
番号を更新していく方法もあります。


> できればあまりクエリを増やしたくないなーと思いまして・・。
> わけがわからなくなってくるので。

今回、便宜上 SQL文の中でクエリ名を指定してしまいましたが、
これもクエリのパフォーマンスを落とす原因になってるかもしれません。

MSサポートオンライン
Access 2000 でクエリを最適化する方法
h tp://support.microsoft.com/kb/209126/ja

正直、難しいことも多々書いてありますが
「クエリのパフォーマンスを改善するためのヒント」 あたりが
ご参考になるかと思われます。

【10113】Re:集計方法について
お礼  あおぎん  - 07/12/21(金) 15:12 -

引用なし
パスワード
   ▼小僧 さん:
こんにちは。
返答が遅れて申し訳ありません。


>番号_12と番号_5 という様な数字と取ってきたという事は
>条件がもっと複雑になってるはずですね。

Q_紹介状のもと は、ユニオンクエリから、判定="要精密検査"で抽出したものですが、それ以外に「受診日」などでもう2つくらい条件をつけてます。
こちらでは長くなるのではしょってましたが、きちんと提示しないと、せっかく回答いただいても結果がでないのですね。気をつけます。

>
>> 判定=”要精密検査”で抽出した
>
>とあるので、漠然としか解りませんが、
>
>DCount("*","元クエリ","社員コード=" & [社員コード] & " AND 種別 >='" & [種別] & "' AND 判定 = '要精密検査'")
>
>の様になるかと思われます。

ご提示のように、条件を付け加えたらきちんとした数値が取得できました。

実はそもそも 
条件の  種別 >='" & [種別] & "'" のところが今ひとつ理解できてませんでしたが、小僧さんの説明でよくわかりました。

丁寧なご説明ほんとうにありがとうございました!

【10114】Re:集計方法について
お礼  あおぎん  - 07/12/21(金) 17:50 -

引用なし
パスワード
   ▼小僧 さん:

こちらにも丁寧なご回答ありがとうございます。
>ワークテーブル、クエリについて
>いくつか疑問やご質問をされていましたが、
>その前に少し説明させて下さい。
>
>
>ツールバーの [ツール] ⇒ [オプション] ⇒ [表示]タブに
>
>システムオブジェクト というチェックボックスがあるので、
>それにチェックをつけて、データベースウィンドウのテーブル欄をご覧になって下さい。
>MSys〜 というテーブルが出現したかと思われます。
>
>その中のMSysObjects というテーブルを開いてみると
>各種オブジェクトが格納されています。
>
>Name と Type から色々推測できると思いますが、
>例えば Type=1 はテーブル、Type=-32768 はフォームのようですね。
>
>さて、Type=5 に関してなのですが、
>通常のクエリの他 ~TMP から始まるものがありませんでしょうか。
>
>クエリの実態として存在していなくても
>例えばコンボボックスの値集合ソースなどにSQL文を選択すると
>Access上では名前をつけてクエリとして管理しています。
>
>
>VBAにてアクションクエリやテーブル定義クエリを発行する分には
>こちらに登録される事はないかと思われます。

ご提示通りやってみました。
~sq_cから始まるものは多かったのですが、 ~TMP はないようです。コンボボックスでSQLは使用しているのですが・・。

でも以前作ったデータベースからコードやクエリをコピーして変更し利用したりが多いせいか、このデータベースにはありえない名前のクエリがあったりします。。
きちんと作らないとだめですねー。


>
>またもう1点、はっきりと確証がある話ではないので申し訳ないのですが
>大量のデータを持つテーブルを削除してもMDBの容量があまり変わらず、
>最適化をするとファイルサイズが小さくなる事を体験された事はありませんか?
>
>こちらについてはユーザには見えない「削除FLG」みたいなものが存在し
>MDBの最適化をするタイミングで削除FLGの立っているレコードを
>消去しているのではないかと思われます。
>> テーブル作成クエリを走らせようと思ってます。
>
>この様な仕様ですと、R_紹介状 が開かれるたびに
>同じ名前のテーブルが削除 ⇒ 作成される事になり
>また上記の推測から最適化されていない状態ですと
>同一のName、Typeを持つレコードが
>システムテーブル:MsysObjects上にいくつも存在する事になります。
>
>これが原因なのかははっきりとは解りませんが
>同一の名前のオブジェクトを何回も作ったり消したりしていると
>MDBファイル自体の破損に繋がる可能性が高いです。

よく破損するのですが、ここらへんが原因でしょうか・・。

>
>
>今回の様な場合は R_紹介状 を開くたびに
>ワークテーブルのフィールド名が変わる事はなさそうですので
>あらかじめ WT_紹介状用 を用意しておき、
>
>WT_紹介状用 の 全レコード削除 ⇒ 必要なデータの挿入
>
>の方が安定した運用ができるかと思われます。

アドバイス参考にします!

>
>
>番号以外のデータをいったん WT_紹介状用 に格納し、
>(その際にオートナンバーでも良いので主キーをかならず設定して下さい)
>
>社員コードでソートしたレコードセットを Loop で回しながら
>番号を更新していく方法もあります。

Loop・・・これはまだ使ったことのないテクニックです。
勉強しなくては・・。

>
>> できればあまりクエリを増やしたくないなーと思いまして・・。
>> わけがわからなくなってくるので。
>
>今回、便宜上 SQL文の中でクエリ名を指定してしまいましたが、
>これもクエリのパフォーマンスを落とす原因になってるかもしれません。
>
>MSサポートオンライン
>Access 2000 でクエリを最適化する方法
>h tp://support.microsoft.com/kb/209126/ja
>
>正直、難しいことも多々書いてありますが
>「クエリのパフォーマンスを改善するためのヒント」 あたりが
>ご参考になるかと思われます。


いろいろと教えていただきありがとうございます。
参考にします。
って、む、難しいのですか・・!?
ちょっと理解力に難ありのわたしですが・・。がんばります。

789 / 2272 ツリー ←次へ | 前へ→
ページ:  ┃  記事番号:
1078201
(SS)C-BOARD v3.8 is Free