TRANSFORM Sum(業務日報.月内合計売上額) AS 合計売上額
SELECT "合計売上額" AS 担当者名
FROM 業務日報
WHERE 業務日報.登録日=DMax("登録日","業務日報")
GROUP BY "合計売上額"
PIVOT 業務日報.担当者名 In ("鈴木","山田","伊藤","田中");
SELECT
T1.ID, T1.日付, T1.内容, T1.開始, T2.終了
FROM
T_日報マスタ AS T1 INNER JOIN T_日報マスタ AS T2
ON (T1.日付 = T2.日付) AND (T1.内容 = T2.内容) AND (T1.終了 = T2.開始) ;
クエリ名 Q_日報マスタ
SELECT
T1.日付, T1.内容, Min(T1.開始) AS 開始, Nz([Q1].[終了],[T1].[終了]) AS 終了
FROM
T_日報マスタ AS T1 LEFT JOIN Q_日報1 AS Q1
ON (T1.開始 = Q1.開始) AND (T1.内容 = Q1.内容) AND (T1.日付 = Q1.日付)
GROUP BY
T1.日付, T1.内容, Nz([Q1].[終了],[T1].[終了])
ORDER BY
T1.日付, Min(T1.開始);
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 ;
hatena様
お返事ありがとうございます。
>左上に開く場合は、Accessウィンドウを中央に移動させてから終了してください。
>次回からその位置で開くはずです。
確かに終了時の場所で開きました。
この場合ユーザーは一人だと問題ないのですが、私が作成しているデータベースは10人以上で共有しています。なので、
>左上に開く場合は、Accessウィンドウを中央に移動させてから終了してください。
が徹底できないリスクがあります。
できれば、位置移動不可、サイズ変更不可としたいのです。
何か良い方法はございますでしょうか？
SetWindowPos hwnd, HWND_TOP, 0, 0, 800, 600, SWP_NOMOVE上記の部分で引数に SWP_NOMOVE を設定しています。これは位置は変更しないという意味です。
また、Accessは前回終了したときの位置を覚えておいて、次に開く時にその位置で開きます。
左上に開く場合は、Accessウィンドウを中央に移動させてから終了してください。
次回からその位置で開くはずです。
返答が遅くなり大変申し訳ございません。
原因は、サブフォームのソースオブジェクトが空だったことと思われます。
クエリを作成しておいて、サブフォームのソースオブジェクトプロパティに
そのクエリを入れてから、RecordSourceにSQL文を入れることで解決出来ました。
ご回答いただき、大変助かりました。
本当にありがとうございました。
度々のご回答誠にありがとうございます。
Where条件でDmax関数の第三引数に期間を指定してみましたが、演算子のエラーが出てしまい、
私の入力にミスがあるのだと思います。
アドバイスを完璧に生かせず申し訳ありませんが、かなり近いところまで来れたと思いますので、
引き続き調整してトライしてみたいと思います。
拙い私にお付き合い下さり、心からお礼申し上げます。
また、頂いた疑問について、少し込み入った業務の話になるのですが、
日々の営業活動による売上額と、別枠の売上項目があり、
各担当によって、それが有ったり、無かったりする上に、日によって増減もするので把握が難しく、
いちいち聞いて合計しているのが手間になり、この度、アクセスで管理してみたいと思いました。
初めてアクセスに挑戦しているので、躓きも多く大変なのですが、
助けて頂き本当に救われました。
これからも頑張っていきたいと思います。
期間内の最新（の1レコードのみ）を抽出するなら>> 5のWhere条件でDmax関数の第三引数に期間を指定すればできますよ
ところで疑問に思ったんですが日報で月内合計を入力するんですか？
日報は日々の売上額を入力するにとどめて合計（集計作業）はACCESSにやらせるというのが普通だと思います
この形であれば月内のデータを取得したクエリで日々の売上額に合計を設定するだけで済みますよ
引き続きアドバイスありがとうございます。
「2020年3月以内」+「各担当の最新登録日のデータ」の部分について説明不足で申し訳ありません。
最終的な目標は、昨日ご回答いただいた表に、
「月内の範囲」で「最新登録日」の「月内合計売上額」を
各担当毎に表示できるようにしたいと考えています。
（担当は６名なので、６つのレコードが並ぶようになる予定です）
毎日、各担当者に業務日報として「月内合計売上額」を入力してもらう予定です。
（「月内合計売上額」は、当月１日から、当日売上分を含めた月内合計売上額です）
売上合計額を表示するフォームは、1か月単位で区切られるものを作成しているので、
自分でもBetweenを使って、月内のデータを取得するまではできたのですが、
さらに、「月内に限った、最新の登録日」の「月内合計売上額」のみ表示するという、
絞り込みをする条件設定が分からないため、
１日から月末までのデータが、ズラッと並んで表示される結果になってしまいました。
たびたびお手数をお掛けして申し訳ございません。
アドバイスを頂いているおかげで、さらに詳しいクエリについて学ぶ機会になっています。
申し訳ございませんが、アドバイス頂ければ幸甚でございます。
「2020年3月以内」+「各担当の最新登録日のデータ」
の説明をデータ例をもとに具体的に説明してもらえますか。
とりあえず、
「登録日」が2020年3月内ということなら、
任意の年月を指定したい場合は、
丁寧なご回答誠にありがとうございます。
今回はじめてAccessを使用しているので、なかなか躓きが多く苦労しており、
アドバイス頂けて助かっています。
教えて頂いたWHERE句の期間についてですが「2020年3月以内」+「各担当の最新登録日のデータ」
を取得する設定にしたい場合はどのようになりますでしょうか？
月毎に表示フォームを分割したいと考えており、月毎に月内合計売上額を完結できたらと思っています。
重ねてご面倒をお掛けいたしますが、アドバイス頂けましたら幸甚でございます。
下記のクエリを作成します。
これのクエリをもとにフォームウィザードでフォームを作成するか、
フォームのデザインビューでレコードソースにこのクエリを設定して、
フィールドをご希望のレイアウトで配置してください。
In () の中は固定の担当者名をカンマ区切りで入力してください。
また、最新日を抽出条件にしてますが、WHERE句を変更すれば期間に変更できます。
例えば、直近１０日間なら、
ご回答ありがとうございます。
はい！ご回答いただいた表をフォームに表示したいと考えております。
担当は固定しています。（6名の間違えでした、申し訳ありません）
合計売上額は「月内合計売上額」です。
担当者は２名で固定ということですか。
フォーム表示は下記のような感じでしょうか。
合計売上額は、直近の「月内合計売上額」を表示でしょうか。
さっそくのお声がけありがとうございます。
テーブル名：業務日報
フィールド名：データID(主キー)、登録日、担当者名、月内合計売上額
表示：売上管理フォームを作成して、直近の合計売上額を表示
レイアウト：2列2行の表（１行：担当名、2行：担当毎合計売上額）
以上、よろしくお願いします。
追記です。
手順通り行ったところ、まさに希望通りの表現ができました！
グループ毎連番の記事は以前拝見しましたが、この作業に応用できるという発想には全く至りませんでした。
それに 全体連番 - グループ連番 でグループを作るとは・・・
これならレコード件数が増えても処理が重くなることがなさそうなので二重に助かります。
ご教授いただきありがとうございました。
回答するには情報不足ですので、下記の点について補足してください。
mayu様ご返信ありがとうございます。
Window 関数についてや詳細な手順まで載せて頂き大変感謝致します。
（旅人算、面白い名前ですね）
まずはご教授していただいた内容にてトライしてみます。
もし不明点あれば恐縮ですがまた質問させていただきます。
( 続き )
ここまで出来たら、あとは単純な集計クエリで表現可能です。
■ SQL
■ 結果
Window 関数は、Oracle や SQLServer など本格的なデータベースには標準で搭載されている機能ですが、
Access には移植されておらず、LAG や LEAD といった関数は、模倣するのが難しいですから
「 旅人算 」という手段を用いて実現する方法 をご紹介します。
【 手順１ 】
hatenaさんのコンテンツで紹介されている下記の関数を使いますから
ご自身のデータベースに、モジュール( SetSequenceNumber関数 )をインポートしておいてください。
【 手順２ 】
T_日報マスタ テーブルに 数値型のフィールドを２つ追加します。
追加するフィールドの名前は
全体連番
グループ連番
とします。
【 手順３ 】
追加した２つのフィールドに VBA で連番を振ります。
(
※ 全体連番には、日付 + 開始時刻 の昇順で ユニークな番号を付与します。
※ グループ連番には 日付・内容 を母集団とし、開始時刻の昇順で 集団内での連番を付与します。
)
■ set_number実行前
■ set_number実行後
追記です。
ご教授していただいた内容で、2つまでなら希望通りのクエリが作成できました。ありがとうございます。
ですが確かに3つ以上はうまくいきません。別の方法が必要なのですね。
同じ日付で内容が連続した場合、そのレコードの中から開始の最小値と終了の最大値を取得するには・・・
早々のご返信ありがとうございます。
取り急ぎ、3つ以上連続する場合もあります。
省略してしまいましたが、テーブルには下記のような内訳あります。
内容によって3つ以上になる場合があります。サンプルが情報不足で申し訳ございません…
まずはご返信された内容にて確認させていただきます。
連続するデータが２つのまでなら、下記のような二段階のクエリでできそうです。
クエリ名 Q_日報1
クエリ名 Q_日報マスタ
3つ以上連続する場合があるとちょっと面倒そうです。
早々のご返信ありがとうございます。
内容不足で申し訳ございません。
テーブル名：T_日報マスタ
主務キー：ID
これを
クエリ名：Q_日報マスタ
のような形にしたいと思っています。
前提として
同じ日付の場合、ID番号の「終了」とID番号+1の「開始」は同じ時刻が入ります。
やりたい作業：ID2、3が同じ日付（3/10）、同内容（作業）で9:00～10:00、10:00～15:00と続いているので9:00～15:00にまとめたい
ただし、ID4が違う作業（15:00～16:00に「移動」）なのでID5はまとめない
同内容の判断基準は、同じ日付、作業 でID番号の「終了」とID番号+1の「開始」の時間：分 が一致する場合です。
ご確認よろしくお願い致します。
実際のテーブル名、フィールド（名前、データ型）、主キー設定をとりあえず提示してください。
また、
例えば、同内容(連続データ)と判断する基準は、秒まで完全に一致する場合か、あるいは、隣接しているなら連続データと判断するのか。あるいは、重なっている場合があるなら、それも連続データとするのか、など。
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さん、ありがとうございます。
最初に教えて頂いた内容で何とか形になることができました。
適切なアドバイスありがとうございました😆