>> 9
SQLは苦手です。メモさせて下さい。
原本と原本コピーがあり、原本の開始日をマイナス1、原本コピーの終了日と突合してみたり、原本の終了日をプラス1、原本コピーの開始日と突合してみたり。最終的に左外部結合。原本は一致レコードと不一致レコード、原本コピーは一致レコードとNullレコード。
そして「T2.終了日 Is Null」と「T3.開始日 Is Null」の部分で不一致クエリ。空欄、先頭の開始日、最後の終了日もひっくるめて判定。
氏名あああの場合、空欄の不一致判定を除外する為に、「And Not (T1.開始日 Is Null」と「And Not (T2.終了日 Is Null」が必要。
氏名いいいの場合、先頭の開始日と最後の終了日の不一致判定を除外する為に、「And Not (…T1.開始日 = DMin」と「And Not (…T2.終了日 = DMax」が必要。
Private Sub Report_Open(Cancel As Integer)
'開始位置入力 全角OK 漢字等は0になるが結果的に最初の位置から印刷
' [キャンセル]ボタンをクリック、または空欄の時は印刷しない
Dim res As String
res = InputBox("印刷開始位置を入力してください(1～)", , 1)
If res = "" Then
Cancel = True
Else
s_position = Val(StrConv(res, vbNarrow))
End If
End Sub
SELECT T1.*,
T2.終了日 Is Null And
Not (T1.開始日 Is Null OR T1.開始日=DMin("開始日","テーブル1","氏名='" & T1.氏名 & "'")) AS 開始日非連続,
T3.開始日 Is Null And
Not (T1.終了日 Is Null OR T1.終了日=DMin("終了日","テーブル1","氏名='" & T1.氏名 & "'")) AS 終了日非連続
FROM
(テーブル1 AS T1 LEFT JOIN テーブル1 AS T2 ON (T1.開始日-1=T2.終了日) AND (T1.氏名=T2.氏名))
LEFT JOIN テーブル1 AS T3 ON (T1.終了日+1=T3.開始日) AND (T1.氏名=T3.氏名);
「販売管理システムで学ぶモデリング講座」の目次
教えていただいた方法を試したところ、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
>> 9
SQLは苦手です。メモさせて下さい。
原本と原本コピーがあり、原本の開始日をマイナス1、原本コピーの終了日と突合してみたり、原本の終了日をプラス1、原本コピーの開始日と突合してみたり。最終的に左外部結合。原本は一致レコードと不一致レコード、原本コピーは一致レコードとNullレコード。
そして「T2.終了日 Is Null」と「T3.開始日 Is Null」の部分で不一致クエリ。空欄、先頭の開始日、最後の終了日もひっくるめて判定。
氏名あああの場合、空欄の不一致判定を除外する為に、「And Not (T1.開始日 Is Null」と「And Not (T2.終了日 Is Null」が必要。
氏名いいいの場合、先頭の開始日と最後の終了日の不一致判定を除外する為に、「And Not (…T1.開始日 = DMin」と「And Not (…T2.終了日 = DMax」が必要。
念の為に確認ですが、クエリを開いて時間でソートをかけると時間順になるけど、レポートのレコードソースに設定して、記事のように組んで横並びにするとソートなしのレコード順になる、という事でしょうか？うっかり、テーブルをレポートのレコードソースに設定していたりしませんか？
すみません、クエリをデザインで開くと(T1.開始日-1=T2.終了日) この部分が消えてしまう？んですかね。
上記のあやまったフラグは出なくなりました。
細かいチェックがまだできていませんが、こちらで恐らく大丈夫なような気がします。
どうもありがとうございました。
厳密なチェックを要するものではありませんので、
hatenaさまのSQLを利用させていただいております。
1点だけ要件を加えさせていただいております。
T1からT2, T3に外部結合する際、氏名、出向元、出向先フィールで結合をするように修正し、
重複レコードが作られるので、Distinctをしております。
演算フィールドのDMin条件には、氏名、出向元、出向先が一対するものと修正いたしました。
データシートは表示できるのですが、あいまいな外部結合が含まれるため、SQLが表示されません。
元データ
| 氏名 | 出向元 |出向先 | 兼務率 |開始日 | 終了日 |
| -------- | -------- | -------- | -------- | -------- | -------- |
| サンプル | AAA | あああ | 50% | 2017/1/1 | 2022/3/31|
| サンプル | AAA | ううう | 50% | 2017/1/1 | 2022/3/31|
| サンプル | AAA | いいい | 0% | 2021/12/1| 2022/3/31|
| サンプル | AAA | ううう | 100% | 2022/4/1 | |
| サンプル | AAA | あああ | 0% | 2022/4/1 | |
SQL実行結果
| 氏名 | 出向元 |出向先 | 兼務率 |開始日 |終了日 | 開始フラグ |終了フラグ|
| -------- | -------- | --------| ---- | -------- | -------- | -------- | -------- |
| サンプル | AAA | あああ | 50% | 2017/1/1 | 2022/3/31 | 0 | 0 |
| サンプル | AAA | ううう | 50% | 2017/1/1 | 2022/3/31 | 0 | 0 |
| サンプル | AAA | いいい | 0% | 2021/12/1| 2022/3/31 | 0 | 0 |
| サンプル | AAA | ううう | 100% | 2022/4/1 | | 0 | 0 |
| サンプル | AAA | ううう | 100% | 2022/4/1 | | -1 | 0 |
| サンプル | AAA | あああ | 0% | 2022/4/1 | | 0 | 0 |
本来、エラーがでないはずなのですが、下から、2行目の重複レコードが生じ、開始日非連続にフラグが立ちます。
原因がよく分からず、よろしくお願いいたします。
ご回答いただきありがとうございました。
他のシステムに入れるため、うまく連携できそうな方でやってみます。
レポートでの出力でいいのなら、下記で紹介している方法もいいでしょう。
下記のようにグループ内連番の列を追加すればクロス集計クエリでできます。
連番は下記で紹介している方法をどれかを使えばいいでしょう。
みなさま
申し訳ございません。忙殺されていまして、確認が遅くなりました。
アドバイスを試してみて、後程フィードバックさせていただきます。
ひとまずご連絡まで。
そういう表ってできましたっけ…？
クロス集計なら、たとえば
のような表はできますが、それだとダメでしょうか？
バッチリうまくいきました。大変助かりました。
似たようなコードで一度実行していたのですが、（？）
引数のエラーが出ていました。
「Cancel = True」というのが必要だったのですよね。
ありがとうございました。
レポートの幅が印刷範囲幅より大きいのではないでしょうか。
デザインビューでレポートの幅を、[用紙幅]－[左右余白]より小さくしてみてください。
DoCmd.OpenReport "R_ラベル印刷", acViewPreviewの前に、
On Error Resume Nextを挿入するといいでしょう。
タブコントロールは見た目は入れ子になってますが、オブジェクトの構造には何も影響ありません。フォームの関係性だけ気を付けてコードを組めばいいです
「ボタン」がメインフォーム側にある場合
「ボタン」がサブフォーム側にある場合
お返事ありがとうございます。
私個人としては Access, Excel, VBA でたいていのことはできると思っていますが、
ローコード、ノーコードの流行の中でITに明るい事務員から質問されて困ったり、
ITに明るい事務員がささっと作ったもののほうが丁寧に開発した Access に優ったり
ということが起こらないか気になり聞かせてもらいました。
私も「マスターするほど勉強しな」さそうですが、
しかし可能性を把握できるところまで掘り下げたいとは思っています。
キャリアプランの件おっしゃるとおりですね。
身の振り方は悩ましいところですが……
お世話になります。
別業務が多忙となり随分時間がたってしまいました。申し訳ございません。
りんご様、ありがとうございます！
やりたいことが完璧にできました！
こちらのサイトには非常にお世話になっています。
いつも的確なご回答非常に感謝致します。
hirotonさんが厳密なエラー例を出してますが、
上記のクエリは、このなかの、2. 3. 6. 7. はエラーになりません。
すべてのエラーに対応するなら、hirotonさん同様VBAを検討しますね。
とりあえず下記でどうでしょう。
出力結果例
ただし上記のクエリは更新不可になりますので、
この結果を見ながらテーブルを修正することになります。
「氏名」だけのチェックでいいのであれば
AND T1.会社=T2.会社を削除すればいいです。そもそも「氏名」「会社」の判定部分は本題とはずれた話ですね。同姓同名問題があるので破綻します。「個人」を特定するためにユニークなキーを設定しましょう日付で起こりうるエラー
エラーとはどのようなデータのことなのか明確にしてhatenaさんが提示したようなSQLを作ってつなげればいいと思いますよ
SQLでやると起こりうるエラー毎にテーブルの参照が必要になるんでhirotonならVBAでエラーレコードの拾い出しにしそうですけどね
アドレスありがとうございます。
サンプルはエラーの発生が無い想定でおだししています。
実データが、終了日4/10日、次の開始日4/12日だった場合、どらのレコードもエラーとしたいです。
正しいデータはいくつかの可能性がありますが、そこは気にせず、単につながりがないレコードを抽出し、
人間が確認をして、正しいデータに修正をすることで、結果、エラーが回避されれば良いと考えています。
運用面で、9999/12/31を使うとか、複合キーの設定も検討はしてみたいと思います。
趣味で遊んでいるだけなのであれですが。
リレーションシップなんやらでExcelの加工・整形をやりたいわけではないので、マスターするほど勉強しないと思います。
キャリアプランに沿って、必要なものを取り入れていくだけではないでしょうか。
終了したら翌日には開始、空白が生じたらエラーという事でしょうか。例えば、終了日4/10日、次の開始日4/12日の場合、どちらをエラーと判定するのでしょう。正しくは、終了日4/11日、次の開始日4/12日、それとも、終了日4/10日、次の開始日4/11。あるいは、終了日と開始日、どちらもエラーなんてことも。
9999/12/31で登録しておくとか。
氏名と開始日に複合主キーを設定して、登録の仕組みを整えるといいかも。
試していませんが、氏名と開始日で外部結合すれば、会社違いのエラー判定を回避できるのかも。
| 氏名 | 会社 | 所属 | 開始日 | 終了日 |
| ううう | ZZZZZ | 法務 | 2020/4/1 | 2021/3/31 |
| ううう | TTTTT | 法務 | 2021/4/1 | 2022/3/31 |
| ううう | EEEEE | 法務 | 2022/4/1 | NULL |
試したところ、なかなかうまく判定ができていません。
例えば、こんなケースもありまして、これはエラーではないと判断したいです。
出向等をして、1レコードで完結している場合など。
よろしくお願いいたします。
hatena様
なるほど、開始日の１日前の終了日が存在しないという条件で判定をするのですか。
どうもありがとうございます。明日、早速試してみます。
下記のSQLで 開始日の１日前の終了日が存在しない場合、チェックが True になります。
コマンドを変数にすることはできません。プロシージャとして宣言しておいて、そのプロシージャを呼び出すようにします。
可能です。
というかなぜ、変数にできないと思ったのか自体が不思議です。
具体的なコードを提示してどのように困っているかの説明をお願いします。
色々とｱﾄﾞﾊﾞｲｽありがとうございました。大変参考になりました。
まずShell関数でexcel.exe のところをﾌﾙﾊﾟｽにして動作させると”実行時ｴﾗｰ53”となりました。指定ﾌｧｲﾙも記述せずExel起動だけでも同様でした。試しにnotepad.exe(ﾌｧｲﾙ指定せずに)だとﾒﾓ帳は起動しました。不思議です。
それでShellを諦めて下記の１でExcelを起動させて ２で指定ﾌｧｲﾙを開くと、上手くいきました。
１：『 Dim oApp As Object
Set oApp = CreateObject("Excel.Application")
oApp.Visible = True
'Only XL 97 supports UserControl Property
On Error Resume Next
oApp.UserControl = True』
２：『oApp.Workbooks.Open Filename:="C:○○△△.xls"』
Shellで起動が出来ないのは不明ですが、何とかこれで取りあえずの対処でします。
WIN10／Office2016(ACCESS2016入り)環境の別PCがありまして、それで現行のmdbﾌｧｲﾙを試した事があるのです。そうするとVbaｺｰﾄﾞが無反応となるｺﾏﾝﾄﾞが多くあったので（mbd形式、accdb形式に変換後も）、それに移行するのが億劫で古いﾊﾞｰｼﾞｮﾝを使い続けている次第です。でも先々考えると行き詰まってしまいますね。今後何とかそれを新しいもので使える様にしなくてはと今回感じました。
色々と丁寧にｱﾄﾞﾊﾞｲｽ頂き、大変ありがとうございました。