
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ベストアンサー
- 回答日時:
簡単にやるには
[年・月]・[相手]・[品番]・[売却代金]・[購入代金]の各フィールドがあるテーブルを作りそのテーブルに売却テーブルのレコードを追加する。[年・月]・[相手]・[品番]はそのままフィールドへ[金額]は[売却代金]に追加します。
購入テーブルも同じテーブルに追加します。[年・月]・[相手]・[品番]はそのままフィールドへ[金額]は[購入代金]に追加します。
そのテーブルを元にクエリを作り相手と品番でグループ化し売却代金と購入代金を合計にします。クエリのフィールドに利益というフィールドを作り演算にして売却金額から購入金額の合計を引きます。
利益:売却金額-購入金額
で利益を求めることが出来ます。
クエリで処理するには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
- 回答日時:
Accessで820万件のテーブルを扱っていますが、特に問題ありません。
このテーブルの容量は1GB強なのですが、Accessのmdbファイルは2GBが上限のようですので、ご注意ください。ご質問の内容は古典的なマッチング処理で、VBAを使えばスマートにできると思いますが、ここでは、クエリーを用いる方法をご提案します。
前提として、ご質問の中で[売却]となっている部分をT_売却テーブル、[購入]となっている部分をT_購入テーブルとします。両テーブルのフィールド構成は、年・月、相手、品番、金額とします。そして新たに作業用のテーブルとして、T_売却・購入テーブルを作成します。このT_売却・購入テーブルのフィールド構成は、年・月、相手、品番とします。
(1)T_売却テーブルからのデータ抽出
空のT_売却・購入テーブルにT_売却テーブルのデータを抽出します。無条件で抽出する場合のクエリーは、SQLビューで書くと、
INSERT INTO T_売却・購入 ( 年・月, 相手, 品番 )
SELECT T_売却.年・月, T_売却.相手, T_売却.品番
FROM T_売却;
となります。必要なら年・月に条件をつけてもよいでしょう。
(2)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));
(3)売却・購入金額の並列表示
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
- 回答日時:
>詳しくご教授頂
OKWAVEには、文字数制約もあるので、説明できないでしょう。
この課題ぐらいになると、普通はシステム構築として、プロの業者に任せるものになるかと思いますが。
それをまる投げ的に、質問に出すのはね・・・。
○100万件の処理はアクセスでもきついように思いますが。
最近はパソコンの処理能力やディスク容量も大きくなっているので
可能でしょうか。経験者の投稿を待ちましょう。
○実例があげてあるのですが
どの項目とどの項目をマッチングして、あとどうするのか質問に説明されていますか。
システムは文書化して、したいことその他を文書化するから始まります。
回答者に推測させるというのは考えが練れてないと思うのでのですが。
○1000ぐらいの少数のサンプルデータを2つ(売却・購入)作り、質問を達成する、そのやり方は、アクセスの本でも読んで勉強し、もう少し細かいレベルの疑問点がでれば、それに絞って、質問してはどうですか。
>Access は今まで使っておらず
リーレーションシップ、主キー、結合、参照整合性などのアクセスというかデータベースの概念に親しんでますか。でなければ、アクセスの本をまず読むか講習に出ることからはじめましょう。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
このQ&Aを見た人はこんなQ&Aも見ています
-
プロが教えるわが家の防犯対策術!
ホームセキュリティのプロが、家庭の防犯対策を真剣に考える 2組のご夫婦へ実際の防犯対策術をご紹介!どうすれば家と家族を守れるのかを教えます!
-
アクセスのテーブルの差分の抽出について
Access(アクセス)
-
ACCESS 複数テーブル・複数フィールドを参照する不一致クエリ
Access(アクセス)
-
ACCESS テーブルを見比べて、一致したレコードを抽出したいです
Access(アクセス)
-
4
ACCESSで2つのテーブル比較で一致データのみ抽出したい
その他(データベース)
-
5
【access】複数のフィールドの不一致クエリ
Visual Basic(VBA)
-
6
2つのテーブルに共通するレコードを削除したい
Access(アクセス)
-
7
Accessで、1つの項目に複数の置換えを1度でするには?
Access(アクセス)
-
8
ACCESSで空白のデーターをクエリで判定/識別する方法を教えてくださ
Access(アクセス)
-
9
Accessのマクロでモジュールを実行させたい。
Access(アクセス)
-
10
アクセスで#エラーを表示させない方法は?
Access(アクセス)
-
11
選択クエリを基に不一致クエリを作成したけれど...
Access(アクセス)
-
12
エクセルからアクセスにインポートすると、エラーが出てしまいます。原因を教えていただけませんか。
Access(アクセス)
-
13
2つのテーブルを比較して一致した行を除外して表示するクエリ
その他(データベース)
-
14
アクセス VBAについて FROM句の構文エラー
Access(アクセス)
-
15
Access カレントレコードがありません
その他(データベース)
-
16
選択したチェックボックスのみチェックを入れたいのですが
その他(データベース)
-
17
インポート時のエラー「データ型の変換エラー」
Access(アクセス)
-
18
Access フォームのテキストボックスに半角英字のみで入力する設定は
Access(アクセス)
-
19
ACCESS側からEXCELの書式を設定するには?
Visual Basic(VBA)
-
20
VBAで文字を反映させると255文字の制限になってしまいます。
Visual Basic(VBA)
関連するカテゴリからQ&Aを探す
このQ&Aを見た人がよく見るQ&A
人気Q&Aランキング
-
4
皆様、御力をお貸し下さい。。...
-
5
Excel ユーザー定義で変換した...
-
6
[オートフィルター]機能について
-
7
エクセルの罫線が印刷できない
-
8
Excelのグレーの部分を戻したい
-
9
エクセル: セルの枠を超えて表示
-
10
エクセル近似曲線(範囲指定)
-
11
(Excel)あるセルに文字を入力...
-
12
エクセルのマクロについて教え...
-
13
あるセルに特定の文字列を打つ...
-
14
Excel 書式を関数で判断。
-
15
IFERROR(IF()IF())のよう...
-
16
Excel関数で、文字を数字に変換...
-
17
エクセル関数について教えてく...
-
18
至急 Excel日付表示設定教えて...
-
19
Excel 記入欄に網掛けして、文...
-
20
1から9まで表示するのに必要な...
おすすめ情報
公式facebook
公式twitter