SELECT y.種別
, x.勘定科目コード
, x.勘定科目
, x.[金額(資産)] As cost
, x.[金額(負債)] As revenue
FROM 貸借対照表 x
INNER JOIN T_条件 y
ON ( x.勘定科目コード Between y.コードfrom And y.コードto )
WHERE y.種別 Between 0 And 1
AND Abs( x.[金額(資産)] ) > 0 ;
SELECT y.種別
, x.勘定科目コード
, x.勘定科目
, x.[金額(資産)] As cost
, x.[金額(負債)] As revenue
FROM 貸借対照表 x
INNER JOIN T_条件 y
ON x.勘定科目コード >= y.コードfrom
AND x.勘定科目コード <= y.コードto
WHERE y.種別 Between 0 And 1
AND Abs( x.[金額(資産)] ) > 0 ;
SELECT y.種別
, x.勘定科目コード
, x.勘定科目
, x.[金額(資産)] As cost
, x.[金額(負債)] As revenue
FROM 貸借対照表 x
, T_条件 y
WHERE x.勘定科目コード Between y.コードfrom And y.コードto
AND y.種別 Between 0 And 1
AND Abs( x.[金額(資産)] ) > 0 ;
種別
勘定科目コード
勘定科目
cost
revenue
0
1001
あ
8100
-8100
0
1891
う
1200
-1200
0
1999
え
500000
-500000
1
2000
カ
-120000
120000
1
2888
キ
7000
-7000
1
3001
ち
-10000
10000
0
9002
＊
3200
-3200
0
9004
￥
15000
-15000
■ 仕上げのSQL
SELECT Max( IIf( q.種別 = 0, q.勘定科目コード ) ) As ex1
, Max( IIf( q.種別 = 0, q.勘定科目 ) ) As ex2
, Max( IIf( q.種別 = 0, q.cost ) ) As 金額_資産
, Max( IIf( q.種別 = 1, q.勘定科目コード ) ) As 勘定科目コード
, Max( IIf( q.種別 = 1, q.勘定科目 ) ) As 勘定科目
, Max( IIf( q.種別 = 1, q.revenue ) ) As 金額_負債
FROM
(
SELECT x.種別
, x.勘定科目コード
, x.勘定科目
, x.cost
, x.revenue
, Count(1) As gnum
FROM Q_表示対象 x
, Q_表示対象 y
WHERE x.種別 = y.種別
AND x.勘定科目コード >= y.勘定科目コード
GROUP BY x.種別
, x.勘定科目コード
, x.勘定科目
, x.cost
, x.revenue
) q
GROUP BY gnum
ORDER BY gnum ;
この結果を出すためのSQLコマンドがうまくいきません
↓書いたＳＱＬコマンド
※テーブル名、損益計算書→貸借対照表
SELECT Max(IIf([貸借対照表].[勘定科目コード]<2000 Or [貸借対照表].[勘定科目コード]>9000,[貸借対照表].[勘定科目コード])) AS 勘定科目コード
, Max(IIf([貸借対照表].[勘定科目コード]<2000 Or [貸借対照表].[勘定科目コード]>9000,[貸借対照表].[勘定科目])) AS 勘定科目
, Max(IIf([貸借対照表].[勘定科目コード]<2000 Or [貸借対照表].[勘定科目コード]>9000,[貸借対照表].cost)) AS 金額_資産
, Max(IIf([貸借対照表].[勘定科目コード] Between 2000 And 3001,[貸借対照表].[勘定科目コード])) AS 勘定科目コード2
, Max(IIf([貸借対照表].[勘定科目コード] Between 2000 And 3001,[貸借対照表].[勘定科目])) AS 勘定科目2
, Max(IIf([貸借対照表].[勘定科目コード] Between 2000 And 3001,[貸借対照表].revenue)) AS 金額_負債, 貸借対照表.gnum
FROM (SELECT x.勘定科目コード
, x.勘定科目, x.[金額(資産)] AS cost
, x.[金額(負債)] AS revenue
, Count(1) AS gnum
FROM 貸借対照表 AS x
, 貸借対照表 AS y
WHERE x.勘定科目コード Between 1001 And 3001 Or x.勘定科目コード=9002 Or x.勘定科目コード=9004 Or x.勘定科目コード=9005
AND y.勘定科目コード Between 1001 And 3001 Or y.勘定科目コード=9002 Or y.勘定科目コード=9004 Or y.勘定科目コード=9005
AND x.勘定科目コード \ 1000 = y.勘定科目コード \ 1000
AND x.勘定科目コード >= y.勘定科目コード
AND Abs( x.[金額(資産)] ) > 0
AND Abs( y.[金額(資産)] ) > 0
GROUP BY x.勘定科目コード
, x.勘定科目
, x.[金額(資産)]
, x.[金額(負債)]) AS 貸借対照表
GROUP BY 貸借対照表.gnum
ORDER BY 貸借対照表.gnum;
SELECT t受注一覧.受注ID,
受注金額,
Sum( iif( 日付 < #1/1/2020#, 売上金額)) AS 前月迄売上,
前月迄売上 / 受注金額 AS 前月迄出来高,
Sum( iif(日付 Between #1/1/2020# And #1/31/2020#, 売上金額)) AS 当月売上,
当月売上 / 受注金額 AS 当月出来高,
Sum( 売上金額) AS 累計売上,
累計売上 / 受注金額 AS 累計出来高
FROM t売上明細 INNER JOIN t受注一覧 ON t売上明細.受注ID = t受注一覧.受注ID
GROUP BY t受注一覧.受注ID, 受注金額
HAVING t受注一覧.受注ID Like "18170*";
Sum( iif( t売上明細.日付 < #1/1/2020#, t売上明細.売上金額)) AS 前月迄売上,
Sum( iif(t売上明細.日付 Between #1/1/2020# And #1/31/2020#, t売上明細.売上金額)) AS 当月売上,
Sum( t売上明細.売上金額) AS 累計売上
SELECT x.勘定科目コード
, x.勘定科目
, x.[金額(費用)] As cost
, x.[金額(収益)] As revenue
, Count(1) As gnum
, Sum( IIf( x.勘定科目コード \ 1000 = 4, y.[金額(費用)], 0 ) ) As total_cost
, Sum( IIf( x.勘定科目コード \ 1000 = 5, y.[金額(収益)], 0 ) ) As total_reve
FROM 損益計算書 x
, 損益計算書 y
WHERE x.勘定科目コード Between 4000 And 5999
AND y.勘定科目コード Between 4000 And 5999
AND x.勘定科目コード \ 1000 = y.勘定科目コード \ 1000
AND x.勘定科目コード >= y.勘定科目コード
AND Abs( x.[金額(費用)] ) > 0
AND Abs( y.[金額(費用)] ) > 0
GROUP BY x.勘定科目コード
, x.勘定科目
, x.[金額(費用)]
, x.[金額(収益)]
ORDER BY x.勘定科目コード ;
SELECT Max( IIf( q.勘定科目コード < 5000, q.勘定科目コード ) ) As ex1
, Max( IIf( q.勘定科目コード < 5000, q.勘定科目 ) ) As ex2
, Max( IIf( q.勘定科目コード < 5000, q.cost ) ) As 金額_費用
, Max( IIf( q.勘定科目コード > 4999, q.勘定科目コード ) ) As 勘定科目コード
, Max( IIf( q.勘定科目コード > 4999, q.勘定科目 ) ) As 勘定科目
, Max( IIf( q.勘定科目コード > 4999, q.revenue ) ) As 金額_収益
FROM
(
SELECT x.勘定科目コード
, x.勘定科目
, x.[金額(費用)] As cost
, x.[金額(収益)] As revenue
, Count(1) As gnum
FROM 損益計算書 x
, 損益計算書 y
WHERE x.勘定科目コード Between 4000 And 5999
AND y.勘定科目コード Between 4000 And 5999
AND x.勘定科目コード \ 1000 = y.勘定科目コード \ 1000
AND x.勘定科目コード >= y.勘定科目コード
AND Abs( x.[金額(費用)] ) > 0
AND Abs( y.[金額(費用)] ) > 0
GROUP BY x.勘定科目コード
, x.勘定科目
, x.[金額(費用)]
, x.[金額(収益)]
) q
GROUP BY gnum
ORDER BY gnum ;
実際のテーブル名、フィールド（名前、データ型）、主キー設定をとりあえず提示してください。
また、
例えば、同内容(連続データ)と判断する基準は、秒まで完全に一致する場合か、あるいは、隣接しているなら連続データと判断するのか。あるいは、重なっている場合があるなら、それも連続データとするのか、など。
DCount関数でもできすますが、重い処理になります。
hirotonさんの回答の方法の方が処理ははるかに高速です。
集計クエリの基本ですのでそちらの方法を理解しておきましょう。
DCount関数でできました。
申し訳ありませんでした。
Shell関数で、うまくいきました！！
長年の懸案が解決です。本当にありがとうございました。
クエリに[作業内容]のフィールドをもう一つ増やして集計の方法でカウントを選べばいいですよ
他のMDBを起動するだけなら、Shell関数で開けばいいでしょう。
Shell 関数 (Visual Basic for Applications) | Microsoft Docs
mayu様
いつも回答頂きありがとうございます。
まさか、where句でも結合が出来るなんて知りませんでした。
SQLは覚える事がいっぱいありますけね。
自分もmayu様のように自分で問題解決できるようになりたいです。
改めてありがとうございました。
参照先URL： 入出庫とも単一のテーブルで管理する構成になっている
yossiさんのDB： 入庫( 仕入 ) と 出庫( 販売 ) が別々のテーブルになっている
という解釈でいいのでしょうか。
いずれにせよ、ご自身の環境に合わせて
参照先 URL で記述されている SQL をカスタマイズできないのでしたら
回答には、以下の情報が必要です。
【 1 】
入庫・出庫に伴う全テーブルの
テーブル名 / フィールド名 / データ型 / 主キー
【 2 】
各テーブルのサンプルデータ、もしくは 元データとご希望の結果セット例
【 3 】
累計計算( 現在庫 )は、クエリやフォームでも表示が必要なのか、
或いは、レポートで表現できればいいのか。
【 4 】
入庫・出庫ともに実績が無い 商品ID の表示はどうするのか。
表示が必要なのか、それとも不要なのか。
(
参照先 URL の SQL にも、所々ミスが散見されます。
商品マスタが サブクエリの後に置かれているため、
全商品の表示が必要なら、RIGHT JOIN で記述しないといけませんし、
表示対象が、過去一度でも入出庫の実績がある 商品ID に限定するのであれば
結合種別は 内部結合 が妥当です。
また、スカラサブクエリの GROUP BY 句は必要ありません。
)
ます、サブフォームにデータを表示するなら、RecordSourceプロパティに SQLを設定するだけでOKです。
OpenRecordsetでレコードセットを開く必要も、Recordsetプロパティにレコードセットを代入する必要もありません。
Me!sfDetail.Form.RecordSource = strsqlのところで「閉じているか存在しないオブジェクトを参照しています」のエラーが出るのでしょうか。だとしたら、sfDetail がサブフォームコントロール名でないのが原因かと思われます。
サブフォーム名ではなくサブフォームコントロール名になってますか。下記参照
CテーブルとQテーブルのおもなフィールド名と主キー設定を提示してください。
あと、試してうまくいかなかったクエリのSQL文をコピーしてここに張り付けてください。
回答するには情報不足です。下記の情報を提示してください。
SQL における テーブル同士の結合は
イコールだけではなく、
<= や >= や <> など、他の比較演算子も使えます。
ただ、Access のデザインビューでは
こういった演算子を用いて結合するクエリ を作成することは出来ず、
デザインにおいても、グラフィカルな表示はできません。
SQL の扱いにおいて、デザインビューの機能や能力は かなり限定されたものになります。
また、オブジェクト間における 結合の種類において
内部結合は FROM 句だけではなく、WHERE 句に記述することが可能で
( 外部結合は、FROM 句で JOIN による結合が必須 )
私が記述した SQL は、デザインビューで表示できるよう、
WHERE句 に記述する方法を採用しています。
したがって、>> 7における Q_表示対象 の SQL は、
以下のような記述を用いても、同様の結果を得ることが可能です。
自己解決しました。
TempVars("G_ID").[Value] の「.」が都合悪いみたいです。
なので.[Value]を消しました^^;;
ctl.ControlSource = Replace(ctl.ControlSource, "DLookUp", "TLookUp")
を
ctl.ControlSource = Replace(Replace(ctl.ControlSource, "DLookUp", "TLookUp"), ").[Value]", ")")
としました。
エラーの原因は不明ですが...
削除クエリに含まれるテーブル、またはそれをレコードソースとするフォームを開いていませんか。
そうでないなら、現状の削除クエリをSQLビューにして表示されるSQL文をコピーしてここに貼り付けてください。
mayu様
いつも回答を頂き誠にありがとうございます。
時間遅くなりましたが無事できました。
でも不思議に思ったのは、今回(おまけではない方)は新しく『T_条件』というテーブルを追加してクエリを作成しました。
クエリは結合してテーブル同士をつなぎ合わせるのが通常ですが、これは『T_条件』と『貸借対照表』のテーブルが独立した状態で、結合のような事が出来て条件通りのデータが返されているという事です。
where条件を除けば特に大きな変わりはないはずなのに、何故このような独立したテーブルの状態で、望んだ結果が返されるのでしょうか？
差分などを引きそこから表を当たらしく作ることにしました。
ありがとうございました。
可能ならば、テーブルが２つある設計を変更したほうがいいでしょう。
最初から一つのテーブルにしておいて、必要に応じて、クエリで分割するようにするほうが、シンプルだし高速です。
２つのテーブルの列数、フィールドの順番を同じになるようにすればいいでしょう。
mayuさん、ありがとうございます。
最初に教えて頂いた内容で何とか形になることができました。
適切なアドバイスありがとうございました😆
ちなみに、yossiさんが参考にした レコードがない場合も用紙の最後まで罫線を出力する関数
のレポートに対して、ご希望の表示にする場合は
受注コード ヘッダーのフォーマット時イベントに
と記述すれば、あとは 先の回答と同じ手順で出来るでしょう。
※ テキストボックス txtGC は、受注コードのグループフッターに配置すればいいでしょう。
※ RptFixRowFunc_07.accdbのレポートは
関数の引数により、２０行分の罫線を出力していますが、
ページフッター( の高さ )で、２行分を消費するため、
１ページに表示可能なデータの最大行数は 実質 １８ となっています。
したがって、合計金額表示用テキストボックスのコントロールソースは
=IIf([Page] = -Int(-[txtGC] / 18), [合計], Null)
※ 今回は、[印刷時拡張] プロパティが「 いいえ 」のため、
簡単にグループ毎のページ数が算出可能ですが、
「 はい 」だと、この方法は使えず、難易度は高くなります。
yossiさんが参考にしたコンテンツの現象 及び 要望を正確に申し上げるなら
グループ毎のページ数が、( １ページに収まらず )複数ページに及ぶ場合でも
合計金額が全てのページに表示されてしまうため、
グループ毎の最終ページにのみ、合計金額を表示する方法が知りたい
ではないでしょうか。
だとすると
参考になさるのは、こちらのほうがいいでしょう。
上記のコンテンツでダウンロードできる RptFixRowPagePages.accdb
のレポート R_納品書 を例にして説明すると
■ 手順１
受注コード のグループヘッダーへ、テキストボックスを新たに１つ配置します。
このテキストボックスの各プロパティを
名前： txtGC
コントロールソース： =Count(1)
可視： いいえ
とします。
■ 手順２
このレポートは、ページ毎に最大１５レコード表示できるようですから
ページヘッダーに配置されている
合計金額を表示するためのテキストボックス( 担当者氏名の直下にある )
のコントロールソースを
=IIf([Page] = 1, [合計], Null)
から
=IIf([Page] = -Int(-[txtGC] / 15), [合計], Null)
に変更します。
以上です。
( おまけ )
>> 5reito117さんの SQL を添削した正解例は、以下になります。
( 前回申し上げたとおり、煩雑で汚い SQL 文になりますから、この記述はお勧めしません )
hirotonさん
コメントありがとうございます。
目的は、誤入力でエラー状態になると、プログラムが停止してしまう事を回避する事です。
おっしゃる通り誤入力を防ぐという手段でも回避は可能ですね。
早速設定しましたら、ありえない年月の入力はできなくなりエラー回避できるようになりました。
とりあえずこれで運用できそうです。
どうも、ありがとうございました。
( 続き )
次に、勘定科目コード、種別、金額 の抽出条件全てを満たす
レコードを表示するためのクエリを作ります。
( SQLの結果に Q_表示対象 という名前を付けて保存します )
■ 仕上げのSQL
■ 結果
左側に表示するコードと、右側に表示するコードに 規則性や共通点が無いですから
という区分けが、意味をなさなくなったのが原因です。
とはいえ、
SQL文の記述が、読みにくくて 見通しが悪い場合は
条件の分岐や、マジックナンバーの追記 といった手法を採用すると
記述がますます汚くなりますから
設計を見直したり工夫するのが、定番の解決方法になります。
■ 貸借対照表
`sqlrid AUTOINCREMENT PRIMARY KEY
, 種別 INT NOT NULL
, コードfrom INT NOT NULL
, コードto INT NOT NULL
);
回答ありがとうございます。
教えて頂いた通りにできました。
また、教えて頂いて恐縮ですが下記の様にするにはどうすればいよいか教えて頂きたく。
・1000番代と9002,9004,9005番代が左側、2000番代と3000番代が右側になるようにする。
この結果を出すためのSQLコマンドがうまくいきません
↓書いたＳＱＬコマンド
※テーブル名、損益計算書→貸借対照表
SELECT Max(IIf([貸借対照表].[勘定科目コード]<2000 Or [貸借対照表].[勘定科目コード]>9000,[貸借対照表].[勘定科目コード])) AS 勘定科目コード
, Max(IIf([貸借対照表].[勘定科目コード]<2000 Or [貸借対照表].[勘定科目コード]>9000,[貸借対照表].[勘定科目])) AS 勘定科目
, Max(IIf([貸借対照表].[勘定科目コード]<2000 Or [貸借対照表].[勘定科目コード]>9000,[貸借対照表].cost)) AS 金額_資産
, Max(IIf([貸借対照表].[勘定科目コード] Between 2000 And 3001,[貸借対照表].[勘定科目コード])) AS 勘定科目コード2
, Max(IIf([貸借対照表].[勘定科目コード] Between 2000 And 3001,[貸借対照表].[勘定科目])) AS 勘定科目2
, Max(IIf([貸借対照表].[勘定科目コード] Between 2000 And 3001,[貸借対照表].revenue)) AS 金額_負債, 貸借対照表.gnum
FROM (SELECT x.勘定科目コード
, x.勘定科目, x.[金額(資産)] AS cost
, x.[金額(負債)] AS revenue
, Count(1) AS gnum
FROM 貸借対照表 AS x
, 貸借対照表 AS y
WHERE x.勘定科目コード Between 1001 And 3001 Or x.勘定科目コード=9002 Or x.勘定科目コード=9004 Or x.勘定科目コード=9005
AND y.勘定科目コード Between 1001 And 3001 Or y.勘定科目コード=9002 Or y.勘定科目コード=9004 Or y.勘定科目コード=9005
AND x.勘定科目コード \ 1000 = y.勘定科目コード \ 1000
AND x.勘定科目コード >= y.勘定科目コード
AND Abs( x.[金額(資産)] ) > 0
AND Abs( y.[金額(資産)] ) > 0
GROUP BY x.勘定科目コード
, x.勘定科目
, x.[金額(資産)]
, x.[金額(負債)]) AS 貸借対照表
GROUP BY 貸借対照表.gnum
ORDER BY 貸借対照表.gnum;
返した結果が下記になります
勘定科目コード 勘定科目 金額_資産 勘定科目コード2 勘定科目2 金額_負債 gnum
9005 減価償却累計額 0 37
9004 立替金 70000 3001 預り金 50000 73
gnumの列を見ると並びがきれいに並ばなくなります。
何卒教えて頂きたくお願い致します。
思った通りの形になりました。
本当にありがとうございました。
誤った値を入力した場合に、処理を継続し、データがない状態を表示することは必要ですか？
テキストボックス「年月」の書式に「yyyy/mm」と設定してしまえばありえない数値の入力は防げます（ありえない数値を入力するとエラーが発生し入力が確定できなくなります）
売上金額を3列に増やして各列に条件を付けて集計しようということです
t売上明細を元に一部抜粋すると次のようなデータを集計(合計)する形になります
返信ありがとうございます。
売上金額フィールドの複製 が勉強不足で良く分かりません。
新規のクエリで上記コードをそのまま貼り付けてみましたが
受注ID別に表示させるための 「like "18170*"」 の入力場所がよく分かりませんでした。
18170の現場を表示させる時には
---------前月迄売上 当月売上 累計
18170-1
18170-2
18170-3
というように表示させたく、売上明細にはそれ以外にも複数の受注IDの売上が入力されています。
WHERE句を使わずに、売上金額フィールドを複製してそれぞれに抽出条件を設定し集計するといいと思いますよ
( 続き )
■ 損益計算書
■ SQL
■ 結果
x と y は両方ともに 損益計算書 のことですが
既にご理解いただいてるように、自己結合 を駆使した場合は
同じ名前のオブジェクトを複数使用することになりますから
各々を区別するため、ユニークな名前を付与する必要があります。
はい。その通りです。
Microsoft Access では、エイリアスの利用において
FROM句の As は省略可能で SELECT句 の As は省略不可
という仕様になっています。
テーブル名でもクエリ名でもないのですが、意味合いとしてはクエリになります。
この部分で、一つの完結したクエリ( SQL文 )になり
Accessにおいても、名前を付けて保存する ことが可能です。
もちろん、保存せずに使用することも可能で
その場合は、インラインビューやサブクエリという言い方をします。
今回の場合だと、インラインビューになっていて
q というのは、インラインビューに付けた名前です。
このビューの役割は
4000番台 のコード と 5000番台 のコードそれぞれにグループ連番を付与することですが
累計計算も同時に出来たりしますから
以下にデモンストレーションを掲載しておきます。
mayu様
前回に引き続きありあとうございます。
前回はmayu様の教えて頂いた集約関数のやり方で解決させて頂きましたので、
こちらの方はをあまり見ずに申し訳なく思っています。
上記について教えて頂きたく。
q=テーブルorクエリ名
x.yは何のテーブルorクエリに該当するのでしょうか？(自己結合というもの？)
それにちなんでfrom句の損益計算書 x,損益計算書 yは損益計算書 As x,損益計算書 As yを単純に省略してるだけ？
もしくは最初のx yと関係してる？
お手数ですが宜しくお願い致します。
前回の回答、2020/01/25 (土) 09:05:50 に記述した SQL へ
金額が 0 ではない科目が表示対象になるよう 抽出条件を加えれば
ご希望の結果になるでしょう。
下記と同じ内容の質問ですね。下記で回答が付いていますのでそちらで続けてください。
Access - Accessのショートカット配布可否について｜teratail
アドバイスありがとうございます。
確かに2003はセキュリティ面でも厳しいので、さっそく2019を購入しました。
OutputメソッドでPDFは簡単にできました。
あとは、メール添付の方を、いろいろと試してみたいと思います。
ありがとうございます。
初歩的なミスでお手数をお掛けしました。
何度もすみせんでした。
原因は、ウィンドウモードを「ダイアログ」に設定していることですね。
ダイアログで開くとそのウィンドウ以外は操作できなくなります。
ウィンドウモードを「標準」に変更してください。
【レポートＢを開く】ボタンは埋込マクロにしています。