アクセスエクセルのマクロについて

質問

社内システムから 毎日 夜間バッチで更新される
access のデータベースを、加工したいと思います 。

１、access上の過去10年分の無駄なデータを削除し
本年分だけを残す
２、新たに１列挿入し計算式を入れる
３、カテゴリ毎の合計を出す

以上の手順をルーティン化したいと考えています。
この場合、accessで全てを行うのと、access→Excelへ
データを落とし込みマクロを組むのとどちらが
スムーズでしょうか？

因みにaccessのみで作業するとデータが重いのか
エラーが出てしまいます。

回答No.5

chayamati · ベストアンサー

>大元のデータは全てそのまま保存しておきたいです。
そこから年単位で一部抽出したうえで、新たに項目を列として追加したかったのですが
------------------------------------------------------------------------------------------------------------
うっかり手を付けると思わぬトラブルが発生する恐れがあります。
「大元のデータ」ではなく、「大元のファイル」をコピペすると
「大元のファイル名&”のコピー”」のファイルが出来ます。
こちらでお試しなさるとよいでしょう
例えば、
消費税を仕組みに組み込むとき「金額」は「税込み金額」、「税抜き金額」、「消費税」、「税率」と増やします。
増やした項目は旧レコードにも適用されますが値は空白(Null値)です。
テーブルの数値項目に1つでもNull値のレコードがあると計算エラーとなります
(エクセルでは0と解釈しますね）
そのため数値項目の規定値を０と定義しておきます。

お礼

早速ありがとうございます！
Accessデータを壊してしまいそうで
冒険出来なかったのですが、コピーなら
安心ですね！思い付きませんでした…

更に具体的な項目の追加方法まで
ありがとうございます！

アクセス講座も受講し、何とかご回答の意図も
理解出来るようになりました。
わかりやすくご教示頂きありがとうございます！

回答No.4

chayamati

社内システムから 毎日 夜間バッチで更新される
access のデータベースを、加工したいと思います 。
----------------------------------------------------------------------
システムの仕組みが判明しないので何とも言えませんが
「夜間バッチ」の必要は無いと思われます。
※仕組みとは、テーブルの数、各テーブルのレコード数、
顧客マスタのような固定的のものと
売上テーブルのように日々追加されるもの、
リレーションシップがどのようなものか

１、access上の過去10年分の無駄なデータを削除し、本年分だけを残す
-----------------------------------------------------------------------
月報、年報のため、ある年数は残しておきましょう
レコード数が肥大化して、古いレコードを削除するときは日付が5年以上前のレコードを削除するには
VBAまたは抽出クエリで一括削除が可能です。

２、新たに１列挿入し計算式を入れる
-------------------------------------------------------------------------
エクセルでは、計算式ですがAccessのフィールドに計算式を入れることはできません

３、カテゴリ毎の合計を出す
--------------------------------------------------------------------------
レポート、集計クエリで可能です

以上の手順をルーティン化したいと考えています。
-----------------------------------------------------------------------------
古いレコードの年1回、月1回の削除をルーチンと言えますか
カテゴリ毎の合計は日報ですか、月報ですか、年報ですか

この場合、accessで全てを行うのと、access→Excelへ
データを落とし込みマクロを組むのとどちらが
スムーズでしょうか？
----------------------------------------------------------------------------
access→Excelはできても、ExcelのデータによりAccessのレコードを削除することはできません

お礼

もやっとした質問に筋道を
つけてくださってありがとうございます！

補足

ありがとうございます！
大元のデータは全てそのまま保存しておきたいです。
そこから年単位で一部抽出したうえで、新たに項目を列として追加したかったのですが…

回答No.3

tsubu-yuki

サンプルデータの開示・データ量・処理量、
クライアント端末の能力などなどがわからないと見積もりすらできません。

どちらにしても、全てアクセスのクエリで充分に対応可能だと思いますが・・
とりあえず、
１．クエリで本年分だけ取り出すことが可能です。
　　重いのはクエリの組み方の問題だろうと思います。
２．アクセスのテーブルに列を挿入するのは
　　無駄にデータを大きくするだけです。
　　もともとテーブルに保持している値を基に演算させるのであれば
　　通常はクエリ上で演算・表示します。
３．集計クエリを使います。
　　というか、アクセスの強みの一つです。

というわけで、アクセスで持っているならアクセスで完結するのが早いです。
ルーティンも何もクエリを組んで実行、
必要ならクエリを関連付けたレポートを用意しておいて印刷するだけ。
ここまでの作業ならマクロ・ＶＢＡは必要が無いです。

まぁ・・
具体的なサンプルもテストデータもここでは開示できないでしょうから、
回答もどうしてもあいまいになってしまいます。
１番さんもおっしゃる通り、
> 夜間バッチで更新される access のデータベース
とのことですから、社内にＳＥさんがいらっしゃるでしょう。
まずはそちらに相談なさってはいかがでしょうか。
というか、そのバッチ処理にそれら機能を追加してもらうくらいなら
造作無いと思うのですが、どうなのでしょう？
バッチで「accessのデータベース」を作ってくれるシステムなのだから
容易いことだと思いますよ。
良くてもＣＳＶファイル、というシステムが多いですからね。

お礼

もやっとした質問にご回答ありがとうございます！
seさんに依頼は大事になりそうで避けていたのですが
そんなに難しくないのですね。
アドバイス通り、アクセスだけで何とか出来るか
まずは試してみます！

回答No.2

imogasi

データの数のオーダーはいくらぐらいですか。参考までに書くべきでしょう。
10万、１００万、数百万レコードとか。
また１か月分に絞ると、データ数はいくらぐらいか。
ーー
エクセルに持ってくるのは簡単です。ＡＤＯを使って、CopyFromRecordset
でできるし、同時に前処理として、ＳＱＬ文でデータの日付を１か月に絞ることはできる。
その後エクセルのピボットテーブルでカテゴリ別の表を出すのは簡単。
他にアクセス内で、クロス集計クエリ（ＶＢＡでも）もあり、またピボットテーブルもあります。
１か月のレコードに絞る部分と集約表を作る部分を分けて（量的に処理時間的にどのようか把握して）考えては。
参考
http://excelwork.info/excel/adocopyfromrecordset/
https://okwave.jp/answer/new?qid=9386201　日付で絞る
http://accessvba.blog.so-net.ne.jp/2011-11-21
VBAといっていながら、質問は初心者らしく、（＞新たに１列挿入し計算式を入れるなどと、アクセスの話題で出すのはよくわかっているのかな）聞く意味がつかめない。
WEBでキーワードで照会すれば、コード例は出てくる程度の内容だと思う。
＞スムーズでしょうか
VBAでやれば、本件では、処理に人手は加わらないのだから、聞いてもしょうがない。
コンピュター処理時間を短くしたいなら、簡単ではなく、小生程度の力では、答えにくい。いまの質問者の状態では、言える技量ではないのでは。
本件アクセスVBAの中級者以上なら、違った質問になるだろう。
社内に、教えてもらう専門家はいないのかな。

お礼

ありがとうございました
参考にさせて頂きます

回答No.1

gkrsnama

>新たに１列挿入し計算式を入れる
というところがよくわかりません。アクセスのテーブルに計算式は入りません（文字列として入れることはできるが、当然に計算しない）

>データが重いのかエラーが出てしまいます。
400万レコードとか、あるフィールドにタグ付きテキスト1MB程度を入れて、コードを書いてデータを取り出すとか、そういうこともできますが？
エクセルの機能を直接に使わない限り、アクセスの方が安定していて能力が高いです。

一般的に言ってエクセルのマクロはアクセスと別物です。アクセスはデータベースという目的に向かって整理されているので、コードは簡単。エクセルはどんな使い方もできるので、コードは多様でややこしくなります。

お礼

ご回答ありがとうございます。
アクセス初心者で質問も手探りです…

テキストを入れてコードを取り出す？
そんなことも出来るのですか！
質問するレベルにすらないことを痛感したので、
せっかく頂いた回答を生かすためにも
明日アクセス講座に行くことにしました！

どうもありがとうございました！

補足

ご回答ありがとうございます。
アクセス初心者で質問も手探りです…

一列挿入し計算式を入れたかったのは、アクセスの価格表示が
税込だけなので新たに税抜表示を加える必要がありまして…
そしてデータ個別に別途名前の列も加えたくて。