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 氏名
この下が囲われる？？
Function tuki(day1, day2 As Date)
’使い方 tuki([入社日],[退社日]) tuki([入社日],date())
Dim i As Long
If (Format(day1, "dd") < Format(day2, "dd")) Then
i = DateDiff("m", day1, day2)
Else
i = DateDiff("m", day1, day2) - 1
End If
tuki = i
End Function
ご回答ありがとうございます。
一つ目のご質問ですが、最終行以下に追記します。また、テーブル定義はしていません。
二つ目のご質問ですが、後者のご認識の通りです。列数は一致しませんし、並び順も一致しません。
どうぞよろしくお願いします。
仕様に関していくつか曖昧な点があるので確認させてください。
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して割る方が良いように思います
手元の過去ログを探してみましたが、該当の質問がどれか分かりませんでした。
前回分ができていなるら、前々回も同じように作成すればできると思うのですが？
現状、どのようなフォームを作成していて、どこで躓いている説明していただけますか。
ご回答ありがとうございます。
一応、フロントバックに分割してネットワーク経由で利用することを検討していまので、トランザクションも検討してみます。
共有していて複数のユーザーが同時に更新する可能性があるなら、トランザクション処理内で実行するようにした方がいいでしょう。そうでないなら、Executeメソッドで問題ないです。
お返事ありがとうございます。
申し訳ありません。ここが問題ではありませんでした。この後にもいくつか処理をしてまして、そちらが問題でデータが消えてしまっていました。そちらのロジックを見直して解決にいたりました。
重ねてお聞きしたいのですが、アクションクエリを複数連続でExecuteメソッドを使って実行していますが、このような対応で問題ないでしょうか？
念のため、同期されるというのは確認していますが、より確実に処理するような手当てがありますでしょうか？例えば、トランザクション処理の中に入れるとか？
重ねてアドバイスよろしくお願いします。
現状で、① ② ③ の処理が希望通りの動作してして、④ だけが空白データしか追加されないというのは、コードを見る限りは原因は想像つかないですね。
単独ではうまくいくんですよね。
もし、症状を再現できる、サンプルデータを含むデータベースファイルとエクセルファイルを作成することが可能なら、それを右サイドメニューのファイル送信フォームから送信していただけますか。
どうもありがとうございます。
インポートの引数はみなおします。
ご指摘通りです。
Mtbl_A_Infoは書き間違きです。
Mtbl_Aが正しいですり
の
acImportDelimはTransferTextの引数です。エクセルをインポートするなら、下記を参考に適切な引数を設定してください。Excel VBA を学ぶなら moug モーグ | 即効テクニック | データをExcelファイルにエクスポートする
あと、Mtbl_A にインポートしている感じですが、更新するときに参照しているのは、Mtbl_A_Info になっているようですが、おかしくないですか。
こちらの状況に合わせてアレンジさせていただき、
この形で仕上げることができました。理想的です！
後は誰でも使えるように、読者や本の追加・削除などの
インターフェイスを追加していきます。
また欲が出てきましたので、hatenaさんのchipsを教科書に
しつつトライしていきます。
ありがとうございました！
投稿をメール送信する設定になっていたので、メールソフトの中にはあるので、それをもとにWEBで読めるようにすることは可能だと思います。ただ、時間はかかると思いますので、しばらくお待ちください。
以前の掲示板の記事を見ることはもうできませんか？
検索から見落としてました！この形だと思います。
次のお休みでじっくり勉強させていただきます。また報告します！
下記が参考になるでしょう。
うまくいきました！！
ただ、私の説明不足が露呈しました。すみません。
仕上がりのイメージとしましては次の通りでした。
①本の登録は別に登録用フォームを作成し、そちらから数を追加する。
※なお、読者の登録も同様にする予定です。
②サブフォームには登録されている本の全てを表示する。
③読み終えたら「既読」チェック ON(OFF)をする。
今回の仕上がりはサブフォームで読者ごとに本の登録をしていく形です。
前回の「クエリをいじりすぎた」のは登録本をすべて表示したくて
色々といじった結果とも言えます。
しかし私の希望する形だと、読者の人数分×本の登録数が常に
テーブルに保存されるので、上策とは言えなさそうです……。
今回の課題は教えていただいた方法でいかせていただきます。
いつも丁寧なアドバイスをありがとうございます。
不器用ながら、更に精進します！！
クエリの設計が間違ってますね。
クエリには、T_本_読者のフィールドをすべて表示させて、T_本からは、タイトル、ジャンルを表示させます。
T_本はマスターテーブルなので、そのテーブルの主キーを変更してはいけません。マスターテーブルからは、主キー以外のフィールドを表示させるようにします。
あと、結合の向きが逆です。下記の画像を参考にして作り直してください。
サブフォームのレコードソースを作り直したクエリにします。
サブフォーム上の タイトル、ジャンル のテキストボックスは「編集ロック」「はい」にしておいてください。
マスターのデータを不用意に更新されないようにするために不可欠です。
これで、サブフォームで 本ID を入力すると 既読 には自動でチェックが入ります。
後は、本ID のテキストボックスをコンボボックスにして、ドロップダウンリストから選択できるようにしておくと使いやすいものになります。
こんにちは。アドバイスを見ながらトライしてみましたが、
うまく機能してくれません。
各テーブルとクエリとリレーションシップは画像の通りです。
イメージとしては「フォーム(表示)」のように、
読者ごとに登録されている本のリストが表示され、
「未読」のチェックボックスをON・OFFできる、というものです。
やはり「未読」が Null で出てしまいます。
私のリレーションがどこか間違っているとは思いますが、
あれこれ試しながらも行き詰ってしまいました。
再度のアドバイスよろしくお願いします。
メイン/サブフォーム形式の設計については、事前にリレーションシップを設定しておくと、フォームウィザードで簡単に作成できます。
下記が参考にしてください。
Accessのフォームのメイン/サブフォームとは | できるネット
とてもわかりやすい解説をありがとうございます！！
多対多の関係は初めてです。早速トライします。
うまくいったら改めてご報告にあがります！！
読者は複数なのですね。
ならば、
ある本を読んだ読者は複数、
ある読者が読んだ本は複数、
となりますので、
本と読者は多対多の関係になります。
この場合、データベース設計としては、
T_本、T_読者 さらに両者を結びつける中間テーブルを作成します。
T_本
T_読者
T_本_読者
読者毎に管理したいなら、下記のような設計にするといいでしょう。
T_本をレコードソースとするフォームをメインフォームにして、
T_本_読者 と T_読者 を結合させたクエリをレコードソースとするフォームを、サブフォームとして埋め込む。
年度末にも関わらず、早速のお返事ありがとうございます。
>一対一関係のデータは、テーブルを分けないで一つにするのがデータベース設計のセオリーです。
>つまり、T_本棚に未読フィールドを追加すればいいだけです。
はい、サンプルをダウンロードさせていただき参考にしながら作成しました。
それはできたのですが、作成過程で……
「読者のファイルをひとつ原本として作成しておき、
読者が増えるごと『読者原本』ファイルをコピーして
個人ファイルを作成すれば、管理・対応が楽になるのでは。
読者が減ればその個人ファイルを削除するだけで良いし、
本棚の登録には影響がない」
……と考えた次第です。T_本棚に未読フィールドを追加する場合、
共通項目となるために読者は1人専用の状態になりますから。
そこで『読者原本』ファイル側(「T_読者」テーブル)に未読フィールドを作ってやってみたのですが、
やはりセオリーから外れているから駄目なのですね……。
別の試しに「T_読者」テーブルに「管理ID」フィールドを追加して主キーとし、
一対多にしてみましたが、やはり同様でした。
ひとつのファイル内にテーブルをまとめる形(基本)に戻って再設計していきます。
いつも的確なアドバイスありがとうございます。
拙いレベルながら、職場での業務改善に貢献できる機会が増えました。
今後も精進します！！
こちらこそ、後押しありがとうございます
テーブル設計に何となく不安があったのですが、本日より作成にかかろうと思います
最初の質問は編集可能でしたがレスの編集が分からなかったのでテストさせて頂きます（汗
確かに、かなり見やすくなってますね！
掲示板とかHTML?は全然分からないので板汚す可能性大です