Microsoft Access 掲示板

なるほど、このような機能があるのですね。

サブデータシートをうっかりさわってレコードを変更してしまわないように非表示にしておきます。

サブデータシートですね。
リレーションシップを設定すると自動で設定されます。
詳細は下記を参照。

サブデータシートはテーブルプロパティで手動設定できる | Access 2019 | 初心者のためのOffice講座

表示させたくなければ、テーブルプロパティで非表示に設定できます。これも上記のリンク先で説明されてます。

お世話になります。
リレーションシップを設定したところ、マスターテーブルの下に＋が表示されるようになり、クリックすると別のテーブルのレコードがでてくるのですが、これは問題ないでしょうか？

ロジックおよび動作確認できました。
当初はDAOによるキーブレイク処理を試みましたが、
最大、最小の日付を取ろうとして、何度も書いてもダメでした。

hatenaさまのロジックは、2段階になっていて、
なるほどと思いました。

どうもありがとうございました。

hatenaさま

ありがとうございます。
私もレコードセットで回してキーブレイク処理で、
判定フラグを立てる方法を試行錯誤していたのですが、
どうしてもできずにいたところでした。

まずは、じっくり内容確認させていただきます。

SQLもいいのですが複雑になるので、私がするなら、メンテナンス性とパフォーマンスを考慮して、
テーブルに在籍期間判定用のフィールドを追加して、
VBA(DAO)でそこに在籍期間ごとの連番を入力していくようにするかな。

Public Sub SetRenban()
    Dim rs As DAO.Recordset
    Dim c As Long
    Dim pre氏名, pre組織名
    Dim strSQL As String
 
    strSQL = "SELECT 在籍期間No, 氏名, 開始日, 終了日, 組織名 FROM アクションテーブル " & _
             "ORDER BY 氏名, 開始日, 終了日;"
 
    Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset, dbFailOnError)
 
    Do Until rs.EOF
        If pre氏名 = rs!氏名 Then
           If pre組織名 <> rs!組織名 Then
                c = c + 1
                pre組織名 = rs!組織名
           End If
        Else
            c = 1
            pre氏名 = rs!氏名
            pre組織名 = rs!組織名
        End If
 
        rs.Edit
        rs(0) = c
        rs.Update
        rs.MoveNext
    Loop
 
    rs.Close
    Set rs = Nothing
 
    MsgBox "完了"
End Sub

上記の実行結果

SQL

SELECT t.氏名, t.組織名, Min(t.開始日) AS 開始日, Max(t.終了日) AS 終了日
FROM アクションテーブル As t
GROUP BY t.氏名, t.組織名, t.在籍期間No
ORDER BY t.氏名, t.在籍期間No;

SQL出力結果

終了日と次のレコードの開始日の連続性は考慮していないので、もし、連続しない(例えば休職とか)場合があるなら、
VBAにその条件判断を追加する必要はあります。

mayuさま

ありがとうございます。
これすごいですね。

SQLでここまできるとは。
結果セットも3万レコードくらいできるのですが、パフォーマンスも高いです。

ありがとうございます、作成を進めてみます！
Hatenaさん、Hirotonさん、いつも的確なアドバイス・回答をありがとうございます。

組織の在籍期間を算定しようと思っています。
⇒赤色でハイライトした箇所が正しく拾えず、黄色の箇所が結果として合っている
⇒出戻りの、更に、出戻りもある可能性もあります

方法は色々とありますけど、SQLのサンプルを載せておきます

■アクションテーブル

　
■SQL

SELECT 氏名
     , 組織名
     , 開始日
     , Min( q.終了日 ) As 終了日
FROM
(
    SELECT x.氏名
         , x.組織名
         , x.開始日
         , y.終了日
    FROM アクションテーブル x
    INNER JOIN ( アクションテーブル y
                 INNER JOIN アクションテーブル z
                         ON y.氏名 = z.氏名 and y.組織名 = z.組織名
    )
    ON x.氏名 = y.氏名 AND
       x.組織名 = y.組織名
    WHERE x.終了日 <= y.終了日
    GROUP BY x.氏名
           , x.組織名
           , x.開始日
           , y.終了日
    HAVING Sum(
               IIf( 
                       ( z.開始日 <  x.開始日
                            AND 
                         x.開始日 <= DateAdd( 'd', 1, z.終了日 )
                       )
                   OR
                       ( y.終了日 <  z.終了日
                           AND 
                         z.開始日 <= DateAdd( 'd', 1, y.終了日 )
                       )
                 , 1
                 , 0
               )
           ) = 0
) q
GROUP BY 氏名
       , 組織名
       , 開始日
ORDER BY 1, 3 ;

　
■結果

画像を見た限りでは特に問題はなさそうです。

氏名　　　　　　　開始日　　　　　　終了日　　　　　組織名　判定フラグ
青山 太郎 20140401 20140525 人事 0
青山 太郎 20140526 20140531 人事 1
青山 太郎 20140601 20150331 人事 1
青山 太郎 20150401 20170930 経営企画 0
青山 太郎 20171001 20210331 法務 0
青山 太郎 20210401 20220331 人事 0
青山 太郎 20220401 20230331 総務 0
青山 太郎 20230401 99991231 総務 1

失礼いたしました、転記ミスです。
他の条件も検討する必要があるとのことですね。

想定しているデータは>> 3の通りですが、補足しているようにデータによっては他の条件を検討する必要がでます

また、終了日を含めて一つの結果にするにはそれなりのクエリ（計算）が必要になります
質問中には「総務」のレコードは2件しかありませんので、途中結果であれ「総務」が3レコードになるのはどこかに間違いがあるのでしょう
hirotonの提案は1発で開始日、終了日両方合わせたような結果が出るようなものではないので、元データと、どのようなクエリを組んだのか確認してみてください

hatenaさま
ありがとうございます。

こちらで正しいです。アクションが空白のデータもありますし、
アクション違いで、複数の同一組織、氏名のレコードは存在します。
職種変更  　20171001   20171001    20210331    法務
      　　　20200801   20171001    20210331    法務

おっしゃる通り、こちらが正解です。
氏名    　開始日  　　　終了日      　組織名
青山 太郎　20140401  20151031　　　　人事
青山 太郎　20151101　20170930 　　　経営企画
青山 太郎　20171001　20210331 　　　法務
青山 太郎  20210401　20220331　 　　人事
青山 太郎　20220401  99991231  　　　総務

hiroton さま
ありがとうございます。
なるほどです。こんな考え方があるのですね。

1点だけ不備がありまして、赤のところが0,1逆になりました。

氏名　　　　　　　開始日　　　　　　終了日　　　　　組織名　判定フラグ
青山 太郎 20140401 20140525 人事 0
青山 太郎 20140526 20140531 人事 1
青山 太郎 20140601 20150331 人事 1
青山 太郎 20150401 20170930 経営企画 0
青山 太郎 20171001 20210331 法務 0
青山 太郎 20210401 20220331 総務 0
青山 太郎 20220401 20230331 総務 0
青山 太郎 20230401 99991231 総務 1

職種変更  　20171001   20171001    20210331    法務
      　　　20200801   20171001    20210331    法務

上記の部分は、下記のものの転記間違いでしょうか。

職種変更  　20171001   20171001    20200731    法務
      　　　20200801   20200801    20210331    法務

あと、
開始日      終了日       組織名
20140401　　20140525    人事
20140526    20140531    人事

上記のように、終了日と次のレコードの開始日が20140525→20140526 というように連続していたら、一つの在籍期間として扱うということでしょうか。

クエリの最終出力フォーマットは、下記のような感じにですか。

氏名    　開始日  　　　終了日      　組織名
青山 太郎　20140401  20151031　　　　人事
青山 太郎　20151101　20170930 　　　経営企画
青山 太郎　20171001　20210331 　　　法務
青山 太郎  20210401　20220331　 　　人事
青山 太郎　20220401  99991231  　　　総務

それとも質問のように、空欄も必要なのですか。

日付が連続するとしているならば、各組織での開始日を、不要なレコードを削除することによって求めることができます。各レコードに於いて、「『開始日』の前日が終了日であるレコード」の組織が同じ組織であれば、そのレコードは連続した組織のデータであり、開始日として不要なデータとなります

そのようなデータであるかどうかを求める場合

DCount("組織","T_アクション","組織='" & [組織] & "' AND 終了日='" & Format(DateValue(Format([開始日],"@@@@\/@@\/@@"))-1,"yyyymmdd") & "'")

このような計算をすると、各組織の開始日に当たるレコードだけ「0」の結果が得られます

※質問中のデータの場合、6行目（アクションが空白のレコード）も抽出されます

「開始日だけ」のデータが出来上がれば、「次のレコードの開始日の前日」が「終了日」です

ありがとうございました。

その機能のことを、すっかり忘れていました。

こちらで解決できるかしら？
Accessのクエリで結合した2つのテーブルのうち一方にしかないデータを抽出する方法

普通そう思いますよね。
基幹システムが全く機能していなくて、所属歴が取れないので苦肉の策です。

基幹人事システムならば、社員所属テーブルがあるはずですが？何故、クエリで捻り出さないといけないの？

心理状態（エントリー時）、心理状態（クローズ時）も同じマスターテーブル（M_心理状態）から値を拾うのですが、エントリー根拠のように別テーブルにする必要はありますか？

データが同じなら、別テーブルにする必要はないです。
リレーションシップを設定するときに、マスターテーブル（M_心理状態）を２つ追加してそれぞれと結合されせればいいでしょう。

T_トレード履歴_エントリー根拠・T_トレード履歴_クローズ根拠のテーブル作成、リレーションシップの設定をしてみましたが、こんな感じでよいのでしょうか？
フォームの入力確定時に保存用テーブル（T_トレード履歴・T_トレード履歴_エントリー―根拠・T_トレード履歴_クローズ根拠）にレコードの書込みを行いたいので、tmpとしています。

M_●●はマスターテーブルです。

画像修正しました。

ありがとうございます。Hatenaさん、Hirotonさんに提案されている正規化をやってみたいと思います。

心理状態（エントリー時）、心理状態（クローズ時）も同じマスターテーブル（M_心理状態）から値を拾うのですが、エントリー根拠のように別テーブルにする必要はありますか？

いまさら気づいたけど>> 2のテーブル例ミスってます
IDの列は単に連番のつもりでした「T_ひらがな表示」の主キー想定です

Accessの勉強を兼ねて作成していますので、入力したデータは集計・分析に活用したいと考えております。
例えば
・トータル勝率
・買い取引時の勝率
・勝率の高い取引根拠
等です。

この３つのうち前の2つは現状のままでもいいですが、
勝率の高い取引根拠 を出したいなら、
すでに私やhirotonさんから提案されいてる形(正規形)にした方がいいでしょう。

正規化のやり方は、検索すれば解説ページが多数見つかるのでその中で、分かりやすそうなページを参考にやればいいでしょう。

例えばエントリ根拠に関していえば、
T_トレード履歴 から、エントリ根拠1～5 フィールドを削除して、
T_トレード履歴_エントリ根拠 テーブル作成して
下記のようなフィールド構成にします。

トレード履歴ID　　　(T_トレード履歴の外部キー)
根拠ID　(M_根拠_エントリーのの外部キー)

トレード履歴ID と 根拠ID で複数フィールド主キーに設定。

フォームは、
F_トレード履歴 に T_トレード履歴_エントリ根拠 から作成した帳票フォームをサブフォームとして埋め込めばいいでしょう。

　マスタテーブルを作った理由は？マスタデータを参照してトレード履歴を登録する、そのためにリレーションシップを設定する、という事じゃないの。
　主キーや正規化云々以前に、リレーションシップを設定する必要性が無いのであれば、MsAccessの話が始まらないでしょう。

あ、T_取引履歴はIDが主キーです。
つけ忘れておりました。

リレーション？は今のところ何も設定していません。

　正規化云々以前に、もし主キーの設定漏れがある場合、きちんと設定ないと話が始まりませんよ。リレーションシップの設定画面をスクリーンショットを添えて、新しい質問を作りませんか？

hirotonさん、Hatenaさん、回答ありがとうございます。

Accessの勉強を兼ねて作成していますので、入力したデータは集計・分析に活用したいと考えております。
例えば
・トータル勝率
・買い取引時の勝率
・勝率の高い取引根拠
等です。

入力情報を格納するテーブルの構成を変えて正規化？をするにはどうすればよいでしょうか？
現在の取引履歴を保存するテーブルの校正は画像2・3枚目の通りで、4枚目のようなマスターテーブル（M_●●）があります。

xECELシートからの読込なので、データの混在はあります。
データの保存先は、ACCESSになります。

A列には、フィールド名と紐づく要素。
B列には、保存するデータが並びます。

なので、B列の要素は、データ型はバラバラになるのが普通です。
.textで値だけ取ってきて、’yや#を使い分ければよいわけですね。
これなら、inputでテキストファイルを読み込むのと、同じ要領で処理できます。
.textって、便利ですね。
ありがとうございました。
蛇足ですが、テキストファイルをADOで読み込むのは、データ型の問題で使い物になりませんでした。

本当に助かりました。

XECELシートを使うのは、誰でも簡単に入力できるからです。
リストから選択するようにしておけば、間違いも減ります。

しかし、内容は自分で決められないので、
連想配列に取り込んで、吐き出させるのが無難です。
処理速度は求めないし、データの大きさも知れたものだし、
いつ変更されるかも分からないので、専用画面など作りたくはないのです。

どの程度を求めるか？ですが、「あまりよくはない」「そうはしないほうが無難」ですかね

困る状況（データベース的におかしい使い方）として、「根拠に〇〇が含まれているトレードを拾い出したい」みたいな要求に応えにくくなります

正規化されていれば、「根拠」フィールドだけを対象にフィルタを掛ければいいですが、横並びで1,2,3としてしまうと、その3つのフィールド全てに対して条件（それぞれのパターンで矛盾しないような）を設定する必要が出てきます。このような運用はデータベース的にNGです

なので、「仕事のシステム」ならそうはしません。「個人使用」ならまぁACCESSでちょっと作っただけだしこんなんでもいいかとしてもいいとは思います（何かやろうとすると間違いなく苦労することになりますが）

質問は実際のDBを簡略化したものです。
FXのトレード日記をAccessで作成しておりまして、

M_取引根拠

このマスターテーブルを値集合ソースにした3つのコンボボックス（連結列：1、名前：cmb_取引根拠1、cmb_取引根拠2、cmb_取引根拠3）の値をT_トレード日記（例でいうT_ひらがな表示）の取引根拠1、取引根拠2、取引根拠3フィールドに格納します。

このT_トレード日記とM_取引根拠を結合したクエリをレコードソースにして、レポート上で、「割安感」「割高感」といった風に表示したいという感じです。

実現の方法は、自己解決の方法で問題ないということでしょうか？

テーブル設計が正規化されていないのでそうなるのは仕方ないですね。

T_ひらがな表示テーブルを正規化するすると、下記のようになります。
これなら、クロス集計クエリで可能です。

T_ひらがな表示

質問の具体例に相当する実際のシステムの具体例が思い浮かびませんが、そっくりそのままやりたいことがそれならそういうものですよ

ただし、

例えばひらがな3、ひらがな4、、、と増えていくと、

のような、「フィールドの数が変動するようなデータ」は通常想定しません。データベース設計としてはNGです
設計段階で「最大個数としてn個ある」のような場合は、その仕様によっては連番で管理されるようなフィールド構造を取る場合もあります

同様の内容が増えていくようなデータの場合は、そのデータ分だけレコード（行）が増えるような形にします

T_ひらがな表示

質問中の「T_ひらがな表示」内の「ID」が、ここで提示した「管理ID」に相当します。「管理ID」が同じレコードを一つのグループとみなす形になります

このように生成したデータを、実際の表示の際に「『管理ID』が同じものを一つのレコードとして扱いたい」場合には、クエリでそのように整形します。手法はいくつかありますが、クロス集計クエリを使うのが一般的でしょう

下記で文字列として読み込んだらどうでしょう。

flArray.Add .Cells(i, 1).Text, .Cells(i, 2).Value

そもそも、データ型が混在している要素が混在してたら逃げ切れないし。

ここはAccessに関する掲示板なので、データベース観点からいれば、
そもそも、テーブルの列にデータ型が混在しているデータはありえないと思います。

ありがとうございました。
.Valueを入れないと、セルの枠を指定することになって、中身が入らないようですね。
あと、.Valueを付ければ取り込めるのですが、
要素がバリアント型として取り込まれているため、
flArray("ほげほげ")では、呼び出せません。

今回は、A列の要素は、すべて文字列だったので、
Dim n as string
n = .Cells(i, 1).value
flArray.Add n, .Cells(i, 2).Value
で逃げ切りましたが、もやもやさま～ずです。
そもそも、データ型が混在している要素が混在してたら逃げ切れないし。

かといって、matchで行数調べて、要素を取り出すのは面倒極まりないわけで。

なにか、良い方法は、ありますでしょうか？

SELECT T_ひらがな表示.ID, M_ひらがなマスター.[ひらがな], M_ひらがなマスター_1.[ひらがな]
FROM (M_ひらがなマスター RIGHT JOIN T_ひらがな表示 ON M_ひらがなマスター.[ひらがなID] = T_ひらがな表示.[ひらがなID]) LEFT JOIN M_ひらがなマスター AS M_ひらがなマスター_1 ON T_ひらがな表示.[ひらがなID2] = M_ひらがなマスター_1.[ひらがなID];

テーブルの表示でM_ひらがなマスターを2つ表示することでレポート用のクエリを作ることが出来ました。
でもこれだと、例えばひらがな3、ひらがな4、、、と増えていくと、その分だけM_ひらがなマスターのテーブルの表示を増やさないといけないですよね？
何か根本的に間違えているような気がするのですが、スマートな実現方法や、間違い等を指摘して頂きたいです。

その後、気がついたのですがそのフォームをフォームビューで開いてからデザインビューにした時にエラーメッセージがでます。そうせずに直接デザインビューで開く時はエラーメッセージはでません。何かコードに問題？

TimeValue 関数

その計算式でなぜうまくいくのかhirotonには理解できません

hirotonは段階を追って処理を提示していますが、どの部分でエラーになりますか？
クエリで各段階をそれぞれフィールドにして確認してみてください。解説の都合で[月終時]、[月始時]と適当に名前を作っていますのでうまいこと解釈していただくか、分からなければ質問してください

この結果をフォームに表示させようとすると
[Q実労働時間クエリ]![月時]

Name?というエラーになってしまいます。

フォームのレコードソース、表示させようとしているコントロールの種類（テキストボックスやコンボボックスなど）、そのコントロールのコントロールソースを確認してください

レコードソースに指定されているソースに[Q実労働時間クエリ]![月時]という名前のフィールドは含まれていますか？普通はこのような名前のフィールドはないと思います