ACCESSで近似値を求める方法を教えてください。

質問

ACCESS初心者レベルの者ですが、クエリなどについて
教えていただけますでしょうか。

給与計算や社会保険の手続きの仕事をしており、
ACCESS2000で社会保険の随時改定対象者の抽出を
行いたいと思って、テーブルには
(1)毎月給与の各項目（毎月インポート予定）と、
(2)保険料一覧のテーブルを作りました。

また、クエリで改定対象者各人の
（1）3ヶ月平均報酬
をAvg関数で各人の報酬を算出することはできました。

次にこの（1）で算出した3ヶ月平均報酬が
(2)保険料一覧[テーブル]フィールドのどの範囲に該当するかという
クエリを組みたいのですが、どのようなものが考えられるか
教えてください。

例えば、（1）で一番上の215,230円だったら該当するのは
(2)では210,000以上229,999以下の範囲となり、220,000を
抽出してくる。
というものです。

（1）3ヶ月の平均報酬[クエリ]
215,230
234,500
255,000
280,000

(2)保険料一覧[テーブル]
---------------------------------------------------
報酬月額以上　報酬月額以下　標準報酬等級　標準報酬月額
210,000 　　　　　　　　　229,999　　　　　18　　　　220,000
230,000　　　　　　　　　　249,999　　　　　19　　　　240,000
250,000　　　　　　　　　　269,999　　　　　20　　　　260,000
270,000　　　　　　　　　　289,999　　　　　21　　　　280,000
290,000　　　　　　　　　　309,999　　　　　22　　　　300,000
310,000　　　　　　　　　　329,999　　　　　23　　　　320,000
330,000　　　　　　　　　　349,999　　　　　24　　　　340,000
---------------------------------------------------

EXCELでやるとすれば、
Vlookup（3ヶ月の平均報酬A2、保険料一覧A2：G4、4、true）
と検索の型をtrueにして近似値を抽出するのと同じ事をACCESSで
行ないたいということです。

なお当方、恥ずかしながら不勉強の為、VBAやSQL文の書き方や
さらにODBCの設定の仕方などもまだ解からないレベルです。

何卒、ご教授の程、宜しくお願いいたします。

回答No.2

venzou · ベストアンサー

別回答で、関数を使わないクエリ。
レコード数が多い場合は、関数を使うと重くなりますので、
その場合は、こちらの方法を試してみてください。

3ヶ月の平均報酬[クエリ]のフィールド名を「平均報酬」と仮定します。

SELECT [3ヶ月の平均報酬].*, 保険料一覧.*
FROM [3ヶ月の平均報酬] , 保険料一覧
WHERE 保険料一覧.報酬月額以上 <= [3ヶ月の平均報酬].平均報酬
AND [3ヶ月の平均報酬].平均報酬 <= 保険料一覧.報酬月額以下;

-----------------------------------------------------------
基本的にはNo1の回答や、上記のような感じで良いと思いますが、
細かい所で不明な点があります。

・保険料一覧の範囲外のレコードの扱いに関して
・平均報酬の小数点以下の扱いに関して

この仕様によっては、もう少し調整を行う必要があると思います。

-----------------------------------------------------------
例：平均報酬 200,000　←該当なし

範囲外のレコードをどう扱うのか？
・レコードその物を選択しないのか？
・空欄を表示するのか？
・その他

-----------------------------------------------------------
例：平均報酬 229,999.33333　←該当なし
　等級18にも、19にも該当しません。

切捨てか、四捨五入などのルールがあるのでは？

-----------------------------------------------------------
補足：SQL文からクエリを作る方法
クエリの新規作成、デザインビュー。
テーブルは追加しない。
「表示」→「SQLビュー」。
SQL文をコピー＆ペースト。
適当な名前で保存。

お礼

とても丁寧なご回答ありがとうございました。

ご指摘の
・保険料一覧の範囲外のレコードの扱いに関して
・平均報酬の小数点以下の扱いに関して
についてですが、保険料一覧は一部を抜粋しました。
報酬月額以上＆以下には
下限の0円～上限の99,999,999円までの範囲を設けております。
さすがに報酬が99,999,999円を越える事はないと思いますので(^_^;)

小数点以下はINT関数でまるめております。
3ヶ月平均報酬がマイナスになった場合まではまだ考えていないのですが・・・。

ご教授いただいたSELECT～をSQLビューにコピペしてみたところ、
（テーブル名、フィールド名は修正してます。）
以上、以下それぞれでパラメータを要求されてしまいました。

当方の質問の段階で明記しなかった不備があったのですが
この作成しようとしているクエリを開くと、すでに
3ヶ月平均報酬を参照して各報酬月額の範囲から標準報酬月額が
抽出されているものをイメージしておりました。

ですので、もう少し調べてみたいと思います。

回答No.4

venzou

＞以上、以下それぞれでパラメータを要求されてしまいました。

恐らくフィールド名に間違いがあります。

認識できないフィールド名は、パラメータと解釈され、
データの入力を要求されます。

テーブル名、フィールド名をもう一度確認して下さい。
正しければ、イメージ通りの結果になるはずです。

解決できない場合は、クエリの内容（SQL文）や、
テーブル構造など、もう少し詳しく補足して下さい。

お礼

ご指導まことにありがとうございます。

初めは貴殿が明記して下さったSQL文をコピペし
テーブル名、フィールド名を当方のものへと
修正して実行したところ、
クエリ式に演算子がありませんとのエラーが出ておりました。
そして<=[3ヶ月の平均報酬].平均報酬の部分をペーストして
きたのでこの箇所が間違っている事がわかりましたが、

苦し紛れに、フィールド名に[]をつけたところ成功し、
改めてSQLビューを確認したところ、以下のように記述されておりました。

SELECT [3ヶ月の平均報酬].*, 保険料一覧.標準報酬月額, 保険料一覧.標準報酬等級
FROM 3ヶ月の平均報酬, 保険料一覧
WHERE (((保険料一覧.報酬月額以上)<=[3ヶ月の平均報酬].[平均報酬])
AND (([3ヶ月の平均報酬].平均報酬)<=[保険料一覧].[報酬月額以下]));

（）については自動で作成されたものですが、この文で
思い通りの結果が得られました。

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

回答No.3

CHRONOS_0

>EXCELでやるとすれば、Vlookup
Accessはリレーショナルデータベースですから
仕組みそのものがVlookupの親玉のようなものです
他のテーブル(クエリ)を参照するのに関数など必要ありません

SQLが分からないということなのでデザインビューでできる方法で回答します

>各人の報酬を算出することはできました。
このクエリと
>保険料一覧[テーブル]
このテーブルをクエリデザインビューに取り込みます
結合はしません

平均報酬の抽出条件欄に
between 報酬月額以上 and 報酬月額以下

これで平均報酬に該当する標準報酬等級、標準報酬月額
を引っ張ってこれます

お礼

ご回答ありがとうございます。

>>各人の報酬を算出することはできました。
>このクエリと
>>保険料一覧[テーブル]
>このテーブルをクエリデザインビューに取り込みます
>結合はしません

>平均報酬の抽出条件欄に
>between 報酬月額以上 and 報酬月額以下

行なってみたのですが、なぜか何も抽出されませんでした。
もう少しいじってみたいと思います。

回答No.1

mshr1962

DMax("標準報酬月額","保険料一覧","報酬月額以上<=" & 3ヶ月の平均報酬)
または
DLookup("標準報酬月額","保険料一覧","報酬月額以上<=" & 3ヶ月の平均報酬 & "And 報酬月額以下<=" & 3ヶ月の平均報酬)
になるのでは？

お礼

ご回答ありがとうございました。
DMax、DLookupともにビルドで行なってみたのですが、

『指定した式の構文が正しくありません。
たとえば、演算子がないのにオペランドを指定しています』

とエラーになってしまいました。