Access 抽出データ件数のカウント更新について

質問

以下の２つのテーブルを用いて
販売した数を更新したいのですが、
どの様に作成すれば
良いのでしょうか？
クエリーテーブルに結果を出力は出来たのですが、
クエリーテーブルではなく、BUSYO_MSTのM_F_countに
カウント結果を出すには、どこを直せばいいでしょうか。

UPDATE BUSYO_MST
SET M_F_count=(
SELECT Count(M_File.key) AS M_F_count
FROM BUSYO_MST LEFT JOIN M_File ON BUSYO_MST.Key = M_File.key
GROUP BY BUSYO_MST.Key);

=========================
<< BUSYO_MST >>

ID | key | M_F_count
---+--------------
01 | バナナ | 0
02 | りんご | 2
03 | みかん | 1

<< M_File >>

ID | key
---+--------------
01 | りんご
02 | みかん
03 | りんご

回答No.2

piroin654 · ベストアンサー

更新クエリにサブクエリを含むとエラーが生じます。

更新可能なクエリであることが必要
http://support.microsoft.com/default.aspx?scid=kb;ja;328828
の以下の部分。
フィールドの値の合計、平均、カウント、その他の集計値を計算するクエリでは、データを更新で

きません。また、クロス集計クエリ、選択クエリ、集計値や集計関数を含むサブクエリから、[レコ

ードの更新] 行にあるフィールドを参照する更新クエリではデータを更新できません。この問題を

回避するには、更新クエリの [レコードの更新] 行で [定義域集計関数] を使用します。これで、

クロス集計クエリ、選択クエリ、集計値や集計関数を含むサブクエリから、フィールドを参照でき

るようになります。

したがって、個別に定義域集計関数を使用するか、
あるいは、コードでテーブルを一括更新するように
します。

【一括更新の場合】

質問のクエリのサブクエリを少し変更して、

Q集計

SELECT M_File.key, Count(M_File.key) AS M_F_count
FROM M_File
GROUP BY M_File.key;

というクエリQ集計を作成します。
以下を標準モジュールに貼り付け、
イミディエイトウィンドウでtest
を実行してみてください。
なお、DAOを使用しているので
コード表の参照設定で、
Microsoft DAO xx Object Library
のチェックを確認してください。

Sub test()
Dim db As Database
Dim rst As Recordset
Dim rsq As Recordset

Set db = CurrentDb
Set rsq = db.OpenRecordset("Q集計", dbOpenDynaset)
Set rst = db.OpenRecordset("BUSYO_MST", dbOpenDynaset)

If rsq.RecordCount > 0 Then
rsq.MoveFirst
Do Until rsq.EOF
rst.MoveFirst
Do Until rst.EOF
If rsq!Key = rst!Key Then
rst.Edit
rst!M_F_count = rsq!M_F_count
rst.Update
Exit Do
End If
rst.MoveNext
Loop
rsq.MoveNext
Loop
End If
rst.Close: Set rst = Nothing
rsq.Close: Set rsq = Nothing
db.Close: Set db = Nothing
End Sub

また、このプロシージャを適当な名前に
して、フォームなりのボタンクリックで

Pribate Sub ボタン1_Click()
Call test
End Sub

とすることもできます。

お礼

レスありがとうございました。
こちらの方法でやることにしました。

回答No.4

layy

＞SELECT Count(M_File.key) AS M_F_count
＞FROM BUSYO_MST LEFT JOIN M_File ON BUSYO_MST.Key = M_File.key
＞GROUP BY BUSYO_MST.Key

というのは
[Count]
０
２
１
のカウント数の項目だけ、他テーブルと紐付けるキーがありませんね。

それと、
このカウントを求めるのに「BUSYO_MST」との紐付けは必要ですか？。
ここで、紐付けが無くても
りんご　２
みかん　１
という（発生分の）２個のレコードは生成できます。
これを基にBUSYO_MSTを更新する、という手もあります。
バナナ　５→０
りんご　１→０
みかん　３→０
と全部０でクリアしておいて
りんご　２
みかん　１
の情報から
バナナ　５→０
りんご　１→０→２
みかん　３→０→１
です。

いずれにしても、そのエラーメッセージの説明内容をよく見てからです。
「BUSYO_MST」が
カウントするための基になっているテーブルであり、
カウント更新するための更新先のテーブルです。
普通に考えるとそれでうまく行くの？、となりますよね？。

お礼

基本でした。ご教授ありがとうございました。

回答No.3

piroin654

No2です。
No2の最初の、

>更新クエリにサブクエリを含むとエラーが生じます。

は、

更新クエリに集計関数のあるサブクエリを含むとエラーが生じます。

です。

お礼

勉強になりました。
ありがとうございます。

回答No.1

layy

『更新可能なクエリであることが必要です。』
が出たのでは？。

カウント求めるときに使われるテーブルを直接更新している、そんなことになってますか？。

更新用の『SELECT～』文においてはカウントだけの項目です。なので、更新先のテーブルと結合させる項目キーがありませんね？。

いったんテーブルに作成することは考えてますか？。

補足

>『更新可能なクエリであることが必要です。』
>が出たのでは？。

おっしゃられる通りです。

更新可能なクエリであることが必要ですの意味がわかりませんでした。

>更新先のテーブルと結合させる項目キーがありませんね？。

IDを入れないといけないのでしょうか。
まだまだ初心者で、どこに入れればいいのか思いつきません。

>いったんテーブルに作成することは考えてますか？。

考えていません。

試しにBUSYO_MSTを全部一緒に書き出すのと同時には出来ました。
INSERT INTO BUSYO_MST ( [key], F_No, LineCD, 部門名, 部署CD, 部署名, M_F_count )
SELECT DISTINCT M_File.key, M_File.F_No, M_File.LineCD, M_File.部門名, M_File.部署CD, M_File.部署名, Count(M_File.key) AS M_F_count
FROM M_File　GROUP BY M_File.key, M_File.F_No, M_File.LineCD, M_File.部門名, M_File.部署CD, M_File.部署名
HAVING (((M_File.部署CD) Is Not Null));