Accessの文字列部分一致を合計する関数

質問

お世話になっております。

Access2003の関数で教えてください。

Acces2003のクエリで、

「同じフィールド内で文字列が部分一致した場合、新しいフィールドに
別のフィールドにある数値を合計する、部分一致しない場合は合計せず、
そのままの数値を表示する」
という関数を設定したいと思っております。

具体的に記載しますと、

在庫テーブルがあり、

商品コード　在庫　
aaa 　　　　 10
bbb 　　　　 20
ccc 　　　　 15
ddd 　　　　 30
aaa-1　　　 40
bbb-1　　　 25
ddd-1　　　 20

というデータが入力されております。

この在庫テーブルから新しいクエリで、新しく「在庫合計」フィールド
を作成し、このフィールドに関数を用いて、
『「商品コード」フィールドの左3文字が一致した場合、「在庫」フィールドの
数値を合計する数値を表示させ、一致しない場合は合計せずそのままの
数値を表示する』
という形です。

クエリの実行結果としては

商品コード　在庫　在庫合計
aaa 　　　　 10　　 50
bbb 　　　　 20　　 50
ccc 　　　　 15　　 15
ddd 　　　　 30　　 50
bbb 　　　　 5　　 50
aaa-1　　　 40　　 50
bbb-1　　　 25　　 50
ddd-1　　　 20　　 50

という結果になり、商品コード「aaa」と「aaa-1」や「bbb」と「bbb-1」の「在庫合計」の値
は同じ値（「在庫」フィールドを合計した数値）が表示され、左3文字が一致したデータ
がない「ccc」は15のまま、という結果です。
※ただ、商品コードに重複がございます。（上記例でいうと商品コードが「bbb」です。）

当方VBAは使えないので、クエリで行いたいと思っております。

Accessで難しければExcelの関数でも構いません。

当方初心者ですので、不足な情報がございましたら加筆いたします。

どうぞよろしくお願いいたします。

回答No.1

tsubuyuki · ベストアンサー

アクセスでもエクセルでも、一発でやろうと思うと結構厳しいので、
とりあえずアクセスでクエリを二つ使って何とかこなす方法を紹介します。

まず、集計クエリ「Q_商品分類」を作ります。
フィールドは
・「分類: Mid([商品コード],1,3)」これをグループ化
・「在庫の合計: 在庫」これの合計を算出
ＳＱＬ文だと

SELECT Mid([商品コード],1,3) AS 分類, Sum(Sheet1.在庫) AS 在庫の合計
FROM Sheet1 GROUP BY Mid([商品コード],1,3);

と言う感じです。

これを利用して、DLookup関数を使ったクエリを作ります。
フィールドは
・「商品コード」
・「在庫」
・「在庫合計: DLookUp("[在庫の合計]","Q_商品分類","[分類]='" & Mid([商品コード],1,3) & "'")*1」
以上の三つで、ＳＱＬ文だと

SELECT Sheet1.商品コード, Sheet1.在庫,
DLookUp("[在庫の合計]","Q_商品分類","[分類]='" & Mid([商品コード],1,3) & "'")*1 AS 在庫合計
FROM Sheet1;

こんな感じです。

ひとまず、参考までに。

補足

ご回答ありがとうございます。一点、追加で質問させてください。
「これを利用して、DLookup関数を使ったクエリを作ります。」からの内容なのですが、これは「在庫テーブル」と新しく作った「Q_商品分類」の２つをクエリのデザインビューで表示し、リレーションを設定するということでしょうか？
初歩的な質問かもしれませんが、ご教授ください。
よろしくお願いいたします。

回答No.3

nicotinism

商品コードが３文字限定ですが、

SELECT 商品コード, 在庫, Sum(IIf(Left([在庫],3)=[在庫],[在庫],0)) AS 在庫合計
FROM 在庫テーブル
GROUP BY 商品コード, 在庫;

とかでも？

補足

ご回答ありがとうございました。
SQL文も実はほとんど触ったことがないので、大変申し訳ないですが、クエリのデザインビューでの方法をご教授いただけないでしょうか？
よろしくお願いいたします。
（ただ、今回いただいた内容は、勉強も兼ねて行ってみます。）

回答No.2

30246kiku

提示されたサンプルでは以下のような結果になりますが、それで良いでしょうか

商品コード　在庫　　在庫合計
aaa　　　　10　　　　50
aaa-1　　　40　　　　50
bbb　　　　20　　　　45
bbb-1　　　25　　　　45
ccc　　　　15　　　　15
ddd　　　　30　　　　50
ddd-1　　　20　　　　50

テーブル名を「TJ」と仮定します。
クエリのSQLビューに以下を記述します。

SELECT Q1.商品コード, Q1.在庫, Sum(Q2.在庫) AS 在庫合計
FROM TJ AS Q1 INNER JOIN
(SELECT LEFT(商品コード,INSTR(商品コード & "-","-")-1) AS 頭コード, 在庫 FROM TJ) AS Q2
ON LEFT(Q1.商品コード,INSTR(Q1.商品コード & "-","-")-1)=Q2.頭コード
GROUP BY Q1.商品コード, Q1.在庫;

つまり、元々のテーブルと、「商品コード」左側（"-" 前までの文字）のテーブルもどきを結合して、
集計で在庫合計を求めます。

LEFT(商品コード,INSTR(商品コード & "-","-")-1) AS 頭コード

では、必ず "-" が存在する様にして "-" 前の文字列を取得しておきます。
（ aaa-1 なら aaa を抽出すように・・・）

※ よって、３文字に限定していません（ "-" の左側を対象に・・・・）

そこで、元のテーブルの LEFT(Q1.商品コード,INSTR(Q1.商品コード & "-","-")-1) と
テーブルもどきの LEFT(商品コード,INSTR(商品コード & "-","-")-1) AS 頭コード とを結び付け
元々の「商品コード」と「在庫」でグループ化した際に在庫合計を計算しておきます。

※ この説明でわかったでしょうか？？

※ 左３文字に限定するのであれば、

LEFT(商品コード,INSTR(商品コード & "-","-")-1) AS 頭コード

上記部分を

LEFT(商品コード,3) AS 頭コード

に変更してみてください。

なお、
> ※ただ、商品コードに重複がございます。（上記例でいうと商品コードが「bbb」です。）

この部分は意味不明です。（サンプルに無いデータが実行結果に出てきています）
が、上記クエリでは統合されます。

補足

ご回答ありがとうございました。
SQL文も実はほとんど触ったことがないので、大変申し訳ないですが、クエリのデザインビューでの方法をご教授いただけないでしょうか？
よろしくお願いいたします。
（ただ、今回いただいた内容は、勉強も兼ねて行ってみます。）