アクセス ｓｑｌで自動加算したい

質問

アクセス2010です
現在

Me.Filter = グループ別
Me.FilterOn = True

Me.OrderBy = 速い順
Me.OrderByOn = True

jj = 1
Me.Recordset.MoveFirst

While (Not Me.Recordset.EOF)
Me.順番 = jj
jj = jj + 1
DoCmd.RunCommand acCmdSaveRecord
Me.Recordset.MoveNext
Wend

これをSQLで

jj=0

UPDATE テーブル SET 順番 = Inc(jj) Where ～～　Order　～～～～

なんて書けないですか？

回答No.7

hatena1989 · ベストアンサー

> ＞Set rs = CurrentDB.OpenRecordset(stSQL, dbOpenDynaset)
>
> やってみましたが、少しは改善されました。
> というのも、この「While～～Wend」間を通るのは３～７回位です。
> しかしこれを５３回呼び出しています。

特定のグループ（予約西暦年）だけでなく、テーブル全体を更新したいということでしょうか。

だとしたら、下記のようにすれば OpenRecordset 一回で済みますので高速化できます。

Dim jj As Long
Dim atSQL As String
Dim rs As DAO.Recordset
Dim curDate As Date

　　stSQL = "SELECT * FROM 予約クエリ OREDER BY 予約西暦年, 計算"

　　Set rs = CurrentDB.OpenRecordset(stSQL, dbOpenDynaset)

　　Do Until rs.EOF
　　 If curDate = rs!予約西暦年 Then
　　　　　　jj = jj + 1
　　　　Else
　　　　　　jj = 1
　　　　　　curDate = rs!予約西暦年
　　　　End If
　　　　rs.Edit
　　　　rs!順番 = jj
　　　　rs.Update
　　　　rs.MoveNext
　　Loop

これで改善しないなら、
予約西暦年、計算 フィールドにインデックスを設定する。
計算 フィールドが、クエリ上の演算フィールドなら、その式を見直す。
ぐらいでしょうか。

また、全レコードを更新する必要があるかどうかの検討もしてみてはどうですか。
グループ内の更新したレコード以降だけ順番を振り直せばいいはずです。

お礼

おかげさまで、かなり改善されました。

１日の予約欄が３行から７行あります。
例えば日曜は７行あって、その週に定例の予約が複数件発生したとします。
ですので１年分約５３週分をLoopさせていますが、時間順に並び替え。

次の年に、定例の予約を更に適用追加。
こんな感じなのですが、

＞stSQL = "SELECT * FROM テーブル名 WHERE グループ別 OREDER BY 速い順"
＞　　Set rs = CurrentDB.OpenRecordset(stSQL, dbOpenDynaset)
これと、呼び出し先の見直しで体感的に１０位早くなりました。

＞If curDate = rs!予約西暦年 Then
＞　　　　　　jj = jj + 1
＞　　　　Else
＞　　　　　　jj = 1
＞　　　　　　curDate = rs!予約西暦年
＞　　　　End If

処理速度に満足していますので、ご指導のコードはいつかの機会にまた参考させて頂きます。
（バグ混入が怖いので、今回はここまで）

いつも的確なご指導、ありがとうございます。

回答No.6

hatena1989

「DCountが遅い」とよく言われてますが、
DCount自体は遅いというわけではありません。

http://detail.chiebukuro.yahoo.co.jp/qa/question_detail/q1147457126

でも検証されていますが、単純に件数を求めるだけなら、RecordCount とほとんど差はありません。

ただ、順位（連番）を取得するという目的でクエリで使用すると、重くなります。
サプクエリを使うと少しは軽くなりますが、やはり重いです。
自分より前のレコードをカウントするという処理を全レコード分実行することになるので重くなるのです。
テーブル内を繰り返しスキャンすることになりますので。

つまりDCountが遅いのではなく、クエリで順位を取得するには「自分より前のレコードをカウントする」という方法しか無いため重くなるのです。

順位を付ける処理は、SQLより、VBAでレコードセットを開いて先頭から連番を入力していく方法が１スキャンですみますので高速です。

この辺り、結構、誤解があるようですね。

一応、比較するために、SQLで実行する場合のコードも提示しておきますね。

Dim s As String

s = "UPDATE 予約クエリ SET 予約クエリ.順番 = " & _
"DCount('*','予約クエリ','計算<= ' & [計算] & ' AND 予約西暦年 = #" & _
Me.予約西暦年 & "#"") " & _
"Where 予約西暦年 = #" & Me.予約西暦年 & "#;"
Debug.Print s
CurretDB.Execute s

イミディエイトウィンドウの出力が、

UPDATE 予約クエリ SET 予約クエリ.順番 = DCount('*','予約クエリ','計算<= ' & [計算] & ' AND 予約西暦年 = #2014/05/18#') Where 予約西暦年 = #2014/05/18#;

となるようにします。

現状、提示されている情報から推測したものですので、このままでは動かないかもしれません。

回答No.5

hatena1989

だいぶスレッドが進んでいますが、ちょっと横から失礼します。

SQLでできないかという意図は、
質問で提示のコードでは、フォーム上でレコード移動が発生するし、処理が重いので、
それをなんとかしたいということですよね。

だとしたら、

DoCmd.RunCommand acCmdSaveRecord
Me.Recordset.MoveNext

でレコード保存、レコード移動しているのが原因です。
下記の方法に書き換えれば、速度は劇的に改善します。

Dim jj As Long
Dim rs As DAO.Recordset

Me.Filter = グループ別
Me.FilterOn = True

Me.OrderBy = 速い順
　　Me.OrderByOn = True

　　Set rs = Me.Recordset.Clone

　　While (Not rs.EOF)
　　　　rs.Edit
　　　　rs!順番 = jj
　　　　rs.Update
　　　　rs.MoveNext
　　　　jj = jj + 1
　　Wend

　　Me.Requery

Recordset と Recordset.Clone の違いは下記に詳細に解説していますのでご参照ください。

フォームの Recordset, RecorsetClone, RecordSet.Clone の違いとは？ - hatena chips
http://hatenachips.blog34.fc2.com/blog-entry-108.html

なお、フォームに表示する必要がないのなら、下記の方が高速です。

Dim jj As Long
Dim atSQL As String
Dim rs As DAO.Recordset

　　stSQL = "SELECT * FROM テーブル名 WHERE グループ別 OREDER BY 速い順"

　　Set rs = CurrentDB.OpenRecordset(stSQL, dbOpenDynaset)

　　While (Not rs.EOF)
　　　　rs.Edit
　　　　rs!順番 = jj
　　　　rs.Update
　　　　rs.MoveNext
　　　　jj = jj + 1
Wend

グループ別 の部分は正しい条件式になるようにしてください。

SQLで、DCountを使うよりも高速なはずです。

お礼

＞フォーム上でレコード移動が発生するし、処理が重いので、それをなんとかしたいということですよね

そのとおりです。

＞Set rs = Me.Recordset.Clone

思い出しました。以前使ってみて劇的な速度に感心したことがありました。

＞Set rs = CurrentDB.OpenRecordset(stSQL, dbOpenDynaset)

やってみましたが、少しは改善されました。
というのも、この「While～～Wend」間を通るのは３～７回位です。
しかしこれを５３回呼び出しています。

したがって、呼び出す側の改良が必要ということがわかりました。

もう少し頭を捻ってみます。ありがとうございました。

回答No.4

piroin654

(1)

＞日付<= " & [日付] & "の[日付]には
＞フォームのフォーカスされているレコードのフィールドの値が入るみたいです。

"UPDATE 予約クエリ SET 予約クエリ.順番 = " + _
"DCount('*','予約クエリ','計算<= " & [計算] & "') " + _
"Where 予約西暦年 = #" + Str(Me.予約西暦年) + "#;"

DCount('*','予約クエリ','計算<= " & [計算] & "')

　　　　　　　　　↓

DCount('*','予約クエリ','計算<= ' & [計算] & ')

のように、すべてシングルクォーテーションにしてみてください。

日付<= " & [日付] & "

についても、

日付<= ' & [日付] & '

のようにしてみてください。

それから文字列の結合には「+」ではなく「&」を使用
してください。

(2)
それから、

"Where 予約西暦年 = #" + Str(Me.予約西暦年) + "#;"

では、Str(Me.予約西暦年) のようにStr関数で文字列に
変換しているので「#」で囲みません。

"Where 予約西暦年 = " & Str(Me.予約西暦年) & ";"

この場合は Me.予約西暦年　のようにMeキーワードで
フォームのコントロールの値を見に行くの「"」で囲んで
文字列の外に出して参照を可能にします。

なお、Str関数で文字列に変換する意図はわかりませんが。

お礼

>すべてシングルクォーテーションにしてみて
"UPDATE 予約クエリ SET 予約クエリ.順番 = DCount('*','予約クエリ','計算<= ' & [計算] & ') "

試験的に　Where 予約西暦年 ははずしてますが、やはり
DCount('*','予約クエリ','計算<= ' & [計算] & ')
構文エラーになってしまいました。

「No6」様の方法でやってみようと思います。

また、DCountを誤解してました。
今後安心して使えます。

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

回答No.3

piroin654

(1)
まず、

s = "UPDATE 予約クエリ SET 予約クエリ.順番 = DCount('*','予約クエリ','予約西暦年 = #" & [予約西暦年] & "# AND 計算<= " & [計算] & ");"

という文字列の、

UPDATE 予約クエリ SET 予約クエリ.順番 = DCount('*','予約クエリ','予約西暦年 = #" & [予約西暦年] & "# AND 計算<= " & [計算] & ");

というSQL文でクエリとして登録できるか確認してみてください。
その上で回答のSQL文の書式をよく見てください。

それと、質問では、

　　UPDATE テーブル SET 順番 = Inc(jj) Where ～～　Order　～～～～

というようにテーブルの更新となっています。条件が違えば
回答の更新クエリが使えない状況もあります。使用条件は
極力正確に質問に織り込んでください。

(2)
DCountが遅いと言っても実際に大量レコードで試しても
体感できるほどの差はでません。

http://detail.chiebukuro.yahoo.co.jp/qa/question_detail/q1147457126

お礼

AND 計算<= " & [計算] & ");"
AND 計算<= " & [計算] & "');"
「)」の前に「'」シングルコーテーションがなかった為でした.

ただこれだと全レコードを書き換えにいくので
"UPDATE 予約クエリ SET 予約クエリ.順番 = " + _
"DCount('*','予約クエリ','計算<= " & [計算] & "') " + _
"Where 予約西暦年 = #" + Str(Me.予約西暦年) + "#;"
としました。

sqlは通るのですが、順番は全て同一の値になり連番になりません。。

日付<= " & [日付] & "の[日付]には
フォームのフォーカスされているレコードのフィールドの値が入るみたいです。

もう少し、考えてみます。ありがとうございました。

回答No.2

piroin654

No1です。

「速い順」　は日付あるいは時刻としてフィールドは
日付/時刻型としています。そのために

　　日付 <= #" & [日付] & "#

のように、「#」を前後にくっつけています。数値型の
場合だと、

　　日付<= " & [日付] & "

のようになります。

補足

ご回答、ありがとうございます。
うまくいきません。

実際には予約システムです。
sqlをかけたいのも、テーブルでなくクエリです。

同年月日内で予約時間の早い順に順番を付けるというものです。（入力された順ということではなく）

s = "UPDATE 予約クエリ SET 予約クエリ.順番 = DCount('*','予約クエリ','予約西暦年 = #" & [予約西暦年] & "# AND 計算<= " & [計算] & ");"
Debug.Print s
DoCmd.RunSQL s

計算＝予約時*100+予約分

実行すると
Debug.Printで
UPDATE 予約クエリ SET 予約クエリ.順番 = DCount('*','予約クエリ','予約西暦年 = #2014/05/18# AND 計算<= 1030);
DoCmd.RunSQLで
「 DCount('*','予約クエリ','予約西暦年 = #2014/05/18# AND 計算<= 1020);の文字列の構文エラーです」となります。

どこがまずいのでしょうか？

また、Dcountを使わず記述はできないでしょうか？
Dcountは処理が遅いと読んだことがあったので。

宜しくお願い致します。

回答No.1

piroin654

テーブルのフィールドが分からないので、
こちらで設定します。

たとえば、テーブル名を　Tテーブル　として、

ID　（オートナンバー)
名前　（テキスト型）
グループ　（数値型）
日付　（日付/時刻型）
順番　（数値型）

とします。

ID　　　名前　　グループ　　　日付　　　　　順番
1　　　　田川　　　3　　　　2014/06/22
2　　　　佐藤　　　1　　　　2014/03/25
3　　　　中川　　　2　　　　2014/10/12
4　　　　山田　　　2　　　　2014/01/31
5　　　　町田　　　1　　　　2014/09/01
6　　　　相川　　　3　　　　2014/08/30
7　　　　前田　　　3　　　　2014/08/02
8　　　　斉藤　　　1　　　　2014/07/12

のようにテーブルにデータがあるとして、順番というフィールドに
グループ別に日付の早い順に連番を振っていくということでいいでしょうか。

UPDATE Tテーブル SET Tテーブル.順番 = DCount("*","Tテーブル","グループ = " & [グループ] & " AND 日付 <= #" & [日付] & "#");