SELECT T2.部品登録ID, T2.品名, T1.小分類名, T2.型式, T3.価格
FROM (T00小分類マスタ AS T1 INNER JOIN T01部品マスタ AS T2 ON T1.小分類ID = T2.小分類ID)
INNER JOIN T01価格マスタ AS T3 ON T2.部品登録ID = T3.部品登録ID;
変更後のクエリでT01部品マスタ AS T2という指定があるので、このクエリの中では「T2はT01部品マスタの代わりとして使える」となります。
SELECT T01部品マスタ.部品登録ID, T01部品マスタ.品名, T00小分類マスタ.小分類名,
T01部品マスタ.型式, T01価格マスタ.価格
FROM (T00小分類マスタ INNER JOIN T01部品マスタ ON T00小分類マスタ.小分類ID = T01部品マスタ.小分類ID)
INNER JOIN T01価格マスタ ON T01部品マスタ.部品登録ID = T01価格マスタ.部品登録ID;
↓
SELECT T2.部品登録ID, T2.品名, T1.小分類名, T2.型式, T3.価格
FROM (T00小分類マスタ AS T1 INNER JOIN T01部品マスタ AS T2 ON T1.小分類ID = T2.小分類ID)
INNER JOIN T01価格マスタ AS T3 ON T2.部品登録ID = T3.部品登録ID;
SELECT
T1.伝票ID
,T1.テーブル番号
,T1.日時
,T1.指名
,T1.名前
,T1.ドリンク
,T1.ドリンク代金
,IIf(T1.指名 = "本指名", T2.テーブル代金 / T3.本指名カウント) AS バック代金
FROM
(
MT_売上 AS T1
INNER JOIN
(
SELECT
伝票ID
,テーブル番号
,日時
,sum(ドリンク代金) AS テーブル代金
FROM
MT_売上
GROUP BY
伝票ID
,テーブル番号
,日時
) AS T2
ON
T1.伝票ID = T2.伝票ID
AND T1.テーブル番号 = T2.テーブル番号
AND T1.日時 = T2.日時
)
INNER JOIN
(
SELECT
伝票ID
,テーブル番号
,日時
,count(*) AS 本指名カウント
FROM
MT_売上
WHERE
指名 = "本指名"
GROUP BY
伝票ID
,テーブル番号
,日時
) AS T3
ON
T1.伝票ID = T3.伝票ID
AND T1.テーブル番号 = T3.テーブル番号
AND T1.日時 = T3.日時
;
メインレポート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],"@@");
りんご様
返信ありがとうございます。
レポートの抽出条件でコードを指定すると、作成希望通り
空白はなくなりました。しかし、数値のあるところも一部表示されず。
画像は上記画像の2/4のコードで指定してます。[8]のところの数値がない。
メインレポートのページヘッダにある、レジメンコードで各ページを抽出できるといいのですが。。
上記URLのvbaにそういうコードを追記？などとも思いましたが具体的な書き方わからず。
すみません。
レポートビュー「式が正しくないか、複雑すぎるため評価できません。たとえば、数式に複雑な要素が多すぎます。変数に式の一部を割り当て、式を簡単にしてください。」
↑これについてはサブレポートを再作成して解決しました。
ありがとうございました。
勉強になりました。
データ ソースの代替名を使用する
フィールドまたは式に代替名を使用する: AS キーワード
SELECT T2.部品登録ID, T2.品名, T1.小分類名, T2.型式, T3.価格
FROM (T00小分類マスタ AS T1 INNER JOIN T01部品マスタ AS T2 ON T1.小分類ID = T2.小分類ID)
INNER JOIN T01価格マスタ AS T3 ON T2.部品登録ID = T3.部品登録ID;
変更後のクエリで
T01部品マスタ AS T2という指定があるので、このクエリの中では「T2はT01部品マスタの代わりとして使える」となります。りんご様
https://hatenachips.blog.fc2.com/blog-entry-350.html
この内容スゴイです。
こんな方法もあるんですね。
ありがとうございました。
hiroton様 りんご様 回答ありがとうございました。
hiroton様 スペルミス申し訳ありませんでした。
https://sys-daddy.com/access_subquery/
上記のサイトを参考にして、自分なりにSQL文を記載したのですが、ダメでした。
再度、質問なのですが、エイリアスとは、T1のことをしめしているのでしょうか？
エイリアスを検索したら、エイリアスは、あだ名みたいなものと記載しているサイトがありました。
例えば下記のクエリです。
↓
T01部品マスタ.部品登録ID⇒T2.部品登録IDに、なぜ変更できるのでしょうか？
この手のコードを理解するにあたり、お勧めの書籍等があれば、教えていただきたいです。
サブクエリは苦手なので答えられないけど、あれこれやってみました。Dcountを追加してみるのは？
Dcount("指名", "MT_売上", "指名='本指名' AND 伝票ID=" & [伝票ID] & "AND テーブル番号=" & [テーブル番号] & "AND 日時=#" & [日時] "#") AS 本指名の合計
以下、手打ちなのでエラーが出たらごめんなさい。
余談ですが、Hatenaさんのサイトを見ながらあれこれするのも楽しかった。
hatena-chips:更新クエリで定義域集計関数を使わずに集計する
とりあえず、ほにゃららテーブルを作成してから遊んでみると。
ちょっとよくわからないけれど、集計クエリの段階で
集計:Where条件、抽出条件:“あああ“みたいな感じに
絞り込む仕組みを検討してみるとか。
質問内の「Q_本指名カウント」が間違っている（コピペミス？）のでどこをどうすればとは言えませんが、基本的に後のクエリで呼び出している部分をそっくりそのまま置き換えればうまくいくと思います。置き換えた部分はSQL終了文字（
;）を削除して、括弧（()）で括りますex)
LEFT JOIN Q_バック代金 ON
↓
LEFT JOIN (SELECT Q_売上合計.伝票ID,～(略)～) ON
また、自己結合をする場合、適宜エイリアスを使用し、その他、（サブクエリ関係なく）一般的なクエリの記述として、複雑なクエリになる場合には適切な括弧の運用が必要になります
具体的にはINNER JOINを連続させる場合、1回であれば必要のない括弧が必要になったりします。
あと、単純に必要な計算の見極めが必要ですね
MT_売上の構造自体が若干怪しいですが、そちらは無視して回答しています
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_レジメン名.総コース;
レポートビューは「式が正しくないか、複雑すぎるため評価できません。たとえば、数式に複雑な要素が多すぎます。変数に式の一部を割り当て、式を簡単にしてください。」と出てしまい表示できません。
印刷プレビューと、メインレポートのクエリも載せます。
情報が少なすぎて、概要が把握できません。
現状のメインレポートとサブレポートのレコードソースのSQLを提示してもらえますか。
それと、現状のレポートのデザインビュー画像、レポートプレビュー画像。
たびたびの質問で失礼します。
先の件はアドバイスに従い大本のレポートとし、プロジェクトの概要をサブレポートとして解決しました。ありがとうございました。
そこで新たな質問させていただきたいのですが、ご教授いただけたらと思います。
先のリンクを参考に以下のクロス集計クエリーを作成しています。
| 大分類 | 商品名 |売上年月:Format([日付]."mm/dd") |売上数量:数量 |
| -------- | -------- | -------- | -------- |
| 商品分類 | 商品 | | 売上 |
| グループ化 | グループ化 | グループ化 | 合計 |
| 行見出し | 行見出し | 列見出し | 値 |
そしてサブレポートとは大分類でリンクフィールドを設定。
大分類でレポートのページを切り替えたく、大分類でグループ化しています。
すると、売上のない日付もすべて表示されてしまいます。希望としては、大分類で該当しない日付と数量のみを表示させたいのです。
レポートのページ毎に更新？とかできたらいいのかなと思い、レポートのイベントプロシージャなど考えてみましたがうまくいきません。なにか可能な方法ございますでしょうか。
文書でうまく表現することができなく申し訳ございません。よろしくお願いします。
りんご様 hiroton 様
ご回答いただきありがとうございます。
おかげ様で解決しました。
フィールド名を「団体コード」もしくは「c1_団体コード」にしたらうまくいきました。
調べてみましたが、フィールド名の頭の文字に数字を使用するのは全角・半角に関わらずNGなんですね
フィールドの列数自体は必要な情報なので「c1_団体コード」で運用しようと思います。
数字のみ、または、数字から始まるフィールド名を使うのはやめましょう。クエリはそれをフィールド名と認識できません
フィールド名であることを明示するために
[2_団体コード]のように記述することは可能ですが（質問のように）間違いのもとです2 バイトのアラビア数字が原因で Microsoft Access でクエリを実行すると、エラー メッセージが表示される
hatena様
返信ありがとうございます。また、遅くなりすみません。
>>仕様
ありがとうございます。
またアイデアありがとうございました。検討してみます！
hatena 様
＞まずは公式のヘルプで確認しましょう。
これによると引数はすべて文字列で指定することが分かります。
ありがとうございます。
文字列式とありました。
今後は公式のヘルプを何度も読み直し、しっかりと確認いたします。
＞どこで調べたか分かりませんが、それはあきらかに間違いですね。
今回のエラーに関して様々な質問板等を見ているときに、DLookup (Expr, Domain, Criteria)ではExprの部分は""で囲まなくてOKです！と書いてあったので、お恥ずかしくも鵜吞みにしておりました。。。
また、きめ細やかなご解説本当に有難うございます。
リンクまで貼って下さり有難うございます。
拝見しました。
素人のわたくしにも理解できるように、詳しくご解説してくださっているので、
とても分かりやすく、勉強になりました。
重ね重ね御礼申し上げます。
まずは公式のヘルプで確認しましょう。
Application.DLookup メソッド (Access) | Microsoft Docs
これによると引数はすべて文字列で指定することが分かります。
>「"入数"」については「"」を付けなくてもよいという記述もあり、まだ理解できておりません。
どこで調べたか分かりませんが、それはあきらかに間違いですね。
第3引数は、抽出条件式を文字列として指定します。
具体的にはクエリのSQL文のWhere句に設定する条件式になります。
例えば 仕入先IDが 1、仕入商品IDが 2 のものを抽出するとき、SQLでは下記のようになります。
(SQLが分からなければ、デザインビューで条件を設定してからSQLビューにすれば確認できます。)
これをDLookupに変換すると、下記のようになります。
1 と 2 の部分は固定値ではなく、フォームのテキストボックスの値を参照することになるので、文字列の外に出して、
この固定値の部分をコントロール参照式に変更します。
ここで注意することは、DLookup関数の第3引数に設定するのはSQLの条件式ということです。
SQLとVBAは似ていますが、まったく別のものだということを意識してください。
VBAとSQLでも文字列の扱いの違いは、下記で詳しく解説していますので、ご参考に。
Access上のコード内で引用符(")と単引用符(')の使い分けについて - hatena chips
hatena 様
お忙しい中大変ご丁寧にありがとうございました。
お教え下さった通りに変更しましたところ、
無事、成功しました。
お教えくださった内容をもとに、何が間違えていたのかを調べました。
正解か分かりかねますが、変数を扱いたいにもかかわらず、お教え下さった下記のように
入数 = DLookup("入数", "UQ仕入商品一覧", "仕入先ID = " & Forms![F仕入入力]![仕入先ID] & " And 仕入商品ID = " & [仕入商品ID])
「"入数"」と「" & Forms![F仕入入力]![仕入先ID] & " 」としていなかったことについて調べました。
「"入数"」については「"」を付けなくてもよいという記述もあり、まだ理解できておりません。
継続して調べてまいります。
「" & Forms![F仕入入力]![仕入先ID] & " 」については、変数を扱うにおいては「"&変数&"」としなければならないとの結論に至りました。（正解か不明ですが…）
色々と調べていたものの、一週間ほど悩んでおりましたので、解決して一安心しております。
本当に有難うございました。
りんご 様
お忙しい中大変ご丁寧にありがとうございました。
＞抽出条件無しでの動作確認、単独条件での動作確認、パラメータの代わりに適当な値を入れて動作確認はどうでしたか？
抽出条件無での動作確認は問題ございませんでした。
また、単独条件での動作確認ですが、仕入商品ID(オートナンバー型)を単独条件としたところ、本来導き出したい数値とは異なるものが代入されました。
わたくしの勝手な推測なのですが、仕入商品ID（オートナンバー型）は仕入先ID各社の仕入商品テーブルがあり、仕入商品ID(オートナンバー型)が重複するため、別のものを代入しているのではないかと考えます。
パラメータの代わりにダイレクトに仕入先IDを3仕入商品IDを3として動作してみたところ、
いずれも「エラー13 型が一致しません」となりました。
わたくしの式の書き方が悪いことが原因と思います。（恥ずかしながら、調べながら利用している素人です）
下記3通り試しました。
入数 = DLookup(入数, "UQ仕入商品一覧", "仕入先ID = 3" And "[仕入商品ID] = 3")
入数 = DLookup(入数, "UQ仕入商品一覧", "[仕入先ID] = 3" And "[仕入商品ID] = 3")
入数 = DLookup(入数, "UQ仕入商品一覧", "Forms![F仕入入力]!仕入先ID = 3" And "[仕入商品ID] = 3")
＞念の為、コンボボックスについても確認です。仕入先IDや仕入商品IDですが、1列目ならば連結列は1、2列目ならば連結列は2となっていますか？
プロパティシートにて連結列確認いたしました。
連結列はコンボボックスすべてにおいて「1」でした。
＞念の為、仕入先IDや仕入商品IDについても確認です。オートナンバー型との事ですか、0001、0002、0003みたいな感じではないですよね？実はNULLもあったり、なんてことはないですよね？
仕入先IDと仕入商品IDは頭に0等は入らず全て整数の「1.2.3…」となっております。
またNULLはございません。
全てのIDには必ず整数が入るようにしております。
hatena様から教えていただきました通りに変更したところ、入数には正しい数値が代入されました。
とりあえずエラーの行を下記のように変更してみてください。
これはAccessの仕様なのでどうすることもできません。
これ以外の方法を考えることになると思います。
詳細は不明なのでアイデアだけになりますが、
列固定のクロス集計クエリならサブレポートに使えるので、VBAでクエリのSQLを修正するようにするとか。
サブレポートは使わずに、レポートのグループ化の機能でなんとかするとか。
など。
団体コードに戻してみたり、団体コード2に変更してみたりするとどうなりますか？
DLookup、正直苦手なのでしっかり回答出来ないのですが、追加情報があればいいかもと。
抽出条件無しでの動作確認、単独条件での動作確認、パラメータの代わりに適当な値を入れて動作確認はどうでしたか？パラメータの参照は想定通り？(仕入商品IDが何処を参照する事になるのか誰か解説して欲しい。)
念の為、コンボボックスについても確認です。仕入先IDや仕入商品IDですが、1列目ならば連結列は1、2列目ならば連結列は2となっていますか？
念の為、仕入先IDや仕入商品IDについても確認です。オートナンバー型との事ですか、0001、0002、0003みたいな感じではないですよね？実はNULLもあったり、なんてことはないですよね？
投稿者です。
あまりにも拙い説明で、非常に分かりにくいかと存じます。
明日改めて、各種画像を添付致しますので、何卒、ご教授のほど、よろしくお願い申し上げます。
テキストボックスの高さをフォントに合わせたいということでしょうか。
デザインビューでテキストボックスを右クリックで[サイズ変更]→[自動調整]でどうでしょう。
良く見たらUTF-8(BOMなし)のASCIIのみのファイルでした。お騒がせしましてすみません。
零細寄りの中小だと取引先がOffice2010以前とかザラなので365の新関数とか使う気が起きないしPowerQueryのファイル集計すら使えないことがある
EmotetみたいなこともあるのでExcelはVBA一切使わずデータ加工を段階ごとに山盛りの非表示シート連鎖させてPivotテーブルまでの機能で似非アプリ化しても元々のデータモデルの認識が間違ってなければ何とかなってしまう
Accessはランタイムでちゃんと動くようエラー処理すれば非常にコスパの良いローコード開発環境（ただしDXとは真逆）
「販売管理システムで学ぶモデリング講座」の目次
教えていただいた方法を試したところ、hatena様の方法で解決できました。
お二人ともありがとうございました。
回答有り難う御座います。なるほど改行コードでもsplitで分割出来るのですね。試みてみます。。
hatena 様
ありがとうございます。
やはりおっしゃるようなテーブルである必要がありますか。
提示していただいたテーブルでなら進めれそうだったのですが、入力時に横方向に入力したいなと思っており悩んでました。
フォームの設計もしくはvbaでのコピペか、悩んでみます。
お忙しいところ相談乗っていただき、ありがとうございました。
まずテーブルがデータベース的な設計になってません。
「正規化」すべきです。具体的には下記のようなテーブル設計にします。
T_予定表
このようなテーブルならクロス集計クエリでご希望の形に簡単に変換できます。クロス集計ウィザードでやってみてください。
このようなテーブルでは入力しにくいという場合は、フォームの設計を工夫して入力しやすいようにします。
メインサブフォーム形式にする場合が多いですね。
あるいは入力用に提示のテーブルを作成しておいて、それでフォームを作成し入力してVBAで上記の正規化されたテーブルに転記するという設計にしてもいいでしょう。
VBAコードをLibreOffice・Writerに貼り付け、文字の置換でできました。ありがとうございました。
>> 13
>> 16
>> 17
しかし、よくよく見れば、DMinで対応出来ているのでそんな事にはならなかった。
すみません、視野が狭くなって勘違いしてました。
りんご様、hatena様、
ありがとうございます。今日はできないので、後で双方のやり方で試してみてまた結果をご報告します。
同姓同名の問題か、転籍等により出向元が変わる場合のことを仰られているのでしょうか。
転籍の場合、手続き上、退職をして入社をするので、氏名が同じでも社員コードが変わり別人格となります。
運用上は、同姓同名の問題もありますので、氏名ではく社員コードで管理をしますので、SQLは読み替えています。
ですので、上記フラグは0で良いです。
レポートの方で「グループ化、並べ替え」の設定をしているとクエリの並べ替えは無視されます。
現状、Numフィールドでグループ化されていますので、その下に「時間」フィールドでの並べ替えを追加してみてください。
上記の「並べ替えの追加」をクリックして「時間」フィールドを選択すればOKです。
データのテキストボックス txtData
４つのテキストボックス txt1, txt2, txt3. txt4
として、
Split 関数 (Visual Basic for Applications) | Microsoft Docs
試していませんが、誤魔化せるかもしれません。
時間1のテキストボックスをフッターに移動すると成功したりしませんか？運が良ければたまたま希望通りになるかも。
根本的な対応は表示順みたいなフィールドが必要になりそうですが、そこまでするのもあまり好きではないんですよねぇ。
りんご様
ありがとうございます。コメントを受けて再確認したところ、レコードソースは間違いなくクエリになっています。
そして今気づいた点ですが、クエリを直接実行すると同日付の中で時間できちんとソートになっています。これがレポートになると時間がバラバラになってしまっています。
>> 14