Accessで名寄せグループの関係人を抽出する方法について

質問

先日からAccessを使ってある顧客データから条件抽出していますが、名寄せグループの抽出方法が分からず悩んでおります。

具体的には、全員で8人の顧客データがあります。
仮に名前を　A-1,A-2,A-3,B-1,C-1,C-2,C-3,C-4,D-1,D-2　とします。
A,B,C,Dは名寄せグループを意味します。
A=3人、B=1人、C=4人、D=2人となります。
夫々に顧客コードと名寄コードを持っています。

(1)ある条件でA-1,C-2,D-1,D-2が抽出されるクエリ１を作成しました。
(2)次にA-1,C-2,D-1,D-2に関係する名寄せグループの他の顧客コードを抽出したいのですが、この方法が分かりません。
なお、A-2,A-3,C-1,C-3,C-4だけが抽出され、A-1,C-2,D-1,D-2は含まない方法があればBESTです。

当方Access初心者なので、出来るだけ判りやすく教えて頂けると助かります。できれば、初心者なのでデザインビューで分かりやすく教えて頂けると助かります。どうか宜しくお願いいたします。

回答No.13

noname#79209 · ベストアンサー

#1です。
「クエリ１」で選択された顧客の「名寄せコード」を網羅した別クエリが必要です。
１．クエリを新規作成します。
２．「クエリ１」を追加します、
３．Accessウインドのメニューバーにある「表示」をクリックして、
　　サブメニューの「集計」をクリックします。
４．「名寄せコード」を下表に展開します。イメージは以下です。
　フィールド　名寄せコード
　 テーブル　クエリ１
　　　　集計　グループ化
　 並べ替え
　　　　表示　　　　V
　 抽出条件
　 　　　または　

５．「クエリ３」と名前をつけて保存して閉じます。

ここで「クエリ２」を修正する手順を書いても良いのですが、
かえって混乱しますので、「クエリ２」を削除したうえで、
新たに作り直すことにします。
１．クエリを新規作成します。
２．「顧客データ」と「クエリ１」と「クエリ３」を追加します、
３．「顧客データ」の「店番」フィールドを
　　「クエリ１」の「店番」フィールド上にドラッグして、
　　双方の「店番」フィールドが線で結ばれるようにします。
４．同じように、「顧客データ」の「顧客コード」フィールドを
　　「クエリ１」の「顧客コード」フィールド上にドラッグして、
　　双方の「顧客コード」フィールドが線で結ばれるようにします。
５．「店番」同士を結んだ線の中央あたりをダブルクリックして、
　　「結合プロパティ」ダイアログの「２」をクリックして●を付け
　　「OK」をクリックします。
　　結ばれた線が、
　　「顧客データ」の「店番」から「クエリ１」の「店番」への矢印に変化します。
７．同じように、「顧客コード」同士を結んだ線の中央あたりをダブルクリックして、
　　「結合プロパティ」ダイアログの「２」をクリックして●を付け
　　「OK」をクリックします。
　　結ばれた線が、
　　「顧客データ」の「顧客コード」から「クエリ１」の「顧客コード」への矢印に変化します。
８．「顧客データ」から、すべてのフィールドを、下のフィールドリストへ展開します。
９。「クエリ１」から、「店番」フィールドと「顧客コード」フィールドを、
　　下のフィールドリストへ展開します。
10．「クエリ１」の「店番」「顧客コード」の各「抽出条件」セルに「Is Null」と入力します。
　フィールド　店番　　　顧客コード
　 テーブル　クエリ１　　クエリ１
　 並べ替え
　　　　表示
　 抽出条件　Is Null　　　Is Null
　 　　　または

　　※「表示」セルのチェックは外してもかまいません。単に抽出用なので。。。

ここまでの設定で、「クエリ１」に入っていないすべての顧客が抽出されています。
当然、「クエリ１」に全く関係のない世帯の顧客も含まれています。

11．「顧客データ」の「名寄せコード」フィールドを
　　「クエリ３」（!!!「クエリ１」ではありません!!!）の
　　「名寄せコード」フィールド上にドラッグして、
　　双方の「名寄せコード」フィールドが線で結ばれるようにします。

この設定で、「クエリ１」と同じ「名寄せコード」を持つ顧客に絞り込まれます。

12.さらに絞り込みたい項目の設定を行います（年収500万以上など）。

13．名前をつけて保存します

私の実験用データではうまくいきました。

お礼

vizzar様
出来ました！！！・・・凄いです！！！
今、凄く感動してます！！
長い期間、本当にありがとうございます。
何とお礼を言ったら良いのか・・・・
本当に、本当に、感謝しております。

一週間超の間、毎日この事ばかり考えておりました。
最後の方は、少し諦めかけておりましたが、vizzar様のお陰で助かりました。
しかし、本当にAccessって凄いですね！！

これからも仕事で使用する事があると思いますが、一生懸命勉強していきたいと思います。
この度は、本当にありがとうございました。

回答No.16

noname#79209

#1です。
最後の忠告として訊いてください。

「クエリ１」と「クエリ２」を合体し、１つのクエリですまそうとするのは、
　　　　「　不　・　可　・　能　」です。

UNIONクエリの説明にそれらしきことが書いてあったのでしょうが、
意味を全く取り違えています。

質問者さんがやろうとしていることの、うまい例えを一日中考えていました。
結果、思いついたのが、

二つの鏡を「合わせ鏡」で使って、自分の後ろ姿を見ていて、
「自分の前にある鏡に、後ろの鏡が映っているだけじないか」と
鏡を一つだけにして、後ろを振り向けば、自分の後ろ姿が見えるだろうろと勘違いしている。

という例えです。
あとは、ご随意に．．．

お礼

お返事ありがとうございます。
なるほど、何となく分かるような気がします。
実際、ダミーでやってみましたが、少し変な動きがあったので、ミラーでループしてしまうような感じでしょうかね？
いずれにしても、本番データでは利用するつもりはありません。

いろいろとご丁寧なアドバイスに、本当に感謝しております。
ありごとうございました。
また、機会がありましたら宜しくお願いいたします。

回答No.15

noname#79209

#1です、
> 「クエリ１」と「クエリ２」を合体することって、Accessでも可能なのでしょうか？
AccessでもExcelでも「論理的」に無理です。
もし、可能なら最初から提示しています。
合体という意味が「ひとつのアクションで」なら、フォームとVBAを使うことで可能となります。
また「クエリ１」と「クエリ３」を合体することはできます。
でも合体してしまうと、「クエリ１」の結果を見ることができなくなり、結局また分離して使うようになるのががオチです。

何でもそうですが、何かひとつに決め打ちしてしまうと、えてして別の側面で齟齬が生じるものです。
フレキシビリティーを残しておくのもシステム構築のコツといえます。

お礼

いろいろとアドバイスありがとうございます。
合体については、他の検索では、SQLで「UNION ALL」を使用すると出来そうなので、挑戦してみたいと思います。
いずれにせよ、これからもいろいろ勉強していきたい思います。
この度は、本当にありがとうございました。

回答No.14

noname#79209

#1です。
お役に立てたようで、何よりです。
私がもっと速くポカに気がついていれば、
もうすこし早く解決したと思います。

なお、この「Is Null」を使った手法は、
結構使いますので、覚えておかれると
何かと便利です。

お礼

ご丁寧にありがとうございます。
本当に感謝しております。
早速、仕事に役立ておりますが、また難題が出てきそうです・・・。
その時は、申し訳ありませんが宜しくお願いいたします。

ちなみに、「クエリ１」と「クエリ２」を合体することって、Accessでも可能なのでしょうか？
Excelで行っても良いのですが、折角Accessを覚えたので出来ないものかと思いまして・・・。

回答No.12

noname#79209

#1です。
原因がわかりました。私の根本的なポカです。
クエリ１で同一の「名寄せコード」の顧客が複数選択されていると
この現象が起きます。
従って、個人コードが異なることを利用しての選別は意味がなかったのです。

急遽考え直します。
ゴメンナサイ。

回答No.11

noname#79209

#1です。
「式1:」が復活してしまうのは、
　フィールド　[顧客データ]![店番] & [顧客データ]![顧客コード]
　 テーブル
　 並べ替え
　　　　表示　　　　□
　 抽出条件　<>[クエリ1]![店番] & [クエリ1]![顧客コード]
　 　　　または
の「表示」にチェックが入っているからでしょう。

「クエリ１」側にも「店番」と「顧客コード」はありますよね？
まあ、無ければエラーになるでしょうから．．．

あと考えられるのは、
・「抽出条件」セルの頭に「<>」をつけていない。
・「フィールド」セルと「抽出条件」セルのテーブル名に同じものを設定しているとか。
　「フィールド」セル側のテーブル名は「[顧客データ]![店番]」「[顧客データ]![顧客コード]」
　「抽出条件」セル側のテーブル名は「[クエリ1]![店番]」「[クエリ１]![顧客コード]」
　です。
　「思いこみ」って案外気づきにくいものですよ。
　　コピペで設定していると、案外以下のようになっていたりして、
　　「[クエリ１]![店番]&[顧客データ]![顧客コード]」

質問者さんのせいにするつもりはありませんが、こういうものは
十中八九、どこかに記入ミスがあるのだと思います。

お礼

何度も本当にありがとうございます。
当方も何回も点検のうえ実行しておりますが、原因がわかりません。
別のダミーを作成してみても、同じような結果となります。

そこで少し分かったのが、
顧客データの家族が２人で、その内「クエリ１」で１人抽出されている場合は、他の１人のみが抽出されています。
しかし、３人家族で２人が抽出されている場合は、どうやら抽出されている２人毎に他の２人を抽出されているようです。
例えば、
父、母、長男で、父、母が「クエリ１」で抽出された場合、
「クエリ２」では、父、母、長男の３人が抽出されてしまいます。
（父の他の人→母、長男）
（母の他の人＝父、長男）
の理論になっていると思われます。
これは、やはりどこか設定に問題あるのでしょうか？

また、『「式1:」が復活してしまうのは、「表示」にチェックが入っているからでしょう。』は、
当方のAccessでは、「表示」のチェックをはずしても復活してしまいます。
Accessに問題があるのでしょうか？
よろしくお願いいたします。

回答No.10

noname#79209

#1です。
もしかするとステップ６の設定を間違えているのかも。
また、私の方も「式1：」というフィールド名はいらないようですね。
デザインビューでのイメージは、以下のようになっていればOKです。

　フィールド　[顧客データ]![店番] & [顧客データ]![顧客コード]
　 テーブル
　 並べ替え
　　　　表示
　 抽出条件　<>[クエリ1]![店番] & [クエリ1]![顧客コード]
　　　または

#8では
式1：[顧客データ]![店番] & [顧客データ]![顧客コード]
とかきましたが、
実際は「式1：」の部分はいらないようです。
試してみてください。

お礼

早々にありがとうございます。
上記のとおり設定しておりますが、やはり変わりはないですね・・・。
考え方は大変良くわかります。
私も、この設定で良いと思うのですが、うまく結果が展開されません。

なお、私のAccess2003では、
フィールドの[顧客データ]![店番] & [顧客データ]![顧客コード]の「式1：」を削除しても自動で回復してしまい、
「式1：[顧客データ]![店番] & [顧客データ]![顧客コード]」になってしまします。

恐らく抽出条件の設定処理がうまく反映していないと思いますが、原因がわかりません。
何がいけないのか、いろいろ試してみたいと思います。

回答No.9

noname#79209

#1です、
> 名寄せコード - 店番 - 顧客コード
> 110001 - 1 - 10002
> 110001 - 1 - 10003
> 110001 - 1 - 10001
> 110001 - 1 - 10003
> 110001 - 1 - 10001
> 110001 - 1 - 10002
> 以上のように重複して抽出されてしまいます。

#8のステップ「６」も設定していますか？

#8のステップ５までしか設定せずに見ているなら、当たり前のことです。
ステップ５まででは、「クエリ１」と「名寄せコード」が同じレコードがすべて、しかもダブって抽出されています。
少なくとも第１段階ではこの状態が必要なのです。
この状態で、さらにステップ６を設定して、初めて、「クエリ１」の顧客を除外できるのです。
要するにステップ６では、このダブったものもすべて含まれた状態から、
「店番+顧客コード」が「クエリ１」の「店番+顧客コード」と異なるものだけを抽出すれば、お望みの結果が出ます。

DBシステムの基礎がわかっていないと、なかなかイメージをつかみ難いのですが．．．

回答No.8

noname#79209

#1です。
実験してみましたが、うまく動くようです。
１．クエリを新規作成します。
２．「顧客データ」と「クエリ１」を追加します、
３．「顧客データ」の「名寄せコード」フィールドを
　　「クエリ１」の「名寄せコード」フィールド上にドラッグして、
　　双方の「名寄せコード」フィールドが線で結ばれるようにします。
４．「顧客データ」から、すべてのィールドを、下のフィールドリストへ展開します。
５．フィールドリストのあいている列（つまり展開した最後尾フィールドの右の列）の
　　フィールドセル上で右クリックして「ビルド」を選択。
　　式1:[顧客データ]![店番] & [顧客データ]![顧客コード]
　　と入力して「OK」をクリック。
６．「式1」の抽出条件セルに
　　<>[クエリ1]![店番] & [クエリ1]![顧客コード]
　　と入力します。
※ここまでの設定で、
　・「クエリ１」と同じ「名寄せコード」を持ち、
　かつ
　・「クエリ１」以外の顧客
　を抽出する設定になっています。
　これ以降の設定は、単なる絞り込みです。お好きな項目でどうぞ。

７．年収で絞り込みたければ「年収」の抽出条件セルに
　　>=500
　　と入力します。(年収フィールドは100万単位として書いています)
８．名前をつけて保存します。

一応解説すると、
１～４で「クエリ１」と同一の「名寄せコード」のレコードが抽出されます。
当然この状態では「クエリ１」の顧客も含まれています。
５～６で「クエリ１」の顧客を除外するために
「顧客データ」の「店番」＋「顧客コード」と、「クエリ１」の「店番」＋「顧客コード」が「等しくない」レコードを抽出しています。

お礼

お忙しいところ本当にありがとうございます。
考え方は一致していると思いますので、私もこの設定でいけると思い早速実行してみました。
しかし、私の設定ミスか結果に不具合があります。
具体的には、
同じ「名寄コード」を持つている人が３人います。
「クエリ１」では、３人とも抽出されています。
仮に、「名寄せコード」を110001とします。
３人の「店番」は１、「顧客コード」は、10001、10002、10003とします。
その場合、「クエリ２」の抽出結果が以下のようになってしまいます。
名寄せコード - 店番 - 顧客コード
110001 - 1 - 10002
110001 - 1 - 10003
110001 - 1 - 10001
110001 - 1 - 10003
110001 - 1 - 10001
110001 - 1 - 10002
以上のように重複して抽出されてしまいます。
恐らく、
110001 - 1 - 10001 以外の顧客コードとして、110001 - 1 - 10002、110001 - 1 - 10003
110001 - 1 - 10002 以外の顧客コードとして、110001 - 1 - 10001、110001 - 1 - 10003
110001 - 1 - 10003 以外の顧客コードとして、110001 - 1 - 10001、110001 - 1 - 10002
が抽出されているようです。
お手数をお掛けしますが、どの辺に問題があるのか分かりますでしょうか？
どうそ宜しくお願いいたします。

回答No.7

noname#79209

#1です。
> 仮に求めている結果を「クエリ２」とした場合、「クエリ２」からは除外されても結構です。
> つまり「クエリ２」と「クエリ１」の顧客は重複しないと言うことです。
なるほど。了解しました。双方の考え方の整合性がとれたようですね。
ただし、本日は外出しますので、お返事は夕方になってしまいそうです。
悪しからず．．．

回答No.6

noname#79209

#1です。
質問文では、以下のように言われています。
---引用---ここから
1)ある条件でA-1,C-2,D-1,D-2が抽出されるクエリ１を作成しました。
(2)次にA-1,C-2,D-1,D-2に関係する名寄せグループの他の顧客コードを抽出したいのですが、この方法が分かりません。
なお、A-2,A-3,C-1,C-3,C-4だけが抽出され、A-1,C-2,D-1,D-2は含まない方法があればBESTです。
---引用---ここまで

にもかかわらず、#5へのコメントで
---引用---ここから
３．「クエリ１」で抽出された人は絶対条件（優先条件）です。（年収に関係なく除外はできません。）
　要するに、２のような抽出ができれば結構です。
---引用---ここまで

矛盾していませんか？

お礼

お返事ありがとうございます。
見捨てられたのかと思い心配していました・・・。

おっしゃる通り、矛盾してますね・・・すいませんでした。
『３．このとき、「クエリ１」で抽出された中に500万以上の人がいても「除外してもかまわない」』に対して、「クエリ１」から除外してしまうのと勘違いしました。
仮に求めている結果を「クエリ２」とした場合、「クエリ２」からは除外されても結構です。
つまり「クエリ２」と「クエリ１」の顧客は重複しないと言うことです。

また、No.５のお礼の事例の場合、
(1)20歳の女性で年収500万以上の人（クエリ１の家族）・・・クエリ２の抽出対象
(2)20歳の女性で年収500万以上の人（クエリ１の家族がいない）・・・クエリ２に抽出されない
(3)40歳の男性で年収100万の人（クエリ１の家族）・・・クエリ２に抽出されない（年収500万未満のため）
となります。

補足

お礼の補足です。

『(2)20歳の女性で年収500万以上の人（クエリ１の家族がいない）・・・クエリ２に抽出されない』は、
正確には・・・クエリ１にもクエリ２にも抽出されない。