解決しました。
以下の関数で出来ました。
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
渡す側
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
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
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";
< ベンチマーク実施環境 >
バージョン: Access 2019 (32bit)
OS: Windows 10 Pro 64bit
CPU: Intel Core i9-8950HK 2.90GHz
メモリ: 64 GB
ディスク: Samsung SSD 970 Pro
< 実行結果 >
手段
EVENT数 15,000
EVENT数 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
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 ;
hiroton さんありがとうございます。
まず私にもわかるかもしれないこのパターンでやってみようと取り組んでいますが、
質問させてください。
テーブルに「抽出用カナ」に該当するフィールドがなく、「苗字カナ」とは別にそれを設ける必要があるということでしょうか?
解決しました。
以下の関数で出来ました。
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
私も同じエラーが出ます。
エラー番号3259
フィールドのデータ型が正しくありません。
▼実行したコード
currentdb.execute "ALTER TABLE inRecve ALTER COLUMN id COUNTER (1, 1)"
ありがとうございます!!
今とりあえず試行錯誤してやってみたopenargsをやってみてからコピーして教えてもらったやり方も試してみます。
リストボックスとは思いもつかなかったやり方です。勉強になります!
先に今うまくいってないopenargsですが、やりたいことにとても近づいてるんですがやはり動きが不安定なんです。
どこか間違っていますか??
これをやるのにopenargsはむいてないのでしょうか。
よろしくお願いいたします。
このボタンというのが、コマンドボタンのことなら、ラジオボタン(オプショングループ内に配置)で実装したほうがいいでしょう。今、押しこれまているボタンの文字で抽出されていると目で見て分かりますので。
下記でラジオボタンで50音選択できるサンプルを紹介しています。
オプショングループで50音選択 高速版 - hatena chips
上記のサンプルをフォームのデータにフィルターをかけるものですが、生成された抽出条件でSQLをコンボボックスに設定すればいいでしょう。
ごめんなさい、混乱させてしまいました。顧客データと来院日時のコンボボックスが既にあるという事ですね。私がイメージしたのは、コンボボックスの代わりに、リストボックスを使う方法でした。
こんな遊びもあるよと軽く読んで下さい。
準備するものは、こんな感じです。
明細フォーム・・・・・・・・・・レコードソース:フォームに連結した明細クエリ
顧客コード検索リストボックス・・・値集合ソース:顧客コード検索クエリ、非連結
来院日時検索リストボックス・・・・値集合ソース:来院日時検索クエリ、非連結
明細リストボックス・・・・・・・・値集合ソース:フォームに連結しない明細クエリ、非連結
顧客コードと来院日時が決まると、明細リストボックスが絞り込まれる。(帳票絞り込みのイメージ)
顧客コード検索リストボックスのクリック時イベント:Me.明細リストボックス.Requery
来院日時検索リストボックスのクリック時イベント :Me.明細リストボックス.Requery
明細リストボックスからどれか選ぶと、明細フォームが絞り込まれる。(単票絞り込み)
明細リストボックスのクリック時イベント :Me.Requery
絞り込むために、同じ明細クエリを2つ用意、名前を変えて、抽出条件で違いを出します。
フォームに連結しない明細クエリ
顧客コードフィールド:[Forms]![明細フォーム]![顧客コード検索クエリ]
来院日時フィールド:[Forms]![明細フォーム]![来院日時検索クエリ]
フォームに連結した明細クエリ
明細IDフィールド:[Forms]![明細フォーム]![フォームに連結しない明細クエリ]
以上になります。
ダイアログ(acDialog)で開くのをやめたから?
AccessVBAでフォームを画面遷移っぽく見せる - Ateitexe アテークゼ *非IT企業のプログラマによるメモブログ
規定値モード(acWindowNormal)で開いたら、DoCmd.Close acForm, Me.Nameとするブログがありました。どうでしょう?
更新削除フォームのヘッダーに、商品コードや商品名のリストボックスを配置、コンボボックス(非連結を想定していますがどうかな?)の値集合ソースに設定しているクエリを開いて、抽出条件にリストボックスを設定するのは、どうでしょう。
↑これのやり方ですが、わからないので質問させてください。
引数で渡している2つは、「F05_更新削除_来院データ」フォームのヘッダーに作成してある非連結のコンボボックスです。
顧客データと来院日時です。
これとは別にこの二つのリストボックスを作成するのでしょうか?
そしてもともとあるコンボボックスの値集合ソースにリストボックスを設定するのですか?!
知識が乏しくイメージが湧かなくて申し訳ないです。
りんごさんありがとうございます。
検索不足でした。
リンク先で、複数の値を渡すやり方で引数を渡すことができました!!
渡す側
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_更新削除_来院データ!来院日時検索)と入力していて、ヘッダーにあるコンボボックスの引数に応じてレコードを表示させています。
分かりにくい説明で大変恐縮ですが、よろしくお願いいたします。
ごめんなさい、一部、訂正します。
更新削除フォームのヘッダーに、商品コードや商品名のリストボックスを配置、コンボボックス(非連結を想定していますがどうかな?)の値集合ソースに設定しているクエリを開いて、抽出条件にリストボックスを設定するのは、どうでしょう。
ググると、色々出てますが、どうですか?ネット情報なので、もしかしたら、過不足や間違いがあるかもしれませんが。
【Access】OpenArgsでフォームを開く引数に複数の値を渡したい|アズビーパートナーズ
T'sWare Access Tips #648 ~フォームに複数のOpenArgsを渡すには?~
ところで、前の質問中に、下記コメントがありました。
少し気になったのが、openargsでやり取りする理由です。
・帳票が検索しやすい、単票が更新削除しやすい、とか?
・非連結のときに、5つのフィールドのデータを受け渡しする予定だったから、とか?
更新削除フォームのヘッダーに、商品コードや商品名のリストボックスを配置、コンボボックス連結クエリの抽出条件に、リストボックスを設定するのは、どうでしょう。
ありがとうございます!
すみませんでした。スレ立てなおしますね!
横道に逸れるけど、結果フォームを連結フォームに変更したという事ならば、結果フォームに検索機能を統合すれば、どうなるか?
あとは、解決クローズならば、スレ立て直しがいいかも。
こんにちは!!
こちらで教えていただいたこの方法が大変役にたっておりますが、これと全く同じ方法で、今度は指定項目は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
お手数ですが、宜しくお願いいたします😵
テキストボックスを配置して、
・選択クエリの抽出条件に、Like[Forms]![フォーム1]![テキスト1] & “*”
・プロパティシートの変更時イベント
ボタンを配置して、
・マウスボタンクリック時イベント
コンボボックスを配置して、
・プロパティシートの値集合ソースに、絞り込むために活用した選択クエリを設定
・フォーカス取得時イベント
※名前の提示がないモノもあるので適当に読み替えてください
「従業員リストのフォーム」にテキストボックス「抽出用カナ」を配置します
「従業員リスト」テーブルを元にクエリ「Q従業員抽出」を作成します
「従業員リストのフォーム」にコンボボックス「従業員コンボボックス」を配置し、以下の設定にします
各ボタンの埋め込みマクロに次のようにアクションを追加します
プロパティの設定の値はそれぞれのボタンに合わせて変更してください
えっとですね、最終的に、ひとつのコンボボックスで出来ると思うのですが、うーん。
早速ありがとうございます!
あ行で絞り込んでいるのは、埋め込みマクロ(条件式 =[苗字カナ] Like "[ア-オ]*")です。
仮に、(やり方がよくわかっていませんが)クエリで絞り込んだ場合、あ行用のコンボボックス、か行用のコンボボックスと、それぞれに必要になるということでしょうか?
例えば、あ行で絞り込めた時に、活用した選択クエリを、コンボボックスのコントロールソースに設定するのは、どうでしょうか?
皆様、ベンチマークなども行っていただき本当にありがとうございました。自分には難しくて理解できなかった部分もありましたので、テーブル設計も含めて、皆様から提示いただいたクエリやVBAをしっかり勉強したいと思います。ありがとうございました。HH
>> 18
丁寧に返信いただいてありがとうございます。お礼嬉しいです。
今回はデータ件数という「壁」があっただけで
りんごさんは、HHさんの希望に沿った回答をちゃんと提供できています。
長時間のチャレンジ、お疲れ様でした。
些細なことですけどENDをチェックする形にするとENDがあればSTARTがあることが保証される(はず)なのでIf判定がおよそ半分減らせますね(処理時間の差がわからない程度の差ですが)
VBAで連番振ってSQLって方式はどうせVBA使うならこれやればいいじゃないなのでACCESS上の話である以上ナンセンスなんですよね
実は対抗としては>> 14でりんごさんが試されている通り元のテーブルに手を入れてあげればn²オーダーに見えるクエリでも爆速で動くという手法だと思います。主キーインデックスのないテーブルに主キーインデックスを設定してスカラサブクエリで表示するという手法でもVBAだけと遜色ない速度がでます
結局のところテーブル設計をしっかりやりましょうという話です
発想の柔軟さ、パフォーマンスへの配慮、さすがhatenaさんです。
標準SQLのウィンドウ関数 LAG が利用可能な SQLServer だと
以下のSQLで hatenaさんと同等のロジックになりそうですね。
>> 16
早速、試してみたところ、時間がかかりすぎると、とても実感できました。続けて、テーブル作成クエリやSQLの悪あがきをやってみたけれど、力尽きました。
ビギナーズラック達成かと舞い上がったのですが、なかなか難しいですね。
返信、遅くなりましたが、このようなアドバイスはとても助かります。ありがとう。
mayuさん、ベンチマーク実験ありがとうございました。非常に示唆に富む興味深い実験ですね。
あと、PC性能すごいですね。当方の環境でテストしたら数倍時間かかりました(;^_^A
100万件のデータとなると連番を入力しておいても結構時間がかかるようです。
そこでテーブルに連番を入力するのではなく StartTime フィールドを追加して、"END"レコードの方に対応する"START"の時刻を格納するという方法にしてみました。
表1に日付/時刻型のフィールドを追加して、名前を StartTime とします
下記のVBAを実行します。
クエリは下記のようにシンプルになります。
E_Condにインデックスを貼っておいたらほぼ一瞬で表示されました。
ありでしょう。
実際かなりの高速化が見込めるでしょうし、素晴らしいチューニングだと思います。
私がインデックスや主キーの設定を施さなかったのは
フィールドのグルーピングや抽出条件に指定するフィールドの順番が
回答者によって異なっていたため、
特定の手法だけベンチマークに有利な影響を及ぼす設定では
アルゴリズムの比較にならない、という理由です。
ただ、処理時間が本当に5秒へ縮小されたかというと...疑問符が付きます。
というのも
定義域集計関数を使ったクエリの場合、データシートビューの表示において
「目視できている」部分のレコードしか各行の演算が済んでいない
ということがあります。
つまり、
「 クエリのデータをデータシートビューで閲覧可能、且つUIの操作も可能 」
という状態であっても
全行の処理が終わっているとは限らないんです。
この SQL に名前を付け、クエリとして保存した後に
クエリを右クリックして表示されるコンテキストメニューから
テキストファイルにでもデータをエクスポートしてみることをお薦めします。
クエリを開くために要した時間は数秒程度と短いのに
データのエクスポートを実施した途端、
ファイル書き出しのオーバーヘッドが多少はあるにしろ
異常なくらい長い時間を要する
ということが実感できると思います。
>> 13
テーブルのEVENTとE_Timeに複合主キーを設定して、再度チャレンジしてみたら、だいたい5秒で処理出来てしまったのだが、うーん、これはありなのだろうか?
Mayu様のサンプルデータ追加モジュールを活用して、上記、りんごのクエリを実行中、、、
応答なし、、、応答なし、、、30min経過、一部、描画、、、応答なし、、、計測断念。
お試ししたら、ダメなやつでした、ごめんなさい。
クロス集計クエリのデザインビューで下記のように設定すればどうでしょう。
"一般協賛"の場合です。
クロス集計クエリは、Google先生と同じ説明になると思います。
例えば、新しいクエリを作り、テーブルの各フィールドを選択・追加。
次に、デザインタブのΣ集計ボタンを押し、下記を設定。
・売上金額フィールドの集計:グループ化を集計:合計に変更。
・収入科目の集計:グループ化をWhere条件に変更、抽出条件:“一般協賛”を入力。
次に、デザインタブのクロス集計ボタンを押して、行列の入れ替えを設定。
・年度が列見出し。
・売上金額が値。
・会社名と住所と電話番号が行見出し。
最後に、実行。
年度別の売上金額合計・目標金額・達成率は、わかりませんでした。もしかしたら、目標金額のような新しいフィールド、見出しや明細のような新しいテーブルが必要なのかもしれません。
個人的には、クロス集計の美しさよりも、テーブル設計の美しさを追求するほうが楽しいと思いますよ?
回答ありがとうございます。
説明不足ですみません。目標金額については無視していただいて構いません。
クロス集計クエリでもできないか調べてみたのですが、いろんな要素があって挫折したのです。
もう少々具体的に教えていただけるとありがたいです、ご回答よろしくお願いします。
回答ありがとうございます。
キーワードから色々調べてみてなんとかできました!!
データを直接修正することについての是非は横においておくとして・・・
3結合にすることでデータ量が増えるということはないですか?
たとえば2結合では 1-A、2-B だったのに、3結合では 1-A-a、1-A-b、1-B-c という風に。
それぞれの結合が、各サブテーブルのすべてのキー項目と連結されているなら増えないハズ。
あと邪道ですが、SQLServer側にVIEWを作ってそれに対してリンクを張るという手もありますね。
お勧めはしませんが。
すみません、パフォーマンス系の知識はあまりなくて。
他の方の有用回答にご期待ください(;==)/
(ちなみにテーブル構造と結合部分が完全なSQL貼ってくださったほうが良いと思います)
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
< 実行結果 >
`
jsConst 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
りんご様、l2106様、ご回答いただきありがとうございます!
このように変えたらうまくいきました!
なるほど…Format関数は日付型でなく文字列型にするので、"#"で囲んでも意味がなかったのですね…
おかげさまで大変勉強になりました!この度は助けていただき本当にありがとうございました!
計算量という考え方があります
表1のデータを表2の形にするということは、STARTのデータに対しENDのデータを見つけることになりますが、シンプルに考えると100万のうち半数のSTARTのデータそれぞれに対し、同様の50万のデータがENDの候補になっていて、その中から最適な一つのデータを見つけることになるので50万x50万のデータのチェックをすることになります。データ数をnとすれば
のチェック回数が必要であることがわかります
nは2乗されます(²が環境依存文字のため念のため)
aはSTARTとENDのデータがそれぞれ元のデータnの半数なので2分の1を2回掛けたり、インデックスが適切に設定されていれば比較処理も半分で済んだりするといったようなチェック回数に関係する係数です(増える係数が掛かることもあるでしょう)
1回のチェック時間がどの程度かは置いておいて、このようなデータ処理を組むと、データ数が10倍になれば処理時間が100倍になります
38秒自体が早いか遅いかは感覚によるところですが、データを複数回チェックしなくていいような仕組みを組めば飛躍的に処理時間が短くなることは期待できます
既にいくつかの手法が出ていますが、データのチェックが1回で済むような複数の処理を組み合わせることにより計算量の式が
となるような処理を作ることができます。この形の処理であればデータ数が10倍になっても処理時間は10倍で済むような処理となるので実時間(38秒)がどうであれ、試した手法は遅い手法だと言えます
シンプルな処理は理論の時点で遅く、データの特徴を掴んだ特殊な処理を入れれば高速化が見込めるというものなので、需要はあっても特殊な処理が必要(コピペで動かない)ではずばりの情報は少ないでしょうね
hirotonの感覚からすれば100万件のデータで38秒は「十分に早い」と思います。問題なのは表1の形式のデータが正式なデータとして使われ、表2の形式が欲しいとなったとき、その都度38秒が発生することでしょう
それなら表2の形式のデータを正式なモノにすればよくないですか?一度データを作ってしまえば次からは集計処理がないので表示も一瞬ですよ
「シンプルな処理を考える時間+38秒」と「複雑な処理を考える時間+一瞬の処理」、一度しかやらなくていい処理ならどちらがいいかの選択も臨機応変です
改めてですが、テーブル構造をしっかり設計するとそもそも需要がないんですよね
シンプルに、新しいクエリを作成して、下記フィールドを考えてみました。
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万レコードの処理時間を教えてくれると嬉しいなぁ。
hatenaさんが回答なさっている{ クロス集計 + 選択クエリ }による
多段クエリのほうが可読性が良く、内容の理解は容易なのですが
SQL の読み書きに対し、特に苦手意識が無いのでしたら
SetSequenceNumber関数で Seq フィールドに連番を付与した後、
表のスキャンが一度だけになる集計クエリを作ればいいでしょう。
また、朱色さんと似たロジックになりますが
自己結合を用いた記述方法もご紹介します。
(
ENDが存在しないイベントも表示する場合は LEFT JOIN
ENDが存在しないイベントが表示不要の場合は INNER JOIN
というように
表示件数とパフォーマンスを自身で調整することが可能です
)
前後への行参照は、定番と言っていいほど需要はあるのですけど
hatenaさんが仰っているように、SQL では重い処理になります。
朱色さんが記述したスカラサブクエリ、私が後半で記述した自己結合ともに「重い」SQLです。
Oracle や SQLServer などの本格的なデータベースでは
SQL文中に row_number, rag, lead といった分析関数を駆使して高速化できるのですが
残念ながら、Accessには分析関数が実装されていません。
したがって、SQL のボトルネックを VBA で補い、
SQL と組み合わせるという Access ならではのテクニックを用いて高速化したのが
hatenaさんの回答になります。
最初に紹介した連番入力する方法を提示しておきます。(ちょっと修正してます。)
まず、テーブル「表1」に数値型のフィールドを追加します。フィールド名は「Seq」とします。
次に、リンク先から、「グループ毎の連番を入力する関数」をコピーして標準モジュールに貼り付けます。
表1のデータを更新したときに、下記のコードを実行します。入力フォームの更新後処理にでも実行するといいでしょう。
下記のようなクロス集計クエリを作成します。名前は「Q1」とします。
このクエリからさらに下記のクエリを作成します。
クエリ2つ、使ってますが、サブクエリを使って一つに纏めることもできます。
クエリはかなり高速になると思います。
100万レコードだと連番入力のコードの実行に時間がかかるかも知れません。その場合は、更新したレコードのEVENTのみを抽出して連番入力するようにするといいでしょう。
表1の入力フォームの更新後処理で実行するとして、下記のようなコードになります。
クエリでやると件数が増えると指数関数的に重くなりますので、そんなもんだと思います。
EVENT と E_Time にインデックスを設定する改善するかも知れません。