Accessで縦に情報が展開されてるテーブルの処理

質問

作業環境は以下の通りで、Accessの知識は初心者です。
OS：Windws7/使用ソフト：Access2003

やりたい事はこれに近いのですが、もう一ひねりある為困っています。
http://okwave.jp/qa/q2452277.html?from=recommend

テーブルに以下のようなデータがあります。
薬剤/配番/効果/使用方法
A剤/00001/頭痛/1回1錠
A剤/00002/発熱/食後…(長文)
B剤/00001/頭痛/1回2錠
B剤/00003/裂傷/清潔な手で…(長文)

これを薬剤名ごとに、効果(数は120を超えます)と使用方法を横へ並べる事はできますか？

この投稿のマルチメディアは削除されているためご覧いただけません。

回答No.12

30246kiku · ベストアンサー

＃９です

> 実行時エラー'94':
> Nullの使い方が不正です
>
> デバックボタンを押してみると55行目の↓この部分が黄色く反転されました。
> sA(i * 2 - 1) = rs("効果")

おそらく、「効果」にデータが無くて Null になっていると思われます。
※ データ/テーブル内容からすると、何かしら設定されているものと思っていたんですが・・・

対処方法は２つあって、

１）代入する時に Nz 関数を使って Null を ""（空文字）に置換える

>　　　　sA(i * 2 - 1) = rs("効果")
>　　　　sA(i * 2) = rs("使用方法")
↓
　　　　sA(i * 2 - 1) = Nz(rs("効果"))
　　　　sA(i * 2) = Nz(rs("使用方法"))

ただ、これには問題がありそう
というのは、両方値が入っていなければ良いと思いますが、
どちらか一方だけに値が設定されていたら・・・　変な内容に？

２）必ず「効果」「使用方法」両方に値が設定されているものを対象にする

>　　Const CSQL1 As String = "SELECT DISTINCT 配番 FROM {%1};"
>　　Const CSQL2 As String = "SELECT * FROM {%1} ORDER BY 薬剤;"

これで抽出しているわけですが、
条件として「効果」「使用方法」とも設定されているもの・・・ を、追加します。

　Const CSQL1 As String = "SELECT DISTINCT 配番 FROM {%1} " _
　　　& "WHERE 効果 Is Not Null AND 使用方法 Is Not Null;"
　Const CSQL2 As String = "SELECT * FROM {%1} " _
　　　& "WHERE 効果 Is Not Null AND 使用方法 Is Not Null ORDER BY 薬剤;"

これに変更すると、１）での変更は不要です。
けど、念のために１）も適用しておいた方が良いかも

この修正でどうなりますでしょうか

お礼

回答ありがとうございます。

> おそらく、「効果」にデータが無くて Null になっていると思われます。
確認してみたところ確かにNull値がありました。

選択クエリで効果と使用方法と両方の3タイプにIs Nullで抽出をかけたところ、効果のみNull値が存在するようです。
その為、２）に変更してみたところ、希望通り横に展開されました！！

ありがとうございます。

処理の解説も頂いていましたので、落ち着いたら本格的にAccessの勉強を始めたいと思います。
(排出されたCSVをもう一加工する為に他のソフトとの連携設定を考えなくてはならないのです…)

回答No.4

NotFound404

追伸、CSV出力を考えるとクエリ二個は以下に差し替えた方が良さそうです。

Q効果は
TRANSFORM Iif(Isnull(First(Tbl.[効果])),'',First(Tbl.[効果])) AS 効果の先頭
SELECT Tbl.[薬剤]
FROM Tbl
GROUP BY Tbl.[薬剤]
PIVOT '効果' & Tbl.[配番];

Q使用方法は
TRANSFORM Iif(Isnull(First(Tbl.[使用方法])),'',First(Tbl.[使用方法])) AS 使用方法の先頭
SELECT Tbl.[薬剤]
FROM Tbl
GROUP BY Tbl.[薬剤]
PIVOT '使用方法' & Tbl.[配番];

お礼

回答ありがとうございます。

テスト環境で試したところ、うまくいきました！

ただ、本番環境で試したところ、やはりフィールド数の多さからか(247ありました)
「このフォームまたはレポートは、1つのレコードのデータの制限を超えるクエリを基にしています」
と小窓が開き、表示されませんでした。

Q効果、Q使用方法、Q薬剤については問題なく表示されます。

今はまだ、このSQL文がどんな命令をしてるか分からないまま入力したのですが、時間を作って理解を深めようと思います。
わざわざ考えて頂いてありがとうございました。

回答No.3

NotFound404

>とりあえずチャレンジしてみたいので、このように両方保存いたしました。
両クエリは問題なかったのでしょうか検証済み？

>正確な数は122×2の為、255は超えないようです。
では余計な心配はしないことにします。

クエリをさらにもう一個、SQL文は
SELECT DISTINCT 薬剤 FROM Tbl;
これは、名前を Q薬剤 にして保存。

これが最後のクエリ
Q薬剤、Q効果、Q使用方法を新規クエリのデザインウィンドウにドラッグ、
Q薬剤とQ効果の薬剤同士を結合、
Q薬剤とQ使用方法の薬剤同士を結合、
Q薬剤の薬剤をデザイングリッドにドラッグ、
Q効果の効果1、使用方法1、効果2、使用方法2、効果3、使用方法3、・・・・、使用方法122
と交互にドラッグします。
データシートビューに切り替えて確認してみてください。
OKだったら、名前は適当に保存。
あとはリボンのエクスポートからテキストファイルを選択し
ウィザードに従ってこのクエリをエクスポートしてみてください。

回答No.2

NotFound404

薬剤/配番/効果/使用方法
A剤/00001/頭痛/1回1錠
A剤/00002/発熱/食後…(長文)
B剤/00001/頭痛/1回2錠
B剤/00003/裂傷/清潔な手で…(長文)
を
薬剤/効果１/使用方法１/効果２/使用方法２　/効果３/使用方法３　　　　/・・・
A剤/　頭痛/　　1回1錠/　発熱 /食後…(長文)/
B剤/　頭痛/　　1回2錠/　　　 /　　　　　　/裂傷　/清潔な手で…(長文)/・・・
のようなイメージ？にしたいということでしょうか？
現時点で
>数は120を超えます（120×2）
なのでAccessの扱えるフィールド数は最大でも255以下の制限に間もなく引っかかりそうです。
【崖っぷち状態です】
制限を超える可能性があるのでは？

とりあえず、クロス集計までは、
テーブル名を、Tbl とします。
新規クエリをデザインビューで開いて、
『テーブルの表示』の小窓は閉じます。
右クリックすると「SQLビュー」がリストにありますので選択、
SELECT: って書いてあるので消去し
そこへ下記をコピペ
TRANSFORM First(Tbl.[効果]) AS 使用方法の先頭
SELECT Tbl.[薬剤]
FROM Tbl
GROUP BY Tbl.[薬剤]
PIVOT '効果' & Tbl.[配番];

データシートビューに切り替えると
薬剤　効果1　　　効果2　　　効果3
A剤　　頭痛 　　　発熱
B剤　　頭痛　　　　　　　　　裂傷
となります。このクエリを、Q効果 とでも命名して保存。

もう一個同様に、
TRANSFORM First(Tbl.[使用方法]) AS 使用方法の先頭
SELECT Tbl.[薬剤]
FROM Tbl
GROUP BY Tbl.[薬剤]
PIVOT '使用方法' & Tbl.[配番];
で
薬剤　使用方法1　使用方法2　使用方法3
A剤　　　1回1錠　　　食後
B剤　　　1回2錠　　　　　　　　清潔な手
になります。これは、Q使用方法 とでも保存。
（使用方法フィールドがメモ型だと厄介かも）

さて、ここからが前述の255の制限を超える可能性の有無で分かれます。
CSVのフォーマットの具体例もないので
とりあえず、ここまで。

余談ですが
また、めでたくCSVが完成したとしてアップロードして
使ってみたら「使いにくいので再考」になったら・・と考えてしまいます。。。

お礼

回答ありがとうございます。

薬剤/効果１/使用方法１/効果２/使用方法２　/効果３/使用方法３　　　　/・・・
A剤/　頭痛/　　1回1錠/　発熱 /食後…(長文)/
B剤/　頭痛/　　1回2錠/　　　 /　　　　　　/裂傷　/清潔な手で…(長文)/・・・
のようなイメージ？にしたいということでしょうか？

その通りです。

使用方法フィールドはメモ型です。
120×2…単純に×2になる事を理解していませんでした

> Q効果 とでも命名して保存。
> Q使用方法 とでも保存。

とりあえずチャレンジしてみたいので、このように両方保存いたしました。
正確な数は122×2の為、255は超えないようです。

> 使ってみたら「使いにくいので再考」になったら・・と考えてしまいます。。。

データを抜き出したいだけなので問題ないハズ…です。

回答No.1

panacon

lotus2014さんへ

　見せるだけで良いのなら、薬剤と配番と効果でクロス集計を作って、そのクエリに使用方法を連結するクエリを作ったものをソースにしたフォームで良いと思います。
　クロス集計は、効果を横に配置する形で、データ領域に配番を選択して、配番の集計方法を先頭としておけばOKです。
　他のクエリで、薬剤と使用方法のみをグループ化したものと、配番と効果のみをグループ化したものを作っておいて、先のクエリと薬剤、配番で連結すれば、薬剤＋配番ｎ個＋効果ｎ個＋使用方法のクエリを作れます。

　入力を考えているとしたら、薬剤と使用方法をメインフォームにして、配番と効果をサブフォームにするようにテーブルに変更すれば良く、そこからの横並びは、前述の真似をしながら、それなりに作れば良いと思います。

補足

回答ありがとうございます。

入力することはなく、最終的にはアップロード用に「.csv」として出力させたい為、リレーションされた内容が横1行に吐き出されるようにしたいのです。

> 先のクエリと薬剤、配番で連結すれば、…

がうまくいかない(配番が表示されない)のですが、クロス集計の設定が間違っているのでしょうか？

薬剤/効果1/効果2/効果3
A剤/00001/空　欄/00002
B剤/00001/空　欄/空　欄
C剤/00001/000003/空　欄