Accessで他のテーブルの値を使って更新したい

ACCESS2016です
データベースはSQLSERVERです。（ODBC接続）

テーブルAの構成は
評価ID
点数
評価
の３列があり
1 95 A
2 75 B
3 50 C
4 30 D
5 0 E
のようなデータが保存されています。

テーブルＢの構成は
名前
点数
評価
の３列があり
名前A 60 NULL
名前B 70 NULL
名前C 100 NULL
名前D 20 NULL
名前E 75 NULL
・・・
のようなデータが保存されています。

テーブルAの点数とテーブルBの点数を比べテーブルBの評価を更新したいです。
テーブルAの点数以上の点ならば、その評価で更新したいです。
更新後はテーブルBは以下のような値で更新されます。
名前A 60 C
名前B 70 C
名前C 100 A
名前D 20 E
名前E 75 B
・・・

これは更新クエリー（SQL文）で一度の更新できないでしょうか？
Recordsetなどを利用し、1件ずつ処理しないと駄目でしょうか？

行動規範の内容に同意します

回答2件

ベストアンサー

更新クエリーは更新可能なクエリーである必要があります。
Access クエリおよびフォームでデータを更新するときに発生する可能性のあるエラーのトラブルシューティング方法

一意なインデックスを持たない、または主キーのない Paradox テーブルを含むリンクされた ODBC テーブルがクエリに含まれる場合、クエリ内のデータを更新することはできません。この問題を回避するには、リンクテーブルに主キーまたは一意インデックスを追加します。

ただ、リンクテーブルを含む結合の場合、行単位で都度トラフィックが発生し低速になります。

ですので、SQL SERVER内で完結するようにパススルークエリーを実行した方が良いかと。
ADOX を使用して Access で SQLパススルークエリを作成する方法

データアクセスオブジェクト (DAO) モデルを使用すると、外部データにアクセスSQLクエリを使用してパフォーマンスを向上させることができます。

パススルークエリを作成する
 【MS Access】動的にパススルークエリを作成する

追記

sql文はどのように作成してよいかわからず、書けてません。

それが本題なのですね。
「出来ないのでしょうか？」という質問なので、やってみたけど出来なかったと思いました。

テーブルA（evaluation）のカラム名は
e_iｄ（PK）,e_ｓcore（点数）,e_evaluation（評価）

テーブルB（result）は
m_id（メンバーID）,m_name（メンバー名）,s_id(教科ID）,s_name(教科名）,score（取得点数）evaluation（決定評価）

要点は、evaluationの点数範囲をどのように表現するかです。
以下のSQLで点数範囲を求めます。

SQL
1select *, e_score as lower_score
2     , Nz((select e_score from evaluation as lag where lag.e_id = evaluation.id -1), 100) as upper_score
3from evaluation

上記をQ_evaluationというクエリーにしたとして、更新するクエリーは以下です。

SQL
1update result set 
2  evaluation=(
3    select e_evaluation from Q_evaluation 
4    where result.score between lower_score and upper_score
5  )
6where score is not null

そもそも更新するテーブルはACCESSのテーブルだったという事なので、この際、ODBCリンクテーブルかどうかは関係がありません。

投稿2021/04/21 01:59

編集2021/04/22 02:49

sazi

総合スコア25206

rabupon

2021/04/21 02:05 編集

すみません。テーブルBには名前ではなく名前IDが入っています。これが主キーになります。それとは別に名簿テーブルがあり名前IDと名前が保存されています。パススルークエリーの方がよいのですね。そのSQL文はどのように記述すればよろしいのでしょうか？

sazi

2021/04/21 02:36

SQLSERVERの文法です UPDATE (Transact-SQL) https://docs.microsoft.com/ja-jp/sql/t-sql/queries/update-transact-sql?view=sql-server-ver15

sazi

2021/04/21 02:38

SQLSERVERの文法で作成したSQLを、クエリーの種類を「パススルークエリ」にしたものに張り付けて実行するなどで確認できます。 SQLSERVER上の実行でも確認は出来ますが。

sazi

2021/04/21 02:51

ついでにですけど、更新可能なクエリーについては更新対象が１：１でなければなりませんので、それが原因でエラーとかになったりしていないでしょうか。パススルークエリーが面倒だと思うなら、現状の駄目なSQLなどを質問に追記して下さい。

rabupon

2021/04/22 00:32

またまたすみません。今回のテーブルBはワークテーブルでしたので、ACCESSのテーブルでした。パススルークエリーは利用できませんよね。 Bには科目IDも入っています。テーブルAは科目にかかわらず、どの科目でも同じ評価でよいのですが、テーブルBには同じ名前IDの別科目IDのデータも入っています。これは、更新対象が1:1ではないということですよね。テーブルBをrecodesetで開いて、テーブルAを参照し、評価をセットするのが良いでしょうか？

sazi

2021/04/22 00:55 編集

更新対象の一意キーに対して１：１であれば更新可能です。 SQLで出来そうですけど、エラーになったとかではないのでしょうか？

sazi

2021/04/22 00:56 編集

エラーになっているならそのSQLを質問に追記して下さい。 SQLでの回答が欲しいのなら、カラム名も明示して下さい。

rabupon

2021/04/22 01:25

sql文はどのように作成してよいかわからず、書けてません。テーブルA（evaluation）のカラム名は e_iｄ（PK）,e_ｓcore（点数）,e_evaluation（評価）テーブルB（result）は m_id（メンバーID）,m_name（メンバー名）,s_id(教科ID）,s_name(教科名）,score（取得点数）evaluation（決定評価）となっています。各メンバーが取得した点数をもとに、テーブルA（evaluation）の点数以下で一番高い点数の評価をテーブルB（result）の決定評価にセットしたいのです。

行動規範の内容に同意します