Microsoft Access 掲示板

「パススルークエリまたは非固定列のクロス集計クエリを、サブフォームまたはサブレポートのレコードソースとして使うことはできません。」

これはAccessの仕様なのでどうすることもできません。
これ以外の方法を考えることになると思います。

詳細は不明なのでアイデアだけになりますが、

列固定のクロス集計クエリならサブレポートに使えるので、VBAでクエリのSQLを修正するようにするとか。

サブレポートは使わずに、レポートのグループ化の機能でなんとかするとか。

など。

hatena様

返信ありがとうございます。また、遅くなりすみません。
>>仕様
ありがとうございます。
またアイデアありがとうございました。検討してみます！

たびたびの質問で失礼します。

先の件はアドバイスに従い大本のレポートとし、プロジェクトの概要をサブレポートとして解決しました。ありがとうございました。
そこで新たな質問させていただきたいのですが、ご教授いただけたらと思います。

先のリンクを参考に以下のクロス集計クエリーを作成しています。
| 大分類    | 商品名    |売上年月:Format([日付]."mm/dd")    |売上数量:数量    |
| -------- | -------- | -------- | -------- |
| 商品分類      | 商品      |       | 売上      |
| グループ化      | グループ化      | グループ化      | 合計      |
| 行見出し      | 行見出し      | 列見出し      | 値      |

そしてサブレポートとは大分類でリンクフィールドを設定。
大分類でレポートのページを切り替えたく、大分類でグループ化しています。
すると、売上のない日付もすべて表示されてしまいます。希望としては、大分類で該当しない日付と数量のみを表示させたいのです。
レポートのページ毎に更新？とかできたらいいのかなと思い、レポートのイベントプロシージャなど考えてみましたがうまくいきません。なにか可能な方法ございますでしょうか。
文書でうまく表現することができなく申し訳ございません。よろしくお願いします。

情報が少なすぎて、概要が把握できません。

現状のメインレポートとサブレポートのレコードソースのSQLを提示してもらえますか。

それと、現状のレポートのデザインビュー画像、レポートプレビュー画像。

hatena様

お返事いただきありがとうございます。わかりにくくて申し訳ございません。
お手数おかけしますが、よろしくお願いします。

メインレポートSQL
TRANSFORM Sum(Tレジメンリスト2.投与量) AS 投与量の合計
SELECT T_レジメン名.[レジメンコード], T_Rpリスト.Rp名
FROM (T_Rpリスト INNER JOIN Tレジメンリスト2 ON T_Rpリスト.Rpコード = Tレジメンリスト2.Rpコード) INNER JOIN T_レジメン名 ON Tレジメンリスト2.[レジメンコード] = T_レジメン名.[レジメンコード]
GROUP BY T_レジメン名.[レジメンコード], Tレジメンリスト2.番号, T_Rpリスト.Rp名
ORDER BY T_レジメン名.[レジメンコード], Tレジメンリスト2.番号, Format([投与day],"@@")
PIVOT Format([投与day],"@@");

サブレポートSQL
SELECT T_レジメン名.[レジメンコード], T_レジメン名.[レジメ番号], T_レジメン名.[レジメン名], T_レジメン名.投与基準, T_レジメン名.投与基準2, T_レジメン名.[スケジュール2], T_レジメン名.全レジメ, T_レジメン名.[スケジュール], T_レジメン名.注意点, T_レジメン名.薬品名・投与量, T_レジメン名.減量方法, T_レジメン名.適応, T_レジメン名.適応2, T_レジメン名.適応3, T_レジメン名.血管外漏出リスク, T_レジメン名.嘔吐リスク, T_Rpリスト.Rp名, T_Rpリスト.閉鎖式使用, T_Rpリスト.[フィルターの有無], T_Rpリスト.制吐リスク, T_Rpリスト.一般名, Tレジメンリスト2.投与量, Tレジメンリスト2.投与単位, T_Rpリスト.確認事項, T_Rpリスト.副作用など, T_Rpリスト.[その他注意点], T_レジメン名.総コース
FROM (T_Rpリスト INNER JOIN Tレジメンリスト2 ON T_Rpリスト.[Rpコード] = Tレジメンリスト2.[Rpコード]) INNER JOIN T_レジメン名 ON Tレジメンリスト2.[レジメンコード] = T_レジメン名.[レジメンコード]
GROUP BY T_レジメン名.[レジメンコード], T_レジメン名.[レジメ番号], T_レジメン名.[レジメン名], T_レジメン名.投与基準, T_レジメン名.投与基準2, T_レジメン名.[スケジュール2], T_レジメン名.全レジメ, T_レジメン名.[スケジュール], T_レジメン名.注意点, T_レジメン名.薬品名・投与量, T_レジメン名.減量方法, T_レジメン名.適応, T_レジメン名.適応2, T_レジメン名.適応3, T_レジメン名.血管外漏出リスク, T_レジメン名.嘔吐リスク, T_Rpリスト.Rp名, T_Rpリスト.閉鎖式使用, T_Rpリスト.[フィルターの有無], T_Rpリスト.制吐リスク, T_Rpリスト.一般名, Tレジメンリスト2.投与量, Tレジメンリスト2.投与単位, T_Rpリスト.確認事項, T_Rpリスト.副作用など, T_Rpリスト.[その他注意点], T_レジメン名.総コース;

レポートビューは「式が正しくないか、複雑すぎるため評価できません。たとえば、数式に複雑な要素が多すぎます。変数に式の一部を割り当て、式を簡単にしてください。」と出てしまい表示できません。
印刷プレビューと、メインレポートのクエリも載せます。

ちょっとよくわからないけれど、集計クエリの段階で
集計:Where条件、抽出条件:“あああ“みたいな感じに
絞り込む仕組みを検討してみるとか。

りんご様

返信ありがとうございます。
レポートの抽出条件でコードを指定すると、作成希望通り
空白はなくなりました。しかし、数値のあるところも一部表示されず。
画像は上記画像の2/4のコードで指定してます。[8]のところの数値がない。

メインレポートのページヘッダにある、レジメンコードで各ページを抽出できるといいのですが。。
上記URLのvbaにそういうコードを追記？などとも思いましたが具体的な書き方わからず。
すみません。

レポートビュー「式が正しくないか、複雑すぎるため評価できません。たとえば、数式に複雑な要素が多すぎます。変数に式の一部を割り当て、式を簡単にしてください。」
↑これについてはサブレポートを再作成して解決しました。

レポートの抽出条件でコードを指定すると

フィルターで抽出したという事かな。レポート機能にあまり詳しくないのであれですが、メインレポートSQLにWHERE句を追加する方向でチャレンジするとどうなりますか？
WHERE T_レジメン名.レジメンコード=11031

りんご様

なんどもありがとうございます。
レポートのレコードソースのクエリにて抽出しました。データシートビューでは添付画像のように[8]が表示されています。他のコードを入力してもデータシートビューではすべて表示されるが、レポートになると一部表示されていないようです。これは別に問題ありそうですね。。

SQLです。
TRANSFORM Sum(Tレジメンリスト2.投与量) AS 投与量の合計
SELECT T_レジメン名.[レジメンコード], T_Rpリスト.Rp名
FROM (T_Rpリスト INNER JOIN Tレジメンリスト2 ON T_Rpリスト.Rpコード = Tレジメンリスト2.Rpコード) INNER JOIN T_レジメン名 ON Tレジメンリスト2.[レジメンコード] = T_レジメン名.[レジメンコード]
WHERE (((T_レジメン名.[レジメンコード])=11031))
GROUP BY T_レジメン名.[レジメンコード], Tレジメンリスト2.番号, T_Rpリスト.Rp名
ORDER BY T_レジメン名.[レジメンコード], Tレジメンリスト2.番号
PIVOT Format([投与day],"@@");

　念の為、レポートの非連結テキストボックスに諸々を設定するコードを確認して頂けますか。For…Nextで、初期値から繰り返し回数まで、となっていると思いますが、この繰り返し回数がもう1回増えるように書き換えると、どうでなりますか？

りんご様

ありがとうございます。
いくつか試したところ、複数列あるコードで表示してみたところ、最終列が表示されていないことがわかりました。昨日の[8]が表示されないのも、これが最終列なためと思われます。

一番最初に質問させていただいたところに記載したURLを参考に以下記載しています。というか、まるまるコピーです。
りんご様の方法で行けそうな気もしますが、どのような記載をすればよいかお分かりでしたらご教授願います。

Private Sub Report_Open(Cancel As Integer)

Dim db As DAO.Database
Dim qd As DAO.QueryDef
Dim cnt As Integer
Dim fld As DAO.Field

Set db = CurrentDb()
Set qd = db.QueryDefs(Me.RecordSource)

For cnt = 2 To qd.Fields.Count - 2
    Set fld = qd.Fields(cnt)
    Me("Label" & cnt).Caption = fld.Name
    Me("Field" & cnt).ControlSource = fld.Name
    Me("Total" & cnt).ControlSource = "=Sum([" & fld.Name & "])"
Next

End Sub

また、今後のこと考え、別途フォームにてコードを指定し、そこからレポートを表示する方法で検討してみました。
コードを指定し、トグルのイベントに以下記載しました。
DoCmd.OpenReport "R_レジメンワークシート", acViewPreview
レポートは指定したものが表示されたのですが、すべてのコードで列がすべて表示されなくなってしまいました。
access難しい。。

重ね重ね申し訳ございません。
よろしくお願いします。

For cnt = 2 To qd.Fields.Count - 2

For cnt = 2 To qd.Fields.Count - 1

DoCmd.OpenReport "R_レジメンワークシート", acViewPreview

DoCmd.OpenReport "R_レジメンワークシート", acViewReport

うまくいくかわからないけれど、とりあえず、どうでしょう。

リンゴ様

返信ありがとうございます。
試しましたが、変化なしです。
ヘージヘッダー、ページフッターにコントロールソースをコードにしたテキストボックスを作成すると選択したコードが表示されていることから、レポートのレコードソースのwhere条件読み込む前にReport_Openのコードが実施されているとかあるのでしょうかね？？でもそうするとRp名（この場合カルセド）が表示されてるのと矛盾するか。。

りんご様

レコードソースで直接コードを指定すると最終列が表示されない件は治りました！ありがとうございます！！
For cnt = 2 To qd.Fields.Count - 1　←これでした
まずは一歩進みました！

>> 17

あとは、添付画像の場合４以降を表示させないようにできれば。。

クロス集計の前に仕組みを入れないと無理だと思います。where条件パラメータの問題解決に戻りましょう。"レジメンコード=" & Me.レジメンを削除するのを忘れずに。

>> 16

where条件で直接コードを入れた結果は…希望の数値が出ました。

　[Forms]![F_レジメンワークシート]![レジメン]の参照値が直接コードと違う値になっていませんか？意図せずにという事もあります。念の為、もう1度確認してみて下さい。例えば、コマンドボタンを作って、メッセージボックスやデバッグプリントで[Forms]![F_レジメンワークシート]![レジメン]の中身を見てみると何かわかるかもしれません。パラメータ参照値が駄目なら直接コードも駄目になるはず。
　
>> 5

メインレポートSQL
TRANSFORM … SELECT … FROM …
GROUP BY T_レジメン名.[レジメンコード], Tレジメンリスト2.番号, T_Rpリスト.Rp名
ORDER BY T_レジメン名.[レジメンコード], Tレジメンリスト2.番号, Format([投与day],"@@")
PIVOT Format([投与day],"@@"

　データがどのように登録されているかわからないので、何とも言えませんが、Tレジメンリスト2.番号を削除してみるのも。クロス集計に必要不可欠、これがないと投与dayがおかしくなるという事であれば無理ですが。
　その他に、番号その1のカルセドと番号その2のカルセドがある場合、片方の投与dayと投与量が未入力になっていないか見直してみるのも。

　試行錯誤の結果が期待通りか否かは、クロス集計クエリを直接開いたり、選択クエリに戻して確認すると良いと思います。

りんご様

ありがとうございます。
Debug.Print Me.レジメン
で確認したところ、レジメンコードが表示されています。また、レポートをデザインビューにし、レコードソースのデータシートビューでもきちんと表示されています。

また、Tレジメンリスト2.番号を削除してもダメでした。
2のカルセドについても数値が入っているのを確認しています。レジメンコードを指定しないで、すべてのレポートを表示さると表示されるので。。

おっしゃる通り、
クロス集計の前に仕組みを入れないと無理だと思います。
だと思うのですが、どういう順番でデータを読み込んでレコードが作成されているのかですかね。

何度もすみません。試してみてください。

Private Sub Report_Open(Cancel As Integer)
Dim db As DAO.Database
Dim qd As DAO.QueryDef
Dim cnt As Integer
Dim fld As DAO.Field
Dim rs As DAO.Recordset
　Set db = CurrentDb()
　Set qd = db.QueryDefs(Me.RecordSource)
　qd.Parameters(“[Forms]![フォームの名前]![絞り込みの名前]”)=[Forms]![フォームの名前]![絞り込みの名前]
　Set rs = qd.OpenRecordset
　For cnt = 2 To rs.Fields.Count - 1
　　Set fld = rs.Fields(cnt)
　　Me("Label" & cnt).Caption = fld.Name
　　Me("Field" & cnt).ControlSource = fld.Name
　　Me("Total" & cnt).ControlSource = "=Sum([" & fld.Name & "])"
　Next
End Sub

下記を追加。フォームの名前と絞り込みの名前は適宜変更して下さい。
Dim rs As DAO.Recordset
qd.Parameters(“[Forms]![フォームの名前]![絞り込みの名前]”)=[Forms]![フォームの名前]![絞り込みの名前]
Set rs = qd.OpenRecordset
下記を変更。
For cnt = 2 To rs.Fields.Count - 1
Set fld = rs.Fields(cnt)

りんご様

できました！！！！
本当に本当にありがとうございます！！
レコードソースのクエリwhere条件とパラメーターは設定したままで、教えていただいたコードを修正したところできました。

本当に本当にありがとうございました！