- ベストアンサー
Accessを使って複数データをまとめたい方法とは？
- エクセルの２つのシート、A店の売上データ、B店の売上データを一つのシートにまとめ、商品ごとの売上金額をまとめたいです。
- また、商品コードの一覧表に紐づけたいのですが、どのようにすれば良いでしょうか？
- A店売上データ、B店売上データを組み合わせてテーブルにして、商品コードのデータと紐づけたいのですが、具体的な手順がわかりません。
- みんなの回答 （10）
- 専門家の回答
質問者が選んだベストアンサー
>商品コードのあるテーブルと紐づけたいと思いましたが 商品コードあるテーブルの商品コードが主キーですね このテーブルを商品マスタとします。 商品コードガ紐付けのキーでよろしいですね、 売上データの項目にも商品コードが必要です また店名もExelの項目として追加します EXCELよりインポート時に集計しようとなさっていますが、 複雑なVBAの知識を必要とします。 今回は、集計はレポートで作成できます また、Excelには品名の代わりに商品コードにし、 品名は商品マスタ参照がより良いでしょう A店売上データの末尾にB店売上データを追記(コピペ)して ACCESSに取り込みます(このテーブルを売上履歴とします この時主キーの作成をハイにするとIDが主キーになります受け入れて下さい。 商品マスタと売上履歴を商品コードで紐付(リレーションシップ) 【データベースツール】⇒【リレーションシップ】⇒【テーブルを表示】 ⇒【商品マスター】⇒【追加⇒【売上履歴】⇒【追加】⇒【閉じる】 ⇒【商品マスタの商品コードから売上履歴の商品コードへドラッグ】 ⇒【参照整合性にチェック】 ⇒【作成】 ⇒【リレーション画面を閉じる】 ⇒【はい】
その他の回答 (9)
- chayamati
- ベストアンサー率41% (260/624)
VBAじゃないと集計が難しいとのことでしたが 追加クエリでA店の売上データとB店の売上データをまとめて A店の売上データのあるテーブルにB店を売上データを入れそのテーブルを クロス集計クエリで集計するのはありでしょうか？ クロス集計したクエリを新規テーブルに移して商品マスタと紐づけるのは可能でしょうか ----------------------------------------------------------------------------------- クロス集計 試してみましたが良くありません 1.行に商品コードにすると列(項目名）に店名を並べることになる ACCESSの項目名に店名は？？？ 2.集計項目は1項目のみなり、数量用と金額用の2つのクエリーが必要 3.Excelでは空白セルは０として集計されるが ACCESSでは集計項目に一つでも空白があれば＃NAME?と表示され、集計不可 先を考えると商品マスタ、売上履歴、売上集計の３つのテーブルを提案します。 ・商品マスタ (品名コード、品名） 将来 規格、標準単価、等項目の追加 ・売上履歴(ID,店名、品名コード、品名、数量、金額） IDを主キーにし、データ型をオートナンバー型とする 品名はリレーションにより商品マスタを参照できるので不要 将来 日付 等追加 .売上合計(品名コード、数量、金額 将来 ID,売上月 等追加 ルーチンワークならシステムとして作成しましょう VBAが必要です。 以下はEXCELのデータをテーブルへインポートするVBAと 売上履歴を売上合計へ集計するVBAです。 如何ですか、独学では入りにくいですが、サポートがあれば可能でしょう １０数回のやり取りが必要ですが、よろしければお付き合いします。 Private Sub 商品マスタインポート_Click() DoCmd.TransferSpreadsheet acImport, 8, "商品マスタ", "D:\商品マスタ.xlsx", True, "" End Sub Private Sub 売上履歴インポート_Click() DoCmd.RunSQL "delete from 売上履歴" '売上履歴の初期化 DoCmd.TransferSpreadsheet acImport, 8, "売上履歴", "D:\売上履歴.xlsx", True, "" End Sub Private Sub 売上集計_Click() DoCmd.RunSQL "delete from 売上合計" '売上集計の初期化 DoCmd.RunSQL "insert into 売上合計(品名コード,数量,金額) select 品名コード,sum(数量),sum(金額) from 売上履歴 group by 品名コード" End Sub
- superdbtool
- ベストアンサー率0% (0/5)
1．A店、B店....データをAccessへインポート テーブル TBL： NAME, CNT, PRICE テーブル ITEMS: ITEM_CODE, NAME 2.以下のSQLを実行 SELECT NAME, SUM(CNT), SUM(PRICE) FROM TBL GROUP BY NAME ; SELECT B.ITEM_CODE, A.NAME, A.CNT, A.PRICE FROM ( SELECT NAME, SUM(CNT), SUM(PRICE) FROM TBL GROUP BY NAME ) A, ITEMS B WHERE A.NAME = B.NAME
- tamu1129
- ベストアンサー率58% (1319/2258)
あと気になる点があるのですが、データの蓄積が必要ないと言ってました 上司がアクセスでやれと言ったからとも言ってますが、アクセスはデータベースソフト エクセルはカルク（表計算ソフト）です アクセスにやらせるべき事は、売り上げデータの情報を蓄積させていって、蓄積させたデータの中から必要な時に過去のデータからデータ抽出をして利用する事です 今回のような事は、エクセルで出来る内容なのです だから回答者が、エクセルのピボットテーブルでやれば良いでしょうって回答が付くのです エクセルのワークシート内で、他のワークシートにある商品コードの参照させるなら、lookup関数使ったりすれば事足ります 一時的な集計作業が目的ならば、エクセルにさせるのが適任なのです 野球選手とサッカー選手がいたとします どちらもスポーツ選手なのでどちらのスポーツさせても一応はこなします しかし、やらせるなら野球は野球選手にサッカーはサッカー選手にまかせるべきなのです 今回私が最初に、データの蓄積をしていくのですよね？との質問にNOだと答えました ならば、この作業はアクセスで行う作業ではありません このあたりをごっちゃにしている人だと、最初のデータが適切になっていないのではないかと思ってしまいます 先の回答で、アクセスで商品コードテーブルとリレーションさせて商品コードを表示させるクエリを集計クエリに変更させる事で希望されている事は出来ると話をしました 懸念している点は商品コードと商品名の関係が１対１になっていないのではないかという点です 商品名が えんぴつ になっている物に商品コードが Ｅ００１やＥ００２など複数のコード与えてしまっていませんか？ Ｅ００１はえんぴつ１ Ｅ００２はえんぴつ2 と別な商品名のような管理になっていれば問題ありません 今エクセルの1枚のワークシートの行数制限 1,048,576 行もあるのですよ Ａ店・Ｂ店のデータがそこまでのデータありますか？ 商品もそこまでの品数ないでしょう 単に集計したいだけならエクセルで1枚目商品コード用ワークシート・2枚目A店データ・3枚目B店データ・4枚目ピボットテーブル用ワークシートのようなbook構成で十分出来た事なんじゃないですか？
- tamu1129
- ベストアンサー率58% (1319/2258)
今は、A店・B店２つの売り上げ用のテーブルが作成されていますが、エクセルからのインポートを適正に行っていれば、２つのデータが合算されたテーブルが１つ出来ていればいいだけなのです なので、最初の部分の運用がなにかおかしいと思います まぁ別個に作成してしまっているので、片方のデータを追加クエリ使って１つにまとめれば良いでしょう よくわからなければ元になったエクセルのシートがありますよね？ エクセルのワークシート上で、A店とB店を1枚のワークシートになるようにして、インポートでデータ取り込んで新規のテーブルでデータ保存してください このテーブルを、選択クエリ使って商品コードテーブルとリレーションさせて商品コード表示できる物作れば、商品コードが表示されるA店・B店両店の売り上げが表示されます この両店の商品コードが表示されるクエリ（クエリ３つめ）をちょっと手を加えてください クエリのデザインで、フィールド部分でマウス右クリックするかツールバーから集計をクリックするとフィールド部分に集計という部分が追加されます 集計部分が グループ化 となっているはずなので、集計を出したい 数量の部分と金額の部分をグループ化ではなく合計に変更します（グループ化部分をクリックすれば三角マークが出てメニューが伸びます） この状態にしたクエリを開いてください 希望されている事が出来ているでしょう データ蓄積が必要ないならば、集計した後に売り上げテーブルの中のデータ全て削除すれば良いです（テーブル自体を削除してはダメですよ 中のデータのみです） 翌月には、売り上げテーブルにエクセルからデータをインポートする時に新規のテーブル作成するのではなく、既存の売り上げテーブルにインポートすれば良いだけです 作成してある、３つめのクエリ開けばひとつにまとめたデータが表示されます 印刷する必要があるなら、この３つ目のクエリを利用したレポートを作成して印刷すれば良いです 様式にこだわらないなら、クエリ開いた状態で印刷してやれば一覧表のように印刷は行われます
- tamu1129
- ベストアンサー率58% (1319/2258)
＞シートはそれぞれ店舗ごとの売上データと ＞商品にもとずいた商品コードの表の3シートあり ＞3シートのテーブルが、アクセスにあります。 商品にもとずいた商品コードの表から作成したテーブルのデータ参照する事になるので、このテーブルは必要です 通常この業務を考える時に、まずエクセルのデータをアクセスに取り込みます imogasiさんもコメントしてくれていますが、アクセスはエクセルのワークシートのデータを取り込む事ができます（これが外部データの取り込みからインポートです） これをする事でエクセルのデータを新規のテーブルとして保存したり、既存のテーブルにデータの追加ができる訳です まずこの作業を行って欲しかったのですが、既にA店のデータがAテーブルに格納されていて、BテーブルにはB店のデータが保存している状態だという訳ですね？ そして商品コードと商品名が記録されているもう一つのテーブルが有ると この状態でも良いんですが、今後はどうするつもりでした？ エクセルのデータどうやってアクセスに入れたんですか？ エクセルの表を見て手で入力し直してました？ 集計するのにVBAなんて必要ありませんよ クエリだけでできます まず、売り上げデータのテーブルと商品コードのテーブルリレーションさせて商品コードを表示する方法です デザインビューで選択クエリ作成してください ウィザードが立ち上がるので A店の売り上げデータが入っているテーブルと、商品コードのテーブル２つを追加します どちらのテーブルにも商品名という共通の部分があるので、片方の商品名の部分をクリックアンドドラッグしてもう片方の商品名の上で離します すると２つの商品名は線で結ばれます こうしてリレーションシップさせれば既存のテーブルに有るデータと他のテーブルにあるデータとを参照してクエリで表示させる事ができます 同じ事をB店のデータが入っているテーブルと商品コードテーブルで選択クエリを作成してやってみてください これでクエリが２つできた訳ですが、片方が商品コードが表示されるA店のデータを表示させるもの、もう片方がB店の商品コードが表示されたデータを表示させる物になります ここまでOKですか？
- imogasi
- ベストアンサー率27% (4737/17070)
（１） 詳しく検討してませんが、エクセルの「ピボットテーブル」の本を買って、丁寧に読んで、質問のことができないかを念頭に置いて、勉強することを勧めます。ピボットだけに絞った解説書が数種類大型書店では見つかるはず。 本質問にフィットしなくても、今後非常に役立つ。 （２）Accessにデータを移して、加工したいなら、手始めにA店、B店のデータをAccessにインポートしてみてください。 Ａ店のデータの最終行の次行から（Ａｐｐｅｎｄ的に）Ｂ店のデータ明細がＡｃｃｅｓｓに移る（追加される）ようにやってみること。 対象データを１テーブルに集めたほうが、後がやりやすいでしょう。 店別データを別テーブルにしてクエリなどで、２（複数）テーブルを扱って１つの結果を出す方法もありますが。 ー それにはフィールド名がエクセルのシート表の先頭行に来ること（データとしての先頭行をシートの指定の都度指定できるかもしれない） フィールド名はＡ店とＢ店で同じ順序や同じ文字列で、フィールド項目の有無もおなじとする。（この点は心配いらないだろうが） ーー またエクセルのシートには、見ればわかる、当然だとして１か所にしか注記がない年月なども全レコード（各行）に入れることが必要でしょう。見出しに「２５年４月店別売上表」とあって各行（アクセスではレコードとなる）には、「２４年４月」という記述は入れてない場合（＝当たり前だが）、年月フィールドを補っておく。 初心者は ＜Ａ店＞たとえば５行目に入れて、以下はＢ店のデータです、 データ行（６行目以下） ＜Ｂ店＞たとえば４０行目にいれて、以下はＢ店のデータです、 データ行（４１行目以下） なんて作り方をする者もいる。パソコンでデータを扱う以前はこちらが当たり前といえよう。 この場合は店番も明細各行データに１フィールドとして付け加える。見出し行（＜Ａ店＞など）はシート上から省く。 ーー 「商品別の合計」や「商品コードを他のマスターからとってくる」のはアクセスの初歩解説書に練習課題として載っていること。 ーー 店別データを１０行ぐらいに実例データを絞り、（多分アクセス圏内で）やってみて、ここがうまく行かないといった具体的な質問をすること。
- tamu1129
- ベストアンサー率58% (1319/2258)
では、エクセルで作成したデータをアクセスにインポートさせてください それが正常に行える事、その時にできるデータのフィールドの名前（業務で使われるのでしょうから、フィールド名を記載できないようなら、できたフィールドの数だけ教えてください） 既に商品コード用のテーブルは作成済みで、この中にデータは入力済みって事ですよね？
お礼
シートはそれぞれ店舗ごとの売上データと 商品にもとずいた商品コードの表の3シートあり 3シートのテーブルが、アクセスにあります。
補足
何度もすみませんが シートの内容は(1)(2)の内容でお願いします。 作業手順のみ、簡易的に教えてほしいので 例えば テーブル作成→クエリ→この時のクエリは追加クエリ→、、、 でできますよ。 追加クエリは、表示→、、、で作成して下さい 的な表現だと大変助かります。
- tamu1129
- ベストアンサー率58% (1319/2258)
データは今後も蓄積されて使用してゆきたいって事ですよね？ エクセルのシートからの取り込み用のテーブルと、データ蓄積用のテーブルと２つ使用した運用が良いのではないかと思います エクセルからのデータの取り込みは出来るって前提で話をします まず、エクセルのデータを一旦取り込み用のテーブルに入れます フィールドは、主キーになるフィールド・商品名・購入数・金額・（エクセルで持っていないなら売り上げ店舗・売上日のフィールドも作成） この時にエクセルの方で、どの店舗での売り上げなのか、いつの売り上げなのかデータが無いなら、アクセスに取り込んだ後に更新クエリで一括更新するなどしてデータを整えます 取り込んで良いデータなのかどうかも、このテーブル確認すれば良いので一旦テンポラリ的なデータとして利用出来る状態にしたほうが良いでしょう このデータを確認してOKならば、データ蓄積用のテーブルに追加クエリ使って主キーにしているフィールド以外のデータをコピーさせて、正常にコピー出来たならばエクセルからデータ取りこみ用としているテーブルのデータを空にしてやれば良いです（データ蓄積用のテーブルのフィールドはエクセルから取りこみ用のテーブルとまったく同じ構成） データ蓄積用のテーブルには過去のデータが全て蓄積される事になるので、このテーブルのデータを利用すれば、トータルの売り上げデータ見る事が出来ます データ取りこみ用のテーブルでどの店のデータなのか、売上日なども入力してある状態にしてやれば、全店分だろうがどちらかの店舗分だろうが自由に参照する事が出来るようになります（月間売り上げとか日付に関する抜き出しも） リレーションは、データ蓄積用のテーブルの商品名と商品コード用テーブルの商品コードをリレーションさせるようにすれば実現します 希望されてるデータを表示させる時は、 商品コードテーブルの商品コード・蓄積テーブルの商品名・蓄積テーブルの購入数フィールドを合計にしたもの・売り上げ金額を合計にしたもの が表示される、集計クエリでデータ抽出すればいいです
補足
ありがとうございます。特に蓄積する気はありません。 初心者なので質問にある1と2の作業手順を教えてくれると大変大変助かります。 参考書を元にしてみましたが思うような操作を探せなかったため質問しました。 最終は2にあるような表を毎回月一で完成させたいのです。 データを蓄積することは作業上、不要です。
お礼
ありがとうございます。 この回答を待ってました。(笑) ちなみにですが VBAじゃないと集計が難しいとのことでしたが 追加クエリでA店の売上データとB店の売上データをまとめて A店の売上データのあるテーブルにB店を売上データを入れそのテーブルを クロス集計クエリで集計するのはありでしょうか？ クロス集計したクエリを新規テーブルに移して商品マスタと紐づけるのは可能でしょうか？