Accessの更新クエリについて質問です。

質問

よろしくお願いします。
集計したクエリを元に更新クエリを実行すると、更新できません。
やりたい作業としては、売上データテーブルのレコード数を得意先ごとにカウントして、得意先マスタテーブルのレコード件数フィールドを更新する作業です。
追加クエリで一度テーブルに追加してから、テーブル同士で更新も考えたんですが、レコード数が多いためにＤＢのサイズが爆発的に増えて廃案になりました。
何か良い方法があれば教えてください。

回答No.4

noname#140971

どうしても、逐一、全レコードを再計算するのであれば・・・。

UPDATE 得意先マスター SET 売上件数 = DBCount("*","売上伝票","[得意先マスター_ID]=" & [ID]);

これを＜更新クエリ＞にすればいいです。

DCount関数を利用する場合よりも３倍速で実行します。

Public Function DBCount(ByVal strField As String, _
　　　　　　　　　　　　ByVal strTable As String, _
　　　　　　　　　　　　Optional ByVal strWhere As String = "") As Variant
On Error GoTo Err_DBCount
　　 Dim N
　　 Dim strQuerySQL As String
　　 Dim rst　　　　 As ADODB.Recordset

　　 Set rst = New ADODB.Recordset
　　 strQuerySQL = "SELECT COUNT(" & strField & ") FROM " & strTable
　　 If Len(strWhere) > 0 Then
　　　　 strQuerySQL = strQuerySQL & " WHERE " & strWhere
　　 End If
　　 With rst
　　　　 .Open strQuerySQL, _
　　　　　　　 CurrentProject.Connection, _
　　　　　　　 adOpenStatic, _
　　　　　　　 adLockReadOnly
　　　　 If Not .BOF Then
　　　　　　 .MoveFirst
　　　　　　 N = Nz(.Fields(0), 0)
　　　　 End If
　　 End With
Exit_DBCount:
On Error Resume Next
　　 rst.Close
　　 Set rst = Nothing
　　 DBCount = N
　　 Exit Function
Err_DBCount:
　　 MsgBox "SELECT 文の実行時にエラーが発生しました。(DBCount)" & Chr$(13) & Chr$(13) & _
　　　　　　"・Err.Description=" & Err.Description & Chr$(13) & _
　　　　　　"・SQL Text=" & strQuerySQL, _
　　　　　　vbExclamation, " 関数エラーメッセージ"
　　 Resume Exit_DBCount
End Function

回答No.3

noname#140971

服飾デザイナーですので妥当な回答か否かは定かじゃありませんが・・・。

私なら

１、そもそも、[得意先マスター]に列[売上件数]は設けない。
２、仮に設けるならば、[売上伝票入力フォーム]を閉じる時に更新する。

＜得意先マスター＞

ID___氏名__________売上件数
1____鈴木 一郎_____________0
2____中村 主水_____________0

＜売上伝票＞

ID___伝票番号_______日付____________得意先マスター_ID__集計
1____2007110001___2007/11/01________________________1__False
2____2007110002___2007/11/02________________________2__False
3____2007110003___2007/11/03________________________1__False
4____2007110004___2007/11/04________________________1__False

＜フォーム[売上伝票]＞

Private Sub Form_Close()
　　Dim intCount As Integer
　　Dim strSQL　 As String
　　Dim rst　　　As ADODB.Recordset

　　strSQL = "SELECT 得意先マスター_ID, 集計 FROM 売上伝票 WHERE NOT 集計"
　　Set rst = New ADODB.Recordset
　　With rst
　　　　.Open strSQL, _
　　　　　　　CurrentProject.Connection, _
　　　　　　　adOpenStatic, _
　　　　　　　adLockOptimistic
　　　　If Not .BOF Then
　　　　　　.MoveFirst
　　　　　　Do Until .EOF
　　　　　　　　intCount = DBCount("x", "xxxx", "xxxxx" & .Fields(0))
　　　　　　　　CnnExecute "UPDATE [得意先マスター] SET 売上件数=" & ・・・・
　　　　　　　　.Fields(1) = True
　　　　　　　　.Update
　　　　　　　　.MoveNext
　　　　　　Loop
　　　　End If
　　 End With
End Sub

今、テストしてみましたが、これ位のコードを書けば事足りるようです。

＜得意先マスター＞

ID___氏名__________売上件数
1____鈴木 一郎_____________3
2____中村 主水_____________1

＜売上伝票＞

ID___伝票番号_______日付____________得意先マスター_ID__集計
1____2007110001___2007/11/01________________________1__True
2____2007110002___2007/11/02________________________2__True
3____2007110003___2007/11/03________________________1__True
4____2007110004___2007/11/04________________________1__True

回答No.2

nobu-

>集計したクエリを元に更新クエリを実行すると、更新できません。

バージョンにもよりますが、「集計したクエリ」から「更新クエリ」ではなく、「テーブル作成クエリ」を実行してはいかがでしょうか？
その場合も、「選択（集計）クエリ」から「テーブル作成クエリ」に変更後、いったんデザインクエリを閉じ、データーベースウィンドウのクエリをダブルクリックしてみては・・・

いかがでしょうか？

回答No.1

mshr1962

DCount関数で件数をカウントすればいいのでは？

得意先コードが数値型（長整数）の場合
DCount("*","売上データテーブル","[得意先コード]=" & [得意先コード])

得意先コードがテキスト型の場合
DCount("*","売上データテーブル","[得意先コード]='" & [得意先コード] & "'")

http://www.accessclub.jp/supbeg/057.html