アクセス2010 他テーブルを参照してCount

質問

アクセス2010

以前質問(QNo.7322538)しましたが、再度質問です。

>質問
テーブルA
[F1]
ab1
aa1
ah0
cd1
gh0

テーブルB
[F1] [F2]
a..........0
c..........0
g..........0

テーブルA[F1]の最後１文字が「1」である条件下で、
テーブルA[F1]の全データから、テーブルB[F1]の数をカウントしたい。

[F1] [F2]
a..........2
c..........1
g..........0

>ご回答
DoCmd.RunSQL _
"UPDATE テーブルB SET テーブルB.F2 = " & _
"DCount(""*"",""テーブルA"",""F1 Like '*1' AND Left(F1,1)='"" & [F1] & ""'"");"

これでうまく動くのですが、DCount使用の為か処理時間がかなりかかります。

もっと早くできないでしょうか？

よろしくお願い致します。

回答No.2

hatena1989 · ベストアンサー

ちなみに、テーブルA、テーブルB のレコード件数は何件ぐらいですか。

> >rs2.Index = "F1"
> でつまずき、先に進めませんが、

テーブルB の「F1」フィールドにインデックスは設定してありますか。
設定してないとエラーになります。

一時テーブル（テーブルC）を使うなら、
[C1] フィールドの「インデックス」プロパティを「はい(重複あり)」に設定しておいて、下記のコードを実行でどうでしょうか。

CurrentDb.Execute "DELETE * FROM テーブルC"
CurrentDb.Execute "INSERT INTO テーブルC ( C1 ) " & _
"SELECT Left([F1],1) FROM テーブルA " & _
"WHERE F1 Like '*1';"
CurrentDb.Execute "UPDATE テーブルB INNER JOIN テーブルC " & _
"ON テーブルB.F1 = テーブルC.C1 " & _
"SET テーブルB.F2 = [F2]+1;"

お礼

>テーブルA 5,000件～10,000件
>テーブルB 20件～150件
上記を１セットとして、同じ構造でデータの違うセットが10本です。

>"SET テーブルB.F2 = [F2]+1;"
"SET テーブルB.F2 = テーブルB.F2+1;"にしました。
こんなことができるんですね。
ただ最初は、F2の規定値がNullだったので結果がでませんでしたが「0」の規定値でうまくできました。

結果、
早い！！！瞬時です。
早いというより、処理中って感じがないです。
Count関数はやはり重い処理なんですね。Dcountはもっと？

おかげ様で快適なデータベースになりました。
今後もよろしくお願い致します。

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

回答No.1

hatena1989

まずは、テーブルA の F1 にインデックスを設定しておいて、

DoCmd.RunSQL _
"UPDATE テーブルB SET テーブルB.F2 = " & _
"DCount(""*"",""テーブルA"",""F1 Like '*1' AND F1 Like '"" & [F1] & ""*'"");"

これでダメなら、DAO か ADO で更新する方法です。
テーブルA の F1 と テーブルB の F1 にはインデックスを設定しておいてください。
また、DAOに参照設定が必要です。

Dim stSQL As String
Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset

stSQL = "SELECT [テーブルB].F1, COUNT([テーブルA].F1) AS F2 " & _
"FROM テーブルB INNER JOIN テーブルA " & _
"ON [テーブルA].F1 Like [テーブルB].F1 & '*' " & _
"WHERE [テーブルA].F1 Like '*1' " & _
"GROUP BY [テーブルB].F1;"
Set rs1 = CurrentDb.OpenRecordset(stSQL, dbOpenSnapshot)
Set rs2 = CurrentDb.OpenRecordset("テーブルB", dbOpenTable)
rs2.Index = "F1"
Do Until rs1.EOF
rs2.Seek "=", rs1!F1
rs2.Edit
rs2!F2 = rs1!F2
rs2.Update
rs1.MoveNext
Loop

rs1.Close: Set rs1 = Nothing
rs2.Close: Set rs2 = Nothing

補足

hatena1989様、いつもありがとうございます。

ひとつ目の方法は
>F1 にインデックスを設定　Dcount　
ほとんど変わらず、でした。

ふたつ目の方法
>rs2.Index = "F1"
でつまずき、先に進めませんが、
stSQL文をクリエデザインの「SQLビュー」に書き試してみましたが、速度は変わりませんでした。

そこで、
テーブルＡの文字データをLeft,Rightで分解し、１文字のテーブルを作り
"INSERT INTO テーブルC ([C1]) SELECT Left(テーブルA.[F1],1) FROM 条件"
"INSERT INTO テーブルC ([C1]) SELECT Mid(テーブルA.[F1],2,1) FROM 条件"
"INSERT INTO テーブルC ([c1]) SELECT Mid(テーブルA.[F1],3,1) FROM 条件"

[F1]
a
b
a
a
a
h
c
d
g
h
それをテーブルＢを参照して、Countする方法はないでしょうか？