お世話になっております。
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ベストアンサー
- 回答日時:
アクセスでもエクセルでも、一発でやろうと思うと結構厳しいので、
とりあえずアクセスでクエリを二つ使って何とかこなす方法を紹介します。
まず、集計クエリ「Q_商品分類」を作ります。
フィールドは
・「分類: Mid([商品コード],1,3)」これをグループ化
・「在庫の合計: 在庫」これの合計を算出
SQL文だと
SELECT Mid([商品コード],1,3) AS 分類, Sum(Sheet1.在庫) AS 在庫の合計
FROM Sheet1 GROUP BY Mid([商品コード],1,3);
と言う感じです。
これを利用して、DLookup関数を使ったクエリを作ります。
フィールドは
・「商品コード」
・「在庫」
・「在庫合計: DLookUp("[在庫の合計]","Q_商品分類","[分類]='" & Mid([商品コード],1,3) & "'")*1」
以上の三つで、SQL文だと
SELECT Sheet1.商品コード, Sheet1.在庫,
DLookUp("[在庫の合計]","Q_商品分類","[分類]='" & Mid([商品コード],1,3) & "'")*1 AS 在庫合計
FROM Sheet1;
こんな感じです。
ひとまず、参考までに。
この回答への補足
ご回答ありがとうございます。一点、追加で質問させてください。
「これを利用して、DLookup関数を使ったクエリを作ります。」からの内容なのですが、これは「在庫テーブル」と新しく作った「Q_商品分類」の2つをクエリのデザインビューで表示し、リレーションを設定するということでしょうか?
初歩的な質問かもしれませんが、ご教授ください。
よろしくお願いいたします。
No.3
- 回答日時:
商品コードが3文字限定ですが、
SELECT 商品コード, 在庫, Sum(IIf(Left([在庫],3)=[在庫],[在庫],0)) AS 在庫合計
FROM 在庫テーブル
GROUP BY 商品コード, 在庫;
とかでも?
この回答への補足
ご回答ありがとうございました。
SQL文も実はほとんど触ったことがないので、大変申し訳ないですが、クエリのデザインビューでの方法をご教授いただけないでしょうか?
よろしくお願いいたします。
(ただ、今回いただいた内容は、勉強も兼ねて行ってみます。)
No.2
- 回答日時:
提示されたサンプルでは以下のような結果になりますが、それで良いでしょうか
商品コード 在庫 在庫合計
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 を抽出すように・・・)
※ よって、3文字に限定していません( "-" の左側を対象に・・・・)
そこで、元のテーブルの LEFT(Q1.商品コード,INSTR(Q1.商品コード & "-","-")-1) と
テーブルもどきの LEFT(商品コード,INSTR(商品コード & "-","-")-1) AS 頭コード とを結び付け
元々の「商品コード」と「在庫」でグループ化した際に在庫合計を計算しておきます。
※ この説明でわかったでしょうか??
※ 左3文字に限定するのであれば、
LEFT(商品コード,INSTR(商品コード & "-","-")-1) AS 頭コード
上記部分を
LEFT(商品コード,3) AS 頭コード
に変更してみてください。
なお、
> ※ただ、商品コードに重複がございます。(上記例でいうと商品コードが「bbb」です。)
この部分は意味不明です。(サンプルに無いデータが実行結果に出てきています)
が、上記クエリでは統合されます。
この回答への補足
ご回答ありがとうございました。
SQL文も実はほとんど触ったことがないので、大変申し訳ないですが、クエリのデザインビューでの方法をご教授いただけないでしょうか?
よろしくお願いいたします。
(ただ、今回いただいた内容は、勉強も兼ねて行ってみます。)
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- SQL Server ACCESSで表が作りたく、そのためのSQL文や設定方法を教えてください。 1 2022/08/15 12:28
- SQL Server ACCESSで3ファイルを結合して、表を作成するやり方を教えて下さい。 17 2022/08/15 20:34
- その他(データベース) カラム上の重複を削除するクエリを教えてください 3 2022/04/12 14:11
- その他(データベース) 更新クエリをリンクデータベーステーブルに実行し実行時エラー3362固有インデックスに重複する値が含ま 1 2022/09/21 11:44
- Excel(エクセル) ¥マークを含むパスの処理について(マクロ、または関数) 2 2022/12/25 02:11
- SQL Server ACCESSで複数テーブルを結合して、リストを作る方法を教えてください。 2 2022/08/12 19:32
- JavaScript 指定したパスが現URLに含まれていたら特定要素を削除するJavascriptのコードを教えてください 2 2023/04/27 17:58
- Access(アクセス) Accessテーブルの結合で別々のテーブルのフィールドを組み合わせて値を出す方法について 2 2022/07/20 19:43
- Excel(エクセル) VBAで、シート間の転記するコードを教えてください。 4 2023/03/26 10:43
- Access(アクセス) Access クエリ 同一テーブル内 複数フィールドの同時集計のやり方について 1 2022/05/18 19:01
このQ&Aを見た人はこんなQ&Aも見ています
-
プロが教える店舗&オフィスのセキュリティ対策術
中・小規模の店舗やオフィスのセキュリティセキュリティ対策について、プロにどう対策すべきか 何を注意すべきかを教えていただきました!
-
Accessのマクロでモジュールを実行させたい。
Access(アクセス)
-
<ACCESS>別テーブルのレコードのテキストデータが中に含まれるレコードを抽出する方法を教えてください
Access(アクセス)
-
Access で 特定の文字の個数をしりたい
Access(アクセス)
-
-
4
AccessからExcelのファイルを起動する
その他(データベース)
-
5
アクセスVBAのMe!と[ ]
Access(アクセス)
-
6
Access:クエリーにて集計後に文字列結合したい。
その他(データベース)
-
7
Access 条件に合うレコード数をカウントできません
Excel(エクセル)
-
8
ACCESS 重複データを1つだけ表示したい(初心者です)
Access(アクセス)
-
9
ACCESSで空白のデーターをクエリで判定/識別する方法を教えてくださ
Access(アクセス)
-
10
ACCESS更新クエリで型変換エラー
Access(アクセス)
-
11
アクセスのエラー「クエリには出力フィールドが1つ以上必要です。」
その他(Microsoft Office)
-
12
Accessクエリ特定文字列のカウントについて
その他(データベース)
-
13
ACCESSのテーブル名をリストにしたい
Visual Basic(VBA)
-
14
ACCESSでVBAから選択クエリの抽出条件を指定したい
Access(アクセス)
-
15
クエリのデータをテーブルに入れたい
Access(アクセス)
-
16
ACCESSでコントロールソースの変更
Access(アクセス)
-
17
ACCESSで値を代入できないとは?
Visual Basic(VBA)
-
18
アクセスで追加した項目に全て同じ値を一発で入れたい
Access(アクセス)
-
19
Accessのフィールド名に半角括弧を使ってしまった
Visual Basic(VBA)
-
20
テキストボックスにクエリ結果を表示させたい
その他(データベース)
関連するカテゴリからQ&Aを探す
おすすめ情報
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
過剰なオブジェクト指向脳から...
-
フィルターかけた後、重複を除...
-
Exel VBA 別ブックから該当デ...
-
1、Rstudioで回帰直線を求める...
-
pythonにてseleniumを使うも、...
-
変数名「cur」について
-
VB6で読み上げをしたい
-
PreviewKeyDownイベントが2回...
-
VB6のComboBox関連の書き方をVB...
-
ユーザーフォームに2つのコン...
-
ユーザーフォームで銀行に対応...
-
chatgptでつくってもらったコー...
-
sinカーブの表示のさせ方
-
access2003 クエリSQL文に...
-
Excel VBAについて
-
Gitのクローンについて
-
ExcelのVBAコードについて教え...
-
python コードについて(初学者...
-
パイソンプログラム
-
COBOLの文法
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
フィルターかけた後、重複を除...
-
access2003 クエリSQL文に...
-
1日に1人がこなせるプログラム...
-
Exel VBA 別ブックから該当デ...
-
pythonにてseleniumを使うも、...
-
ExcelのVBAコードについて教え...
-
ExcelのVBAコードについて教え...
-
chatgptでつくってもらったコー...
-
欠番の抽出について
-
JANコードとPOSコードは同じ?
-
JavaScriptの定数名が取り消し...
-
1、Rstudioで回帰直線を求める...
-
特定行の背景色を変えたいのですが
-
変数名「cur」について
-
PreviewKeyDownイベントが2回...
-
将来AIが進歩してくるにつれて...
-
ACCESSユニオンクエリでORDER B...
-
VBAでファイルオープン後にコー...
-
COBOLの文法
-
Nullの使い方が不正です。
おすすめ情報