Microsoft Access 掲示板

6,622 件中 3,841 から 3,880 までを表示しています。
7

hiroton さんありがとうございます。
まず私にもわかるかもしれないこのパターンでやってみようと取り組んでいますが、
質問させてください。
テーブルに「抽出用カナ」に該当するフィールドがなく、「苗字カナ」とは別にそれを設ける必要があるということでしょうか?

5
にこっと、、 2021/06/25 (金) 14:52:16 70584@0c3fa

解決しました。
以下の関数で出来ました。
Public Sub initalizeTables(tblName$)
    CurrentProject.Connection.Execute "DELETE FROM " & tblName$
    CurrentProject.Connection.Execute "ALTER TABLE " & tblName$ & " ALTER COLUMN id NUMBER"
    CurrentProject.Connection.Execute "ALTER TABLE " & tblName$ & " ALTER COLUMN id COUNTER (1, 1);"
End Sub

▼参考
sql - MS Access run-time error 3259 invalid field data type on alter table - Stack Overflow
https://stackoverflow.com/questions/22659444/ms-access-run-time-error-3259-invalid-field-data-type-on-alter-table/33188788

4
にこっと、、 2021/06/25 (金) 14:27:58 70584@0c3fa

私も同じエラーが出ます。

エラー番号3259
フィールドのデータ型が正しくありません。

▼実行したコード
currentdb.execute "ALTER TABLE inRecve ALTER COLUMN id COUNTER (1, 1)"

7

ありがとうございます!!
今とりあえず試行錯誤してやってみたopenargsをやってみてからコピーして教えてもらったやり方も試してみます。
リストボックスとは思いもつかなかったやり方です。勉強になります!
先に今うまくいってないopenargsですが、やりたいことにとても近づいてるんですがやはり動きが不安定なんです。
どこか間違っていますか??
これをやるのにopenargsはむいてないのでしょうか。
よろしくお願いいたします。

6

従業員リストのフォームから、ア、カ、サ、タ、ナと抽出できるボタンを作りました。

このボタンというのが、コマンドボタンのことなら、ラジオボタン(オプショングループ内に配置)で実装したほうがいいでしょう。今、押しこれまているボタンの文字で抽出されていると目で見て分かりますので。

下記でラジオボタンで50音選択できるサンプルを紹介しています。

オプショングループで50音選択 高速版 - hatena chips

上記のサンプルをフォームのデータにフィルターをかけるものですが、生成された抽出条件でSQLをコンボボックスに設定すればいいでしょう。

6
りんご 2021/06/25 (金) 05:01:26 c564b@0e907 >> 4

 ごめんなさい、混乱させてしまいました。顧客データと来院日時のコンボボックスが既にあるという事ですね。私がイメージしたのは、コンボボックスの代わりに、リストボックスを使う方法でした。
 こんな遊びもあるよと軽く読んで下さい。
 準備するものは、こんな感じです。
  明細フォーム・・・・・・・・・・レコードソース:フォームに連結した明細クエリ
  顧客コード検索リストボックス・・・値集合ソース:顧客コード検索クエリ、非連結
  来院日時検索リストボックス・・・・値集合ソース:来院日時検索クエリ、非連結
  明細リストボックス・・・・・・・・値集合ソース:フォームに連結しない明細クエリ、非連結
 顧客コードと来院日時が決まると、明細リストボックスが絞り込まれる。(帳票絞り込みのイメージ)
  顧客コード検索リストボックスのクリック時イベント:Me.明細リストボックス.Requery
  来院日時検索リストボックスのクリック時イベント :Me.明細リストボックス.Requery
 明細リストボックスからどれか選ぶと、明細フォームが絞り込まれる。(単票絞り込み)
  明細リストボックスのクリック時イベント     :Me.Requery
 絞り込むために、同じ明細クエリを2つ用意、名前を変えて、抽出条件で違いを出します。
  フォームに連結しない明細クエリ
   顧客コードフィールド:[Forms]![明細フォーム]![顧客コード検索クエリ]
   来院日時フィールド:[Forms]![明細フォーム]![来院日時検索クエリ]
  フォームに連結した明細クエリ
   明細IDフィールド:[Forms]![明細フォーム]![フォームに連結しない明細クエリ]
 以上になります。
  
 

5
りんご 2021/06/25 (金) 01:52:38 c564b@0e907 >> 3

なんか動きが変なんです。

ダイアログ(acDialog)で開くのをやめたから?
AccessVBAでフォームを画面遷移っぽく見せる - Ateitexe アテークゼ *非IT企業のプログラマによるメモブログ
規定値モード(acWindowNormal)で開いたら、DoCmd.Close acForm, Me.Nameとするブログがありました。どうでしょう?

4

更新削除フォームのヘッダーに、商品コードや商品名のリストボックスを配置、コンボボックス(非連結を想定していますがどうかな?)の値集合ソースに設定しているクエリを開いて、抽出条件にリストボックスを設定するのは、どうでしょう。

↑これのやり方ですが、わからないので質問させてください。
引数で渡している2つは、「F05_更新削除_来院データ」フォームのヘッダーに作成してある非連結のコンボボックスです。
顧客データと来院日時です。
これとは別にこの二つのリストボックスを作成するのでしょうか?
そしてもともとあるコンボボックスの値集合ソースにリストボックスを設定するのですか?!
知識が乏しくイメージが湧かなくて申し訳ないです。

3

りんごさんありがとうございます。
検索不足でした。
リンク先で、複数の値を渡すやり方で引数を渡すことができました!!

渡す側
Private Sub 更新削除_Click()
    Dim args As String
    args = Me.顧客コード & "," & Me.来院日時
    DoCmd.OpenForm "F05_更新削除_来院データ", , , , , , args
End Sub

受ける側
Private Sub Form_Open(Cancel As Integer)
    Dim str As Variant
    If Me.OpenArgs <> "" Then
        str = Split(Me.OpenArgs, ",", , vbTextCompare)
        Me.顧客コード検索.Value = str(0)
        Me.来院日時検索.Value = str(1)
    End If
End Sub

これで、正しく渡したい引数を渡して「F05_更新削除_来院データ」にも正しく表示されるのですが
なんか動きが変なんです。
渡す側で、コマンドボタンを押して「F05_更新削除_来院データ」が表示されるのですが
渡す側のフォームが全面にアクティブ化されていて開いたフォームが上手く表示できません。
また、続けてコマンドボタンを押すと今度はなにも反応しなくなってしまいます。
なにかおかしいでしょうか??

openargsでやり取りする理由は、それしかわからなかったからです😭
「F05_更新削除_来院データ」のフォームは、今回のようにコマンドボタンから引数を渡して開きたい場合と
直接開いて、ヘッダーにあるコンボボックスの引数を指定して表示させる2通りで表示させます。
このフォームのフィルターには、
(顧客コード=Forms!F05_更新削除_来院データ!顧客コード検索 and 来院日時=Forms!F05_更新削除_来院データ!来院日時検索)と入力していて、ヘッダーにあるコンボボックスの引数に応じてレコードを表示させています。
分かりにくい説明で大変恐縮ですが、よろしくお願いいたします。

2
りんご 2021/06/24 (木) 23:52:42 c564b@0e907 >> 1

ごめんなさい、一部、訂正します。

更新削除フォームのヘッダーに、商品コードや商品名のリストボックスを配置、コンボボックス連結クエリの抽出条件に、リストボックスを設定するのは、どうでしょう。

更新削除フォームのヘッダーに、商品コードや商品名のリストボックスを配置、コンボボックス(非連結を想定していますがどうかな?)の値集合ソースに設定しているクエリを開いて、抽出条件にリストボックスを設定するのは、どうでしょう。

1
りんご 2021/06/24 (木) 22:57:50 c564b@0e907

 ググると、色々出てますが、どうですか?ネット情報なので、もしかしたら、過不足や間違いがあるかもしれませんが。
【Access】OpenArgsでフォームを開く引数に複数の値を渡したい|アズビーパートナーズ
T'sWare Access Tips #648 ~フォームに複数のOpenArgsを渡すには?~
 ところで、前の質問中に、下記コメントがありました。

F05_更新削除を、なんとか教えてもらったリンク先の通り連結フォームで作ってレコードソースをT01_商品としました。
更新削除フォームを直接開くと、商品コード検索という非連結のコンボボックスがひとつおいてあって
そこで選んだ商品コードに値するレコードが表示できるようにしています。

少し気になったのが、openargsでやり取りする理由です。
・帳票が検索しやすい、単票が更新削除しやすい、とか?
・非連結のときに、5つのフィールドのデータを受け渡しする予定だったから、とか?
 更新削除フォームのヘッダーに、商品コードや商品名のリストボックスを配置、コンボボックス連結クエリの抽出条件に、リストボックスを設定するのは、どうでしょう。

11

ありがとうございます!
すみませんでした。スレ立てなおしますね!

10
りんご 2021/06/24 (木) 20:05:00 c564b@0e907

 横道に逸れるけど、結果フォームを連結フォームに変更したという事ならば、結果フォームに検索機能を統合すれば、どうなるか?
 あとは、解決クローズならば、スレ立て直しがいいかも。

9

こんにちは!!
こちらで教えていただいたこの方法が大変役にたっておりますが、これと全く同じ方法で、今度は指定項目は2つになるとどのように記述すればいいでしょうか??
どう変更してもうまくいかなくて、また教えていただけるとありがたいです。

検索フォーム側(商品コードだけではなくて商品名も一致しているレコードを表示したい)
    Dim args,args2 As String
    args = Me.商品コード
    args2 = me.商品名
    DoCmd.OpenForm "氏名テーブル", acNormal, , "商品コード=" & args & "", , acDialog, args,args2?

結果フォーム側
Private Sub Form_Open(Cancel As Integer)
    If Me.OpenArgs <> "" Then
      me.商品コード検索.Value = Me.OpenArgs
      me.商品名検索.valtue = ????
    End If
End Sub

お手数ですが、宜しくお願いいたします😵

5
りんご 2021/06/24 (木) 13:19:24 c564b@0e907 >> 1

 テキストボックスを配置して、
・選択クエリの抽出条件に、Like[Forms]![フォーム1]![テキスト1] & “*”
・プロパティシートの変更時イベント

Private Sub テキスト1_Change()
    Me.Requery
    Me.テキスト1.SetFocus

    If IsNull(Me.テキスト1) Then
        Me.テキスト1.SelStart = 0
    Else
        Me.テキスト1.SelStart = Len(Me.テキスト1)
    End If
End Sub

 ボタンを配置して、
・マウスボタンクリック時イベント

Private Sub ボタン1_MouseDown(ほにゃらら、自動入力されるので、省略)
     Me.テキスト1 = “[ア-オ]“
     Me.Requery
End Sub

 コンボボックスを配置して、
・プロパティシートの値集合ソースに、絞り込むために活用した選択クエリを設定
・フォーカス取得時イベント

Private Sub コンボ1_Enter()
        Me.コンボ1.Requery
End Sub
4
hiroton 2021/06/24 (木) 13:07:34 修正 79b19@f966d

※名前の提示がないモノもあるので適当に読み替えてください

「従業員リストのフォーム」にテキストボックス「抽出用カナ」を配置します
「従業員リスト」テーブルを元にクエリ「Q従業員抽出」を作成します

フィールド:氏名苗字カナ
テーブル:従業員リスト従業員リスト
並べ替え:
表示:
抽出条件:Like [Forms]![従業員リストのフォーム]![抽出用カナ]

「従業員リストのフォーム」にコンボボックス「従業員コンボボックス」を配置し、以下の設定にします

値集合ソース|Q従業員抽出
値集合タイプ|テーブル/クエリ

各ボタンの埋め込みマクロに次のようにアクションを追加します

プロパティの設定
 コントロール名 抽出用カナ
   プロパティ 値
       値 [ア-オ]*

再クエリ
 コントロール名 従業員コンボボックス

プロパティの設定はそれぞれのボタンに合わせて変更してください

3
りんご 2021/06/24 (木) 11:31:45 c564b@0e907 >> 2

えっとですね、最終的に、ひとつのコンボボックスで出来ると思うのですが、うーん。

2

早速ありがとうございます!
あ行で絞り込んでいるのは、埋め込みマクロ(条件式 =[苗字カナ] Like "[ア-オ]*")です。

仮に、(やり方がよくわかっていませんが)クエリで絞り込んだ場合、あ行用のコンボボックス、か行用のコンボボックスと、それぞれに必要になるということでしょうか?

1
りんご 2021/06/24 (木) 10:58:45 c564b@0e907

例えば、あ行で絞り込めた時に、活用した選択クエリを、コンボボックスのコントロールソースに設定するのは、どうでしょうか?

22

皆様、ベンチマークなども行っていただき本当にありがとうございました。自分には難しくて理解できなかった部分もありましたので、テーブル設計も含めて、皆様から提示いただいたクエリやVBAをしっかり勉強したいと思います。ありがとうございました。HH

21

>> 18
丁寧に返信いただいてありがとうございます。お礼嬉しいです。

今回はデータ件数という「壁」があっただけで
りんごさんは、HHさんの希望に沿った回答をちゃんと提供できています。
長時間のチャレンジ、お疲れ様でした。

20

些細なことですけどENDをチェックする形にするとENDがあればSTARTがあることが保証される(はず)なのでIf判定がおよそ半分減らせますね(処理時間の差がわからない程度の差ですが)

VBAで連番振ってSQLって方式はどうせVBA使うならこれやればいいじゃないなのでACCESS上の話である以上ナンセンスなんですよね

実は対抗としては>> 14でりんごさんが試されている通り元のテーブルに手を入れてあげればn²オーダーに見えるクエリでも爆速で動くという手法だと思います。主キーインデックスのないテーブルに主キーインデックスを設定してスカラサブクエリで表示するという手法でもVBAだけと遜色ない速度がでます

結局のところテーブル設計をしっかりやりましょうという話です

19

StartTime フィールドを追加して、
"END"レコードの方に対応する"START"の時刻を格納するという方法にしてみました。

発想の柔軟さ、パフォーマンスへの配慮、さすがhatenaさんです。

標準SQLのウィンドウ関数 LAG が利用可能な SQLServer だと
以下のSQLで hatenaさんと同等のロジックになりそうですね。

SELECT EVENT
     , starttime
     , endtime
     , datediff( second, starttime, endtime ) DURATION
FROM
(
    SELECT EVENT
         , E_Time endtime
         , E_Cond
         -- OVER句における E_Cond DESC の指定は不要かも
         , LAG( E_Time, 1 )
               OVER ( PARTITION BY EVENT
                      ORDER BY     E_Time, E_Cond DESC ) starttime
    FROM 表1
) 
VBA_PART
WHERE E_Cond = 'END' ;
18
りんご 2021/06/22 (火) 17:13:08 c564b@0e907

>> 16
 早速、試してみたところ、時間がかかりすぎると、とても実感できました。続けて、テーブル作成クエリやSQLの悪あがきをやってみたけれど、力尽きました。
 ビギナーズラック達成かと舞い上がったのですが、なかなか難しいですね。
 返信、遅くなりましたが、このようなアドバイスはとても助かります。ありがとう。

17

mayuさん、ベンチマーク実験ありがとうございました。非常に示唆に富む興味深い実験ですね。
あと、PC性能すごいですね。当方の環境でテストしたら数倍時間かかりました(;^_^A

100万件のデータとなると連番を入力しておいても結構時間がかかるようです。
そこでテーブルに連番を入力するのではなく StartTime フィールドを追加して、"END"レコードの方に対応する"START"の時刻を格納するという方法にしてみました。

表1に日付/時刻型のフィールドを追加して、名前を StartTime とします
下記のVBAを実行します。

Public Sub SetStarTime()
    Const strSQL = _
          "SELECT * FROM 表1 ORDER BY EVENT, E_Time, E_Cond DESC;"

    Dim rs As DAO.Recordset
    Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)

    Do Until rs.EOF
        Dim stime As Date
        If rs!E_Cond = "START" Then
            stime = rs!E_Time
        Else
            rs.Edit
            rs!StartTime = stime
            rs.Update
        End If
        rs.MoveNext
    Loop

    rs.Close

    MsgBox "StarTime入力完了"
End Sub

クエリは下記のようにシンプルになります。

SELECT
    EVENT,
    StartTime,
    E_Time,
    DateDiff('s', StartTime, E_Time) AS DURATION
FROM
    表1
WHERE
    E_Cond = "END";

E_Condにインデックスを貼っておいたらほぼ一瞬で表示されました。

16

テーブルのEVENTとE_Timeに複合主キーを設定して、
再度チャレンジしてみたら、だいたい5秒で処理出来てしまったのだが、
うーん、これはありなのだろうか?

ありでしょう。
実際かなりの高速化が見込めるでしょうし、素晴らしいチューニングだと思います。

私がインデックスや主キーの設定を施さなかったのは
フィールドのグルーピングや抽出条件に指定するフィールドの順番が
回答者によって異なっていたため、
特定の手法だけベンチマークに有利な影響を及ぼす設定では
アルゴリズムの比較にならない、という理由です。

ただ、処理時間が本当に5秒へ縮小されたかというと...疑問符が付きます。
というのも
定義域集計関数を使ったクエリの場合、データシートビューの表示において
「目視できている」部分のレコードしか各行の演算が済んでいない
ということがあります。

つまり、
「 クエリのデータをデータシートビューで閲覧可能、且つUIの操作も可能 」
という状態であっても
全行の処理が終わっているとは限らないんです。
 

SELECT EVENT
     , E_Time
     , CDate(
           DMin( "E_Time", "表1"
               , "EVENT = '" & [EVENT] & "' And E_Time > #" & [E_Time] & "#"
           )
       ) As E_END
     , DateDiff( 's', E_Time, E_END ) As DURATION
FROM 表1
WHERE E_Cond = 'START' ;

 
この SQL に名前を付け、クエリとして保存した後に
クエリを右クリックして表示されるコンテキストメニューから
テキストファイルにでもデータをエクスポートしてみることをお薦めします。

クエリを開くために要した時間は数秒程度と短いのに
データのエクスポートを実施した途端、
ファイル書き出しのオーバーヘッドが多少はあるにしろ
異常なくらい長い時間を要する

ということが実感できると思います。

14
りんご 2021/06/22 (火) 00:21:18 c564b@0e907

>> 13
テーブルのEVENTとE_Timeに複合主キーを設定して、再度チャレンジしてみたら、だいたい5秒で処理出来てしまったのだが、うーん、これはありなのだろうか?

13
りんご 2021/06/21 (月) 23:48:40 c564b@0e907 >> 10

Mayu様のサンプルデータ追加モジュールを活用して、上記、りんごのクエリを実行中、、、
応答なし、、、応答なし、、、30min経過、一部、描画、、、応答なし、、、計測断念。
お試ししたら、ダメなやつでした、ごめんなさい。

4

クロス集計クエリのデザインビューで下記のように設定すればどうでしょう。
"一般協賛"の場合です。

フィールド 会社名 住所 電話番号 年度 売上金額 収入科目
集計 グループ化 グループ化 グループ化 グループ化 合計 Where条件
行列の入れ替え 行見出し 行見出し 行見出し 列見出し
抽出条件 "一般協賛"
3
名前なし 2021/06/21 (月) 22:56:27 c564b@0e907

 クロス集計クエリは、Google先生と同じ説明になると思います。
 例えば、新しいクエリを作り、テーブルの各フィールドを選択・追加。
 次に、デザインタブのΣ集計ボタンを押し、下記を設定。
・売上金額フィールドの集計:グループ化を集計:合計に変更。
・収入科目の集計:グループ化をWhere条件に変更、抽出条件:“一般協賛”を入力。
 次に、デザインタブのクロス集計ボタンを押して、行列の入れ替えを設定。
・年度が列見出し。
・売上金額が値。
・会社名と住所と電話番号が行見出し。
 最後に、実行。

 年度別の売上金額合計・目標金額・達成率は、わかりませんでした。もしかしたら、目標金額のような新しいフィールド、見出しや明細のような新しいテーブルが必要なのかもしれません。
 個人的には、クロス集計の美しさよりも、テーブル設計の美しさを追求するほうが楽しいと思いますよ?
 

2
セメント 2021/06/21 (月) 20:15:02 d1e06@c0fb6

回答ありがとうございます。

説明不足ですみません。目標金額については無視していただいて構いません。

クロス集計クエリでもできないか調べてみたのですが、いろんな要素があって挫折したのです。

もう少々具体的に教えていただけるとありがたいです、ご回答よろしくお願いします。

2
セメント 2021/06/21 (月) 20:11:37 d1e06@c0fb6

回答ありがとうございます。

キーワードから色々調べてみてなんとかできました!!

4
朱色 2021/06/21 (月) 19:59:40 修正 db0eb@2db89

データを直接修正することについての是非は横においておくとして・・・

3結合にすることでデータ量が増えるということはないですか?
たとえば2結合では 1-A、2-B だったのに、3結合では 1-A-a、1-A-b、1-B-c という風に。
それぞれの結合が、各サブテーブルのすべてのキー項目と連結されているなら増えないハズ。

あと邪道ですが、SQLServer側にVIEWを作ってそれに対してリンクを張るという手もありますね。
お勧めはしませんが。

すみません、パフォーマンス系の知識はあまりなくて。
他の方の有用回答にご期待ください(;==)/

(ちなみにテーブル構造と結合部分が完全なSQL貼ってくださったほうが良いと思います)

12

100万件のデータに対して、クエリ( SQL )の実行を10回繰り返し
最終行のデータが表示されるまでの平均所要時間を計測してみました。
( EVENT, E_Time, E_Cond, Seq 各列にインデックスや主キーの設定は無し )

< ベンチマーク実施環境 >
バージョン:  Access 2019 (32bit)
OS:     Windows 10 Pro 64bit
CPU:     Intel Core i9-8950HK 2.90GHz
メモリ:    64 GB
ディスク:   Samsung SSD 970 Pro
 
< 実行結果 >

手段EVENT数 15,000EVENT数 700
クロス集計クエリ14.8秒10.4秒
選択クエリ(多段)42.1秒36.8秒
集計クエリ6.2秒6.0秒
自己結合(内部結合)20.4秒7分22秒
自己結合(外部結合)36.7秒12分48秒
スカラサブクエリ計測断念計測断念※ 20分経過しても結果が表示されず
定義域集計関数計測断念計測断念※ 20分間Accessが固まったまま
 
< サンプルデータ追加モジュール >
`js
Sub add_record()

    Const TOTAL_REC    As Long = 1000000
    Const EVENT_CHAR   As Long = 3
    Const MAX_DURATION As Long = 120
    Const TABLE_NAME   As String = "表1"

    With CurrentDb
        If (DCount("*", "MSysObjects", "[Name] = '" & TABLE_NAME & "'") = 0) Then
            Dim strDDL As String
            strDDL = "CREATE TABLE " & TABLENAME & "     " & vbNewLine
                   & "(                                   " & vbNewLine
                   & "      EVENT   VARCHAR(50)  NOT NULL " & vbNewLine

                   & "    , ETime  DATETIME     NOT NULL " & vbNewLine
                   & "    , ECond  VARCHAR(20)  NOT NULL " & vbNewLine
                   & "    , Seq     INT                   " & vbNewLine _
                   & ");"
            .Execute Query:=strDDL, Options:=dbFailOnError
            Application.RefreshDatabaseWindow
        End If
        .Execute Query:="DELETE FROM " & TABLE_NAME & ";", Options:=dbFailOnError

        Dim ts As Date
        ts = DateSerial(Year(Date), 1, 1) + TimeSerial(0, 0, 1)

        Dim dic As Object
        Set dic = CreateObject("Scripting.Dictionary")

        With .OpenRecordset(TABLE_NAME, dbOpenTable)
            Dim i      As Long
            Dim j      As Long
            Dim e_name As String

            e_name = Space$(EVENT_CHAR)

            For i = 1 To TOTAL_REC
                If (i Mod 2 = 1) Then
                    Randomize
                    For j = 1 To EVENT_CHAR
                        Mid(e_name, j, 1) = Chr$(Int((90 - 65 + 1) Rnd) + 65)
                    Next j
                    dic(e_name) = dic(e_name) + 1
                End If
                ts = DateAdd("s", Int(MAX_DURATION
Rnd + 1), ts)

                .AddNew
                .Fields("EVENT").Value = e_name
                .Fields("E_Time").Value = ts
                .Fields("E_Cond").Value = IIf(i Mod 2 = 1, "START", "END")
                .Fields("Seq").Value = CLng(dic(e_name))
                .Update
            Next i
            .Close
        End With

        Set dic = Nothing
        .Close
    End With
    MsgBox Prompt:="データ作成完了", Buttons:=vbInformation, Title:="実行結果"
End Sub


3
だいふくもち 2021/06/21 (月) 10:44:18

りんご様、l2106様、ご回答いただきありがとうございます!

rs.FindFirst "入金月 = " & "#" & DateAdd("m", i, startDate) & "#"

このように変えたらうまくいきました!
なるほど…Format関数は日付型でなく文字列型にするので、"#"で囲んでも意味がなかったのですね…
おかげさまで大変勉強になりました!この度は助けていただき本当にありがとうございました!

11
hiroton 2021/06/21 (月) 08:53:58 2ea60@f966d

計算量という考え方があります

表1のデータを表2の形にするということは、STARTのデータに対しENDのデータを見つけることになりますが、シンプルに考えると100万のうち半数のSTARTのデータそれぞれに対し、同様の50万のデータがENDの候補になっていて、その中から最適な一つのデータを見つけることになるので50万x50万のデータのチェックをすることになります。データ数をnとすれば

n² x a

のチェック回数が必要であることがわかります
nは2乗されます(²が環境依存文字のため念のため)
aはSTARTとENDのデータがそれぞれ元のデータnの半数なので2分の1を2回掛けたり、インデックスが適切に設定されていれば比較処理も半分で済んだりするといったようなチェック回数に関係する係数です(増える係数が掛かることもあるでしょう)

1回のチェック時間がどの程度かは置いておいて、このようなデータ処理を組むと、データ数が10倍になれば処理時間が100倍になります

結果が表示されるまでに約38秒かかりました。この時間は遅いのでしょうか。それとも相応なのでしょうか。

38秒自体が早いか遅いかは感覚によるところですが、データを複数回チェックしなくていいような仕組みを組めば飛躍的に処理時間が短くなることは期待できます

既にいくつかの手法が出ていますが、データのチェックが1回で済むような複数の処理を組み合わせることにより計算量の式が

n x a

となるような処理を作ることができます。この形の処理であればデータ数が10倍になっても処理時間は10倍で済むような処理となるので実時間(38秒)がどうであれ、試した手法は遅い手法だと言えます

こういったデータの並び替えって、需要が結構ありそうだと思っていたんですが、以外に少ないみたいですね。

シンプルな処理は理論の時点で遅く、データの特徴を掴んだ特殊な処理を入れれば高速化が見込めるというものなので、需要はあっても特殊な処理が必要(コピペで動かない)ではずばりの情報は少ないでしょうね


hirotonの感覚からすれば100万件のデータで38秒は「十分に早い」と思います。問題なのは表1の形式のデータが正式なデータとして使われ、表2の形式が欲しいとなったとき、その都度38秒が発生することでしょう

それなら表2の形式のデータを正式なモノにすればよくないですか?一度データを作ってしまえば次からは集計処理がないので表示も一瞬ですよ

「シンプルな処理を考える時間+38秒」と「複雑な処理を考える時間+一瞬の処理」、一度しかやらなくていい処理ならどちらがいいかの選択も臨機応変です

こういったデータの並び替えって、需要が結構ありそうだと思っていたんですが、以外に少ないみたいですね。

改めてですが、テーブル構造をしっかり設計するとそもそも需要がないんですよね

10
りんご 2021/06/20 (日) 18:39:48 48103@0e907

シンプルに、新しいクエリを作成して、下記フィールドを考えてみました。
EVENT
E_Time
E_Cond・・・抽出条件:“START”
式1(表2のENDを導出):CDate(DMin(“E_Time”,”表1”,”EVENT=‘“ & [EVENT] & “‘ and E_Time>#” & [E_Time] & “#”))
式2(表2のDURATIONを導出):DateDiff(“n”,[E_Time],[式1])

 もしお試し頂けたらこれ幸いです。成否の結果と100万レコードの処理時間を教えてくれると嬉しいなぁ。

9

hatenaさんが回答なさっている{ クロス集計 + 選択クエリ }による
多段クエリのほうが可読性が良く、内容の理解は容易なのですが
SQL の読み書きに対し、特に苦手意識が無いのでしたら
SetSequenceNumber関数で Seq フィールドに連番を付与した後、
表のスキャンが一度だけになる集計クエリを作ればいいでしょう。

SELECT EVENT
     , Max( IIf( E_Cond = 'START', E_Time ) ) As E_START
     , Max( IIf( E_Cond = 'END',   E_Time ) ) As E_END
     , DateDiff( 's'
        , Max( IIf( E_Cond = 'START', E_Time ) )
        , Max( IIf( E_Cond = 'END',   E_Time ) )
       ) As DURATION
FROM 表1
GROUP BY EVENT
       , Seq
ORDER BY Max( IIf( E_Cond = 'START', E_Time ) )
       , EVENT ;

  
また、朱色さんと似たロジックになりますが
自己結合を用いた記述方法もご紹介します。
(
  ENDが存在しないイベントも表示する場合は  LEFT JOIN
  ENDが存在しないイベントが表示不要の場合は INNER JOIN
  というように
  表示件数とパフォーマンスを自身で調整することが可能です
)

SELECT x.EVENT
     , x.E_Time        As E_START
     , Min( y.E_Time ) As E_END
     , DateDiff( 's', x.E_Time, Min( y.E_Time ) ) As DURATION
FROM       表1 x
INNER JOIN 表1 y
ON
(
    x.E_Cond = 'START'   AND
    y.E_Cond = 'END'     AND
    x.EVENT  = y.EVENT   AND
    x.E_Time < y.E_Time
)
GROUP BY x.EVENT
       , x.E_Time
ORDER BY x.E_Time
       , x.EVENT ;

 

こういったデータの並び替えって、
需要が結構ありそうだと思っていたんですが、以外に少ないみたいですね。

前後への行参照は、定番と言っていいほど需要はあるのですけど
hatenaさんが仰っているように、SQL では重い処理になります。
朱色さんが記述したスカラサブクエリ、私が後半で記述した自己結合ともに「重い」SQLです。

Oracle や SQLServer などの本格的なデータベースでは
SQL文中に row_number, rag, lead といった分析関数を駆使して高速化できるのですが
残念ながら、Accessには分析関数が実装されていません。

したがって、SQL のボトルネックを VBA で補い、
SQL と組み合わせるという Access ならではのテクニックを用いて高速化したのが
hatenaさんの回答になります。

7

最初に紹介した連番入力する方法を提示しておきます。(ちょっと修正してます。)

まず、テーブル「表1」に数値型のフィールドを追加します。フィールド名は「Seq」とします。

次に、リンク先から、「グループ毎の連番を入力する関数」をコピーして標準モジュールに貼り付けます。

表1のデータを更新したときに、下記のコードを実行します。入力フォームの更新後処理にでも実行するといいでしょう。

    SetSequenceNumber "Seq", "表1", "Event,E_Cond", "E_Time" 

下記のようなクロス集計クエリを作成します。名前は「Q1」とします。

TRANSFORM First(E_Time)
SELECT EVENT, Seq
FROM 表1
GROUP BY EVENT, Seq
PIVOT E_Cond;

このクエリからさらに下記のクエリを作成します。

SELECT EVENT, START, END, DateDiff("s",START,END) AS DURATION
FROM Q1
ORDER BY START, EVENT;

クエリ2つ、使ってますが、サブクエリを使って一つに纏めることもできます。

クエリはかなり高速になると思います。

100万レコードだと連番入力のコードの実行に時間がかかるかも知れません。その場合は、更新したレコードのEVENTのみを抽出して連番入力するようにするといいでしょう。

表1の入力フォームの更新後処理で実行するとして、下記のようなコードになります。

    SetSequenceNumber "Seq", "表1", "Event,E_Cond", "E_Time", "EVENT='" & Me.EVENT & "'"
6

クエリでやると件数が増えると指数関数的に重くなりますので、そんなもんだと思います。
EVENT と E_Time にインデックスを設定する改善するかも知れません。