Access を利用した大量データにおけるマッチング

質問

Access は今まで使っておらずエクセルを用いていたのですが、今回、100万超のデータ数をもつデータベースを扱うことになりました。

データはある種の取引で、自分から相手への金額の流れと、相手から自分への流れが記録されています。更に、品目も含まれています。
このデータをマッチングさせて、相手・品毎に利益を計算するような手順について、Accessについては初心者なのでなるべく詳しくご教授頂きたいです。

やりたいことを具体的に言えば、例えば、ある月において

[売却]
年・月　相手　品番　金額
2001.1　A社　0001　10000
2001.1　A社　0002　 8000
2001.1　B社　0005　20000
...

[購入]
年・月　相手　品番　金額
2001.1　A社　0002　4000
2001.1　A社　0003　6000
2001.1　C社　0004　5000
...

というようなデータを

年・月　相手　品番　売却代金　購入代金
2001.1　A社　0001　10000　0
2001.1　A社　0002　 8000　4000
2001.1　A社　0003　0　6000
2001.1　B社　0005　20000　0
2001.1　C社　0004　0　5000
...

とするような操作です。

それではよろしくお願いいたします。

回答No.2

O_cyan · ベストアンサー

簡単にやるには
[年・月]・[相手]・[品番]・[売却代金]・[購入代金]の各フィールドがあるテーブルを作りそのテーブルに売却テーブルのレコードを追加する。[年・月]・[相手]・[品番]はそのままフィールドへ[金額]は[売却代金]に追加します。
購入テーブルも同じテーブルに追加します。[年・月]・[相手]・[品番]はそのままフィールドへ[金額]は[購入代金]に追加します。
そのテーブルを元にクエリを作り相手と品番でグループ化し売却代金と購入代金を合計にします。クエリのフィールドに利益というフィールドを作り演算にして売却金額から購入金額の合計を引きます。
利益：売却金額-購入金額
で利益を求めることが出来ます。

クエリで処理するにはUNIONクエリと集計クエリがあれば出来ます。
SELECT 年・月,相手,品番,(1) AS 区分
FROM 売却
UNION ALL SELECT 年・月,相手,品番,(2) AS 区分
FROM 購入
ORDER BY 年・月,相手;
年・月と相手を基準に結合させ売却と購入の年・月,相手,品番を結合して両方のテーブルのフィールドを一緒にします。区分は売却なら1購入なら2というフラグを立てるようにします。
このUNIONクエリを使って年・月と相手をリレーションし売却と購入から金額のレコードを取得して売却代金・購入代金のフィールドを作りグループ化し集計させるクエリを作ります。
売却代金のフィールドに
売却代金: Sum(IIf([UNIONクエリ名.区分]=1,[売却.金額],Null))
として集計は合計
購入代金のフィールドに
購入代金: Sum(IIf([UNIONクエリ名.区分]=2,[購入.金額],Null))
として集計は合計し仕訳るようにします。
利益は利益：売却代金-購入代金　で求めます。
SQLにすると
SELECT
[UNIONクエリ名].年・月, [UNIONクエリ名].相手,[UNIONクエリ名].品番,Sum(IIf([UNIONクエリ名.区分]=1,[売却.金額],Null)) AS 売却代金, Sum(IIf([UNIONクエリ名.区分]=2,[購入.金額],Null)) AS 購入代金, [売却代金]-[購入代金] AS 利益
FROM ([UNIONクエリ名] LEFT JOIN 売却 ON ([UNIONクエリ名].年・月 = 売却.年・月) AND ([UNIONクエリ名].相手 = 売却.相手)) LEFT JOIN [購入] ON ([UNIONクエリ名].年・月 = [購入].年・月) AND ([UNIONクエリ名].相手 = [購入].相手)
GROUP BY [UNIONクエリ名].年・月, [UNIONクエリ名].相手,[UNIONクエリ名].品番;

これで出来ます。
こんな感じで良いと思います。

お礼

ぽむ、と手を打って「この手があったか」と思ったのがこの解答です。この回答を見てから一時間しないうちにマッチングの作業を完了することができました。

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

回答No.3

noname#14314

Accessで820万件のテーブルを扱っていますが、特に問題ありません。このテーブルの容量は１ＧＢ強なのですが、Accessのmdbファイルは２ＧＢが上限のようですので、ご注意ください。

　ご質問の内容は古典的なマッチング処理で、VBAを使えばスマートにできると思いますが、ここでは、クエリーを用いる方法をご提案します。

　前提として、ご質問の中で[売却]となっている部分をT_売却テーブル、[購入]となっている部分をT_購入テーブルとします。両テーブルのフィールド構成は、年・月、相手、品番、金額とします。そして新たに作業用のテーブルとして、T_売却・購入テーブルを作成します。このT_売却・購入テーブルのフィールド構成は、年・月、相手、品番とします。

（１）T_売却テーブルからのデータ抽出
　空のT_売却・購入テーブルにT_売却テーブルのデータを抽出します。無条件で抽出する場合のクエリーは、SQLビューで書くと、

INSERT INTO T_売却・購入 ( 年・月, 相手, 品番 )
SELECT T_売却.年・月, T_売却.相手, T_売却.品番
FROM T_売却;

となります。必要なら年・月に条件をつけてもよいでしょう。

（２）T_購入テーブルからのデータ抽出
　T_売却・購入テーブルにT_購入テーブルのデータを抽出し追加します。ここでは、年・月, 相手, 品番がT_売却テーブルにないもののみ抽出しています。

INSERT INTO T_売却・購入 ( 年・月, 相手, 品番 )
SELECT T_購入.年・月, T_購入.相手, T_購入.品番
FROM T_売却 RIGHT JOIN T_購入 ON (T_売却.品番 = T_購入.品番) AND (T_売却.相手 = T_購入.相手) AND (T_売却.年・月 = T_購入.年・月)
WHERE (((T_売却.金額) Is Null));

（３）売却・購入金額の並列表示
　T_売却・購入テーブルを元に、売却金額と購入金額を並べて表示します。サンプルデータで試す限りは、ご所望の結果になるはずです。

SELECT T_売却・購入.年・月, T_売却・購入.相手, T_売却・購入.品番, T_売却.金額, T_購入.金額
FROM (T_売却・購入 LEFT JOIN T_売却 ON (T_売却・購入.品番 = T_売却.品番) AND (T_売却・購入.相手 = T_売却.相手) AND (T_売却・購入.年・月 = T_売却.年・月)) LEFT JOIN T_購入 ON (T_売却・購入.品番 = T_購入.品番) AND (T_売却・購入.相手 = T_購入.相手) AND (T_売却・購入.年・月 = T_購入.年・月)
ORDER BY T_売却・購入.年・月, T_売却・購入.相手, T_売却・購入.品番;

　このようにSQLビューで書いているのは、この回答欄からのコピー・ペーストの便を考えてのことであって、コピペした後は通常のデザインビューにして構いません。

以上

お礼

なるほど。参考になります。
今後もこのくらいのデータを扱うのであれば SQL も必須ですね。今までは少し頑張ればエクセルで処理できたので後回しにしていたのですが、今後は少し勉強したいと思います。

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

回答No.1

imogasi

＞詳しくご教授頂
OKWAVEには、文字数制約もあるので、説明できないでしょう。
この課題ぐらいになると、普通はシステム構築として、プロの業者に任せるものになるかと思いますが。
それをまる投げ的に、質問に出すのはね・・・。
○１００万件の処理はアクセスでもきついように思いますが。
最近はパソコンの処理能力やディスク容量も大きくなっているので
可能でしょうか。経験者の投稿を待ちましょう。
○実例があげてあるのですが
どの項目とどの項目をマッチングして、あとどうするのか質問に説明されていますか。
システムは文書化して、したいことその他を文書化するから始まります。
回答者に推測させるというのは考えが練れてないと思うのでのですが。
○１０００ぐらいの少数のサンプルデータを２つ（売却・購入）作り、質問を達成する、そのやり方は、アクセスの本でも読んで勉強し、もう少し細かいレベルの疑問点がでれば、それに絞って、質問してはどうですか。
＞Access は今まで使っておらず
リーレーションシップ、主キー、結合、参照整合性などのアクセスというかデータベースの概念に親しんでますか。でなければ、アクセスの本をまず読むか講習に出ることからはじめましょう。

お礼

全く参考になりませんでしたが、とにかく返答ありがとうございます。