Public Sub CloseAllForm()
Dim i As Long
For i = Forms.Count - 1 To 0 Step -1
If Forms(i).Name <> "フォームA" And Forms(i).Name <> "フォームB" Then
DoCmd.Close acForm, Forms(i).Name
End If
Next
End Sub
PARAMETERS 基準日を入力 DateTime;
SELECT A.連番, DateAdd("m",1-[B].[連番],[基準日を入力]) AS 対象月
FROM T_連番 AS A, T_連番 AS B
WHERE A.連番>=B.連番;
出力例（パラメータに「2018/06/01」と入力）
連番
対象月
6
2018/01/01
6
2018/02/01
6
2018/03/01
6
2018/04/01
6
2018/05/01
6
2018/06/01
5
2018/02/01
5
2018/03/01
5
2018/04/01
5
2018/05/01
5
2018/06/01
4
2018/03/01
4
2018/04/01
4
2018/05/01
4
2018/06/01
3
2018/04/01
3
2018/05/01
3
2018/06/01
2
2018/05/01
2
2018/06/01
1
2018/06/01
このクエリと労働時間データテーブルを結合してクロス集計クエリを作成すれば希望の結果になります。
TRANSFORM Round(Avg([時間]),1) AS 式1
SELECT 労働時間データ.氏名
FROM 労働時間データ INNER JOIN Q_月繰り返し ON 労働時間データ.対象月 = Q_月繰り返し.対象月
GROUP BY 労働時間データ.氏名
ORDER BY Q_月繰り返し.連番 DESC
PIVOT Q_月繰り返し.連番;
PARAMETERS 基準日を入力してください DateTime;
SELECT
氏名,
Round(Avg(時間),1) AS 6ヶ月平均,
Round(Avg(IIf(対象月>=DateAdd('m',-4,[基準日を入力してください]),時間,Null)),1) AS 5ヶ月平均,
Round(Avg(IIf(対象月>=DateAdd('m',-3,[基準日を入力してください]),時間,Null)),1) AS 4ヶ月平均,
Round(Avg(IIf(対象月>=DateAdd('m',-2,[基準日を入力してください]),時間,Null)),1) AS 3ヶ月平均,
Round(Avg(IIf(対象月>=DateAdd('m',-1,[基準日を入力してください]),時間,Null)),1) AS 2ヶ月平均,
Round(Avg(IIf(対象月=[基準日を入力してください],時間,Null)),1) AS 当月
FROM 労働時間データ
WHERE [対象月] Between DateAdd('m',-5,[基準日を入力してください]) And [基準日を入力してください]
GROUP BY 氏名;
select
氏名,
round(avg(時間),1) as 6ヶ月平均,
round(avg(iif(対象年月 between DateAdd('m', -4, cdate([基準日を入力してください])) and cdate([基準日を入力してください]),時間,null)),1) as 5ヶ月平均,
(各ヶ月)
from 労働時間データ
where 対象年月 between DateAdd('m', -5, cdate([基準日を入力してください])) and cdate([基準日を入力してください])
group by 氏名
hatena様有り難うございます。
ご指摘のようにしましたが、抽出条件でデータ型が一致しませんと出ます。
宜敷お願いします。
>>申請中: Not [申請日] Is Null
レポートのグループ化の機能で、このフィールドでグループ化すればいいでしょう。
以上のように対応はしています。
失礼いたしました。
手順のミスでした。
アドバイスいただいた通りに動作いたしました。
一対多の入力フォームは、メインフォーム（一側データ）にサブフォーム（多側データ）を埋め込む形にします。
軽く依頼があった場合は、
メインフォームで新規レコードへ移動して、主キー（受注番号など）と仮受注日、顧客コードは確定させて、金額など未確定のものは未入力(Null)にしておきます。そして、サブフォームで下請けへの発注データを入力します。
というような設計になると思います。
下請けのデータを入力するときに、関連付けするべき一側のデータを決めておかないと、
どれとどれが対応するかは後から判断できないですよね。
テーブル設計の一例
（そちらの詳しい業務内容が分からないので想像ですので参考程度に）
受注テーブル
受注番号 主キー
仮受注日
受注先顧客コード
受注内容
本受注日
金額
下請発注テーブル
下請発注ID 主キー オートナンバー型
受注番号 受注テーブルとリンクする外部キーフィールド
下請顧客コード
下請発注日
下請内容
金額
下請けデータを入力する前に、受注テーブルの受注番号、仮受注日、受注先顧客コードは確定しておく必要があると思います。
電話などで軽く依頼があり
そのときに下請けに発注することがあります
下請けには先に払う（入力）することがあり
あとから取引先から正式な受注データがくることがある、といった感じです
また、受注データは1案件扱いで10万だったとして、それは月末などに確定します
それの案件を自社と下請けで分担してやるので
先に下請けのデータだけを入力する場合がでてしまいます
1案件に対して複数の下請けが存在します
例えば
今日、複数の下請けのデータを入力したとします
月末に受注データを入力します
受注データを入力するときに、自動的に対応する下請けの
データを関連付けることは可能なのでしょうか？
その月かつその取引先からの業務のすべての下請け金額の小計を差し引いて粗利を表示するといったものです
文章だとわけわかりませんよねえ
ということなら、下記でどうでしょうか。
stFilter に下記のような条件文が格納されるように考えましょう。
本部 In(1,2) And ([申請日] Is Null Or [申請日]=#2019/04/01#)上記の意味は、(本部が１か2) かつ (申請日が未入力 または 申請日が2019/04/01)
ということです。() 内が優先順位が高いということを利用します。
ということで、下記でどうでしょう。
早速の返信ありがとうございます。説明不足で申し訳ありません。
伝票テーブル
伝票ID 伝票名
1 請求書
2 請求書（控）
クエリで材料、数量・金額などの明細の後に上記テーブルを入れ、交互に出すようにしてあるので請求書の内容は全く同じでテキストボックス（コントロールソース 伝票名）だけ名前が変わるようになっております。
それようのフォームを作り
DoCmd.PrintOut acPages, Me!txt開始ページ, Me!txt終了ページ
を挿入するぐらいしかわかりません。
正は1ページ目にでるので1ページ目だけ指定とかできるのでしょうか
hatena様早速のお返事ありがとうございます。おっしゃるとおりにしましたが
申請日がないものについて表示されません。
上記の式のなかで
stFilter = "本部 In(" & Mid(stFilter, 2) & ")And ([申請日]=#" & [TXT申請] & "#) )"
を
stFilter = "本部 In(" & Mid(stFilter, 2) & ")And ([申請日]=#" & [TXT申請] & "#) )
OR 本部 In(" & Mid(stFilter, 2) & ")And ([申請日]=" & IS NULL([TXT申請]) & ") )"
なんて素人考えでやってますがうまくいきません。
やりたいことはレポートに中からTXT申請日に記入のある申請日と 申請日の記入がない申請日を
選びたいのですが。宜しくご教示のほどお願いします。
この仕組みがどうなっているか不明ですので、推測での回答になりますが、
OpenReportの引数で抽出条件で"（正）"のみ抽出するようにはできませんか。
レコードソースのフィールド名が不明なので、推測ですが、
どのようなエラーがでますか。エラーメッセージを提示してください。
また、同じデータ、同じ追加先テーブルで、手動で保存済みインポート定義を使ってインポートできるかも確認してください。
すみません、追記です。
strTblNameのテーブル定義は、必要な10のフィールドに絞っものを使っています。
すみません、業務が立て込んでおり確認とご報告が遅くなってしまいました。
一度だけ手動でインポート定義を作成して保存しました。
インポート定義の名前："CSVインポート"
この定義を使って下記のようにインポートできるのでしょうか？
strTblName：ここは固定のテーブル
strCSVFilePath：インポートするファイル名は変数で与えるため可変です
DoCmd.TransferText acImportDelim, "CSVインポート", strTblName, strCSVFilePath, True
何度かトライしましたが、エラーが出てうまくインポートできませんでした。
ひとつのレポートに 申請中 のものと 未申請 のものを表示したいということでしょうか。
２つのレポートが同じレイアウトなら、
レコードソースのクエリに、下記の演算フィールドを設けて、
申請中: Not [申請日] Is Null
レポートのグループ化の機能で、このフィールドでグループ化すればいいでしょう。
どうもありがとうございます。
自分のやり方に自身がなかった、
あるいは、もしかしたら私の思いもつかないやり方があるのかも？と思って相談させていただきましたので、hatena様もそのようにされるというのをお聞きできて大変参考になりました。
なるほど、初めて入力する場合も考慮すると式は退避させておく必要はありますね。
ならば、自分がするとしてもそのような設計になると思います。
他に、計算列以外を1列ずつCopyFromRecordsetという方法も考えられますが、列数が多いと遅くなりそうです。
ありがとうございます。
最終行に追加する場合は、既存の関数列にデータが残っているので、それでもいいと思いますが、初期のデータ追加の場合も考慮すると、関数が、消えてしまうので、
統一的なロジックとして、そのように対応したつもりです。つまり、一番上の関数が消えてしまわないように、ワークに残しました。
hatena様
思い通りの仕組みになりました！
本当にありがとうございます。
下記のコードでどうでしょう。
Forms(0から始まるインデックス) で開いているフォームを参照できます。
インデックスの最後から削除しているのは、前から削除すると削除したとき、インデックスが前にずれるので、
削除されずにのこるフォームが出てくるためです。
エクセルのカラム名と１行目に式があるかどうかをチェックしながらSQLが生成できたら、
それをレコードセットとして開いて、CopyFromRecordsetで貼り付ければいいですよね。
計算列はNullの演算フィールドにしておけばいいでしょう。
貼り付けた後、計算列はオートフィルで上から埋めるようにすればいいのでは。
ワークテーブルがなぜ必要なのでしょうか。
ご回答ありがとうございます。
一つ目のご質問ですが、最終行以下に追記します。また、テーブル定義はしていません。
二つ目のご質問ですが、後者のご認識の通りです。列数は一致しませんし、並び順も一致しません。
どうぞよろしくお願いします。
仕様に関していくつか曖昧な点があるので確認させてください。
Excelのシートには既にデータや計算式が入力されているようですが、
Accessのデータは、シートの最終行の後に追記するということでしょうか。
また、Excelのシートのデータ範囲にテーブル定義をしてますか。
AccessとExecelのカラム名の数も一致してますか。
それともExecelにはあるがAccessにはないカラム名が存在しますか。
例えば、「計算列（ユーザー任意）」はAccessには存在しないのでしょうか。
ちょっとよく分からないのですが、
実際の取引では、
受注してから、それを下請けに割り振るという形になると思います。
受注者からの入金、下請けへの支払いが前後することがあったとしても、
受注 → 下請け の順は変わらないと思いますが、どうなんでしょう。
受注があったら、その時点で「受注番号」とかの主キーになるものを入力します。
それを下請のテーブルの外部キーとするような設計にすればいいと思いますが。
金額が未定の場合は未入力にしておいて、後から入力しても問題ないと思います。
いろいろなやり方があると思いますが、
売上テーブルは分けない方がいいと思います。
なるべく最小の単位で1レコードとします。
ということなら、集計結果の45はテーブルにはしません。
上記の3レコードが1グループだと分かるようなフィールドを追加します。
例えば、
売上先 日付 数量 委託先
あ 4月4日 10 A社
あ 4月4日 15 A社
あ 4月4日 20 B社
というような感じです。
そちらの業務内容が分かりませんので、あくまで一例です。
グループ化できる項目があるはずですのでそれをフィールドに追加します。
売上先 日付 数量 委託先
あ 4月4日 10 A社
あ 4月4日 15 A社
あ 4月4日 20 B社
い 4月4日 30
というように１売上1レコードなら1グループに1件だけにしておけばいいです。
これを、集計クエリで、売上先、日付 でグループ化すれば
売上先 4月4日 45
という結果を取得できます。
委託先への支払いもクエリで委託先でグループ化して希望の形になるようにすればいいでしょう。あるいは、レポートのグループ化の機能を使えば、合計と詳細を同時に表示させることも可能です。
テーブルは最小単位のデータを１レコードとする。
集計結果や演算結果はクエリで加工する。
あるいは、レポートを使って見やすいようにレイアウトする。
という方針にすればいいと思います。
一応、解説しておきます。
[入金額]-[出金額] とすると片方がNullだと、演算結果もNullになってしまいます。
その結果、DSumで集計すると 0 になります。
Nz([入金額],0)-Nz([出金額],0) とNullを0に変換することで、数値として演算できます。
早速のご指導ありがとうございます。
計算がされるようになりました。
Dsum関数とNz関数
理解してたつもりですが、
これを機会に、また少しづつ勉強したいと思います。
まずはお礼にて！
前年繰越のテキストボックスのコントロールソースの式を、
としたらどうでしょうか。
取り込む10のフィールドは決まっているのですよね。
ならば、一度だけ手動でインポート定義を作成して保存しておけば、後はそれを使って Docmd.TransferText すればいいでしょう。インポート定義では、インポートする/しないの選択、データ型の指定もできますので、日付/時刻型に設定しておけばいいでしょう。
日付/時刻型なら、書式設定で、yyyy/m/d でも、和暦表示でも、お好みの表示にすることができます。
ありがとうございます。
おっしゃる通り、元になるファイルから必要ない列のみ残したものを、実際にインポートしてテーブル定義を作成しました。
元のファイルは列数が250以上ありまして、インポートの定義でインポートするしないを設定するのは大変かなと思ってしまいまた。実際に使う列は250のうち10程度です。
データも、一月4万件のデータを12ヶ月分洗い替えでインポートしなければならないため、事前に加工しようと考えました。
そのようにやっているのですが、日付データの形式が変わってしまいます。
テキストファイルに対してオートメーションってできました？ちょっとどのようにしているのか分かりません。
csvファイルの列構成が固定で、削除する列も固定なら、まずは手動でインポートして、そのときに、データ型を設定したり、インポートしない列を設定できますので、その設定をして、インポート定義として保存します。
その後、
Docmd.TransferTextの引数でインポート定義名を指定してインポートすればいいかと思います。リフレッシュで回復できましたか。それはよかったです。
リフレッシュでは回復できない場合もありますので、定期的なバックアップは必須です。
コードの大きな改変する前にもバックアップは取っておくといいでしょう。
エラーが起きる直前にDo Loop で無限ループを起こさせてしまいまして、強制中止させています。
たぶん、それが原因ではないかと思います。教えて頂いたリフレッシュを実行したところ、module2が破損しているメッセージがでたので、ああこれが原因かと思った次第です。参照設定は再設定する必要があり、奇妙な現象がすこし起きていますが、なんとかなると思います。ありがとうございました。
それまては正常に動作していたのが、突然エラーが出るようになったのでしょうか。
まずは、下記のページのリフレッシュを実行してみてください。
AccessのDBファイルを長期的に安定して使用するには - hatena chips
それでだめなら、正常動作していたときのバックアップを使用するようにしてください。
バックアップをとってないなら、下記の方法で復元できる場合があります。
Windows 10：以前のバージョンって？－使い方教えて！
hatena様
ありがとうございます。
前者です。Avg関数はnullを無視する仕様知りませんでした。勉強になりました。
これで解決です。どうもありがとうございました。
データがない場合は、どのようにしたいのでしょうか。
例えば、1月、2月、5月、6月 のデータがある（3月、4月がない）場合の6か月平均は、
(1月+2月+5月+6月)/4ですか。
あるいは、ない月は 0時間として、
(1月+2月+0+0+5月+6月)/6ですか。
前者なら、
Avg関数はNullを無視しますので、前回の回答のSQLでOKなはずです。後者の場合は、レコード無しだと複雑になるので、入力するときに、労働がない月も 0時間として入力するようにすれば、前回の回答でOKです。
macof様、hatena様
ありがとうございます。
格段にパフォーマンスがアップいたしました。
ご指摘の通り、データがない場合、
平均値が正しく計算されないですね。
気づきませんでした。、
sumしてデータ件数で割るとした場合、
ゼロもしくはnullでないデータ件数はどう判定したらよいですか？
すみません、別案はまだ私の理解が及ばず試せていません。
別案
下記のテーブルを作成します。
T_連番
下記のクエリを作成。
Q_月繰り返し
出力例（パラメータに「2018/06/01」と入力）
このクエリと
労働時間データテーブルを結合してクロス集計クエリを作成すれば希望の結果になります。(名前入れ忘れてました)
hatena 様
お手数かけて申し訳ありません。
まさにその辺修正しようと思って来たんですがもう直していただいていたとは…
パラメータの型指定もそんな形で出来たんですね。
私も名前なしさんと同様のSQLにします。
名前なしさんと同じロジックですが、、、
変更点。
>=だけでいい。スマホで書いてるので検証してなくて申し訳ありませんが
単純に今の書き方を踏襲するなら以下でいけるんじゃ無いですかね(年月のカラム名は対象年月が正しいとして)
select
氏名,
round(avg(時間),1) as 6ヶ月平均,
round(avg(iif(対象年月 between DateAdd('m', -4, cdate([基準日を入力してください])) and cdate([基準日を入力してください]),時間,null)),1) as 5ヶ月平均,
(各ヶ月)
from 労働時間データ
where 対象年月 between DateAdd('m', -5, cdate([基準日を入力してください])) and cdate([基準日を入力してください])
group by 氏名
でもこれ対象者のデータが抜けると平均にならない部分が出てくるので、
個人的にはsumして割る方が良いように思います
手元の過去ログを探してみましたが、該当の質問がどれか分かりませんでした。
前回分ができていなるら、前々回も同じように作成すればできると思うのですが？
現状、どのようなフォームを作成していて、どこで躓いている説明していただけますか。