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.1
- 回答日時:
>詳しくご教授頂
OKWAVEには、文字数制約もあるので、説明できないでしょう。
この課題ぐらいになると、普通はシステム構築として、プロの業者に任せるものになるかと思いますが。
それをまる投げ的に、質問に出すのはね・・・。
○100万件の処理はアクセスでもきついように思いますが。
最近はパソコンの処理能力やディスク容量も大きくなっているので
可能でしょうか。経験者の投稿を待ちましょう。
○実例があげてあるのですが
どの項目とどの項目をマッチングして、あとどうするのか質問に説明されていますか。
システムは文書化して、したいことその他を文書化するから始まります。
回答者に推測させるというのは考えが練れてないと思うのでのですが。
○1000ぐらいの少数のサンプルデータを2つ(売却・購入)作り、質問を達成する、そのやり方は、アクセスの本でも読んで勉強し、もう少し細かいレベルの疑問点がでれば、それに絞って、質問してはどうですか。
>Access は今まで使っておらず
リーレーションシップ、主キー、結合、参照整合性などのアクセスというかデータベースの概念に親しんでますか。でなければ、アクセスの本をまず読むか講習に出ることからはじめましょう。
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 も必須ですね。今までは少し頑張ればエクセルで処理できたので後回しにしていたのですが、今後は少し勉強したいと思います。
回答ありがとうございました。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- 簿記検定・漢字検定・秘書検定 簿記3級 仕訳の問題です。 2 2022/09/23 22:03
- その他(データベース) pythonでsqlight勉強中、クエリー結果の利用法教えて下さい 1 2022/04/28 20:38
- 高校 日商簿記3級の勉強中なのですが 精算表が完成せず困っています。 こちらの問題の回答を教えていただきた 2 2023/03/02 09:07
- 財務・会計・経理 事業復活支援金の事前確認について 2 2022/04/11 16:07
- その他(悩み相談・人生相談) 少しややこしいお話しですが、皆様の意見を聞かせてください。 あるサイトで商品を出品していました。購入 2 2023/05/30 09:34
- 金銭トラブル・債権回収 購入手続き後の値上げ 4 2022/06/29 18:03
- その他(社会・学校・職場) 少しややこしいお話しですが、皆様の意見を聞かせてください。 あるサイトで商品を出品していました。購入 3 2023/05/23 13:40
- 財務・会計・経理 【仕訳】集計されて引かれる販売手数料について 5 2023/04/26 15:32
- SoftBank(ソフトバンク) 3ヶ月前にドコモからソフトバンクに変更しました。昨日、ソフトバンクからのメッセージが来ました。「残り 5 2022/06/04 07:18
- 財務・会計・経理 仕訳の仕方を教えていただけませんでしょうか 2 2023/01/22 16:17
このQ&Aを見た人はこんなQ&Aも見ています
-
新NISA制度は今までと何が変わる?非課税枠の拡大や投資対象の変更などを解説!
少額から投資を行う人のための非課税制度であるNISAが、2024年に改正される。おすすめの銘柄や投資額の目安について教えてもらった。
-
アクセスで数値型のフィールドにNullをいれたい
その他(データベース)
-
ACCESSで2つのテーブル比較で一致データのみ抽出したい
その他(データベース)
-
アクセスのテーブルの差分の抽出について
Access(アクセス)
-
-
4
アクセス VBAについて FROM句の構文エラー
Access(アクセス)
-
5
ACCESS 複数テーブル・複数フィールドを参照する不一致クエリ
Access(アクセス)
-
6
Accessでデータシートに同じデータがいくつもでてしまいます。
その他(データベース)
-
7
AccessVBAで他テーブルのデータを取得する方法について。
Visual Basic(VBA)
-
8
Accessでフィールドを比較したいのですが
その他(データベース)
-
9
テーブルでメモ型になっているのにクエリで結合ができません。
その他(データベース)
-
10
「#エラー」の回避
Access(アクセス)
-
11
ACCESSテーブル2つのデータの比較について
Visual Basic(VBA)
-
12
ACCESS テーブルを見比べて、一致したレコードを抽出したいです
Access(アクセス)
-
13
2つのテーブルを比較して一致した行を除外して表示するクエリ
その他(データベース)
関連するカテゴリからQ&Aを探す
おすすめ情報
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
新NISAのつもりが一般枠で...
-
Access を利用した大量データに...
-
三井住友信託銀行の投資信託
-
SBI証券→住信SBIネット銀行へお...
-
円奏会の投資ですが、一度も プ...
-
親が子供のために株を買う
-
信用金庫の出資証券ってなんで...
-
高齢者が口座の開設を拒否され...
-
過去の(一年分とか、、)5分足...
-
株式について
-
証券会社の倒産の際、顧客の資...
-
気配値を見れるサイト
-
先月開設したばかりの証券口座...
-
新生・フラトンVPICファンド 解...
-
投資信託の本人以外の解約は不...
-
NISAの使用方法
-
楽天証券 外貨預り金
-
オープン基準価格?オープン投信?
-
証券会社のIDやパスワードを知...
-
配当金はどこに振り込まれるの...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
Access を利用した大量データに...
-
新NISAのつもりが一般枠で...
-
ウェルスナビの積み立てNISAに...
-
同日中に同一銘柄を売買した場...
-
有価証券売却益は経常利益に計上?
-
まめ株の売り操作ができません
-
1000万円使えるならどうしますか?
-
SBI証券→住信SBIネット銀行へお...
-
証券会社の倒産の際、顧客の資...
-
円奏会の投資ですが、一度も プ...
-
SBIハイブリッド預金って欄。 ...
-
ベトナム株ETFの「ヴァンエック...
-
楽天証券からの郵送を停止したい
-
証券会社の社員が株式取引する場合
-
定期預金する場合の口数について
-
高齢者が口座の開設を拒否され...
-
信用金庫の出資証券ってなんで...
-
過去の(一年分とか、、)5分足...
-
証券会社のIDやパスワードを知...
-
証券自己
おすすめ情報