Access (一般機能)

Accessの一般機能に関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(Windows 10全般 : Access 2016)
DBの構成について知りたいことがあります
投稿日時: 21/03/23 10:39:37
投稿者: だいふくもち

いつもこちらのサイトにはお世話になっております。
Excelで管理していた売掛データをDBに移行するために
Accessを学び始めたばかりの者です。
不慣れな点がありましたら申し訳ありません…
 
ご経験者の皆様に、データベース構成の方向性が合っているか
ご意見をいただけたらと思い、ご相談させていただきました。
 
最終的な目標は、毎月の見積書の出力と、入金確認に利用できる
データベースを作成することです。
 
*テーブル_契約マスタ
【フィールド】契約番号 | 契約内容 | 契約開始日 | 契約終了日 | 顧客コード |
【レコード】 1001001 | 〇〇〇案件 | 2021/1/1 | 2021/5/1 |001
【レコード】 1001002 | △△△案件 | 2021/2/1 | 2021/10/1 |001
【レコード】 1001003 | ●●●案件 | 2021/5/1 | 2022/1/1 |002
 
*テーブル_顧客マスタ
【フィールド】顧客コード | 客先名 | 住所 |
【レコード】001 | aaa | aa県aa市
【レコード】002 | bbb | bb県bb市
【レコード】003 | ccc | cc県cc市
 
*テーブル_月額マスタ
【フィールド】 契約番号 | 2021年1月 | 入金済 | 2021年2月 | 入金済 | ...
【レコード】 1001001 | \3,000 | □ | \3,000 | □ | \3,000
【レコード】 1001002 | ------ | □ | \5,000 | □ | \5,000
【レコード】 1001003 | ------ | □ | ------ | □ | ------
 
という構成を当初は考えておりました。しかし、ネットで調べていると、
「フィールドがだんだん増えていくシステムにすべきでない」
という意見を多数見かけましたので、以下のように見直しました。
 
*テーブル_月額マスタ
【フィールド】契約番号 | 月 | 金額 | 入金済 |
【レコード】 1001001 | 2021/1/1 | \3,000 | □ |
【レコード】 1001001 | 2021/2/1 | \3,000 | □ |
【レコード】 1001001 | 2021/3/1 | \3,000 | □ |
【レコード】 1001001 | 2021/4/1 | \3,000 | □ |
【レコード】 1001001 | 2021/5/1 | \3,000 | □ |
【レコード】 1001002 | 2021/2/1 | \5,000 | □ |
【レコード】 1001002 | 2021/3/1 | \5,000 | □ |
【レコード】 1001002 | 2021/4/1 | \5,000 | □ |
【レコード】 1001002 | 2021/5/1 | \5,000 | □ |
.
.
.
 
しかし、以上の構成で作成した場合、
今年度全体の売掛金を把握したい際に、ちょっと見づらいかなと思いました。
(2020年4月〜2021年3月の範囲のみを金額マスタから抽出し、
月別のフィールドで金額を見れるようなクエリを作れたらいいのですが…)
 
皆様だったらどのような構成をされるのか、是非知りたく存じます。
どのようなご意見でも構いませんので、教えていただけますと大変有難いです。

回答
投稿日時: 21/03/23 10:49:05
投稿者: sk

引用:
2020年4月〜2021年3月の範囲のみを金額マスタから抽出し、
月別のフィールドで金額を見れるようなクエリを作れたらいいのですが…

クロス集計クエリを使用なさればよろしいのではないかと。

投稿日時: 21/03/23 11:53:28
投稿者: だいふくもち

>sk様
さっそくご回答いただきありがとうございます!
その機能を知りませんでしたので、革命的でした…!
試してみたことろ、こちらの機能で問題なく集計できそうです。
 
sk様さえよければ教えていただきたいのですが、
Accessをはじめて学ばれる際、
どのようにされていらっしゃいましたか?
 
今、私が取り組んでいる方法が、
ネットで公開されている無料のAccess入門講座に一通り目を通し、
「こういう流れのDBを作りたい」というワークフローを設定して
その構築に必要な操作を洗い出し、簡単そうなところから
ネットで調べつつ取り組んでいっているのですが、
基本以上の操作(複雑な機能やVBA等)が必要だとつまづくので
初心者〜中級者向けのテキストで一度体系的に学習し、
存在する機能を把握すべきなのかな…と悩んでおります。
(ExcelでVBAをよく使用しておりましたので、
得意な方ではないですが抵抗はない方です)
 
もし差し支えなければ、教えていただけますと幸いです。

回答
投稿日時: 21/03/23 14:21:12
投稿者: sk

引用:
試してみたことろ、こちらの機能で問題なく集計できそうです。

実際にどのような操作によって、どのようなクロス集計クエリを
作成されたかは分かりませんが、クロス集計クエリを使用される際は
以下の点に留意されることをお奨めします。
 
・Access のクエリにおいて出力可能なフィールドの数が 255 個まで
 という制限がある以上、クエリ列見出しを無限に増やせるわけではない。
 
・クエリ列見出しが可変である(列見出しとして出力される
 フィールドの名前や数がその都度変わる)クロス集計クエリは、
 連結フォーム/連結レポートのレコードソースや
 コンボボックス/リストボックスの値集合ソースとして
 利用するには相性が悪すぎる。
 (出来なくはないが、VBA による制御が必須となり、非常に面倒である)
 
引用:
今年度全体の売掛金

引用:
2020年4月〜2021年3月の範囲のみを金額マスタから抽出

年度単位で集計するのであれば、例えば次のような形で
クエリ列見出しを固定してみるのも良いでしょう。
 
( SQL ビュー)
--------------------------------------------------------------------
TRANSFORM Sum([月額マスタ].[金額]) AS [金額の合計]
SELECT [契約マスタ].[契約番号],
       [契約マスタ].[契約内容],
       [契約マスタ].[顧客コード],
       [顧客マスタ].[客先名],
       Year(DateAdd("m",-3,[月額マスタ].[月])) AS [年度]
FROM ([契約マスタ]
      LEFT JOIN [顧客マスタ]
      ON [契約マスタ].[顧客コード] = [顧客マスタ].[顧客コード])
LEFT JOIN [月額マスタ]
ON [契約マスタ].[契約番号] = [月額マスタ].[契約番号]
WHERE [月額マスタ].[入金済] = False
GROUP BY [契約マスタ].[契約番号],
         [契約マスタ].[契約内容],
         [契約マスタ].[顧客コード],
         [顧客マスタ].[客先名],
         Year(DateAdd("m",-3,[月額マスタ].[月]))
ORDER BY [契約マスタ].[契約番号],
         Year(DateAdd("m",-3,[月額マスタ].[月]))
PIVOT Month([月額マスタ].[月]) & "月"
   IN ("4月","5月","6月","7月","8月","9月","10月","11月","12月","1月","2月","3月");
--------------------------------------------------------------------
 
引用:
sk様さえよければ教えていただきたいのですが、
Accessをはじめて学ばれる際
どのようにされていらっしゃいましたか?

初めての頃の話をしても、恐らく全く参考にはならないと思います。

投稿日時: 21/03/23 16:05:01
投稿者: だいふくもち

>sk様
ご丁寧にご説明いただき本当にありがとうございます…!
 
私がやってみたのは、手動でですが、
まず必要な期間のみを抽出した[月額マスタ]のクエリを作成し、
「クエリウィザード」から「クロス集計クエリウィザード」を選択、
行を「契約番号」に設定し、列を「月」に設定してみたのですが、
sk様が当初想定されていたやり方に沿っておりますでしょうか…?
 
SQL文は全くのノータッチでしたので、せっかく教えていただいた
構文を理解するのに時間が掛かりそうです。
今すぐに試せずに申し訳ありません…
ネットで調べてなんとなく理解できましたが、
まだ何となくの域ですので、もっと理解してから使ってみようと思います。
 

引用:
Year(DateAdd("m",-3,[月額マスタ].[月])) AS [年度]

ここで3カ月分引かれているのは何故でしょうか?
 
引用:
初めての頃の話をしても、恐らく全く参考にはならないと思います。

差し出がましい質問をして申し訳ありませんでした…
恐らくとてつもない努力をされたのだろうと感じました。
まだ足元にも及びませんが、やり方を模索しながら勉強してみようと思います。

回答
投稿日時: 21/03/23 17:10:15
投稿者: sk

引用:
まず必要な期間のみを抽出した[月額マスタ]のクエリを作成し、
「クエリウィザード」から「クロス集計クエリウィザード」を選択、
行を「契約番号」に設定し、列を「月」に設定

その場合は、次のようなクロス集計クエリが
ウィザードによって作成されるはず。
 
( SQL ビュー)
-----------------------------------------------------------
TRANSFORM Sum(クエリ名.[金額]) AS 金額の合計
SELECT クエリ名.[契約番号], Sum(クエリ名.[金額]) AS [合計 金額]
FROM クエリ名
GROUP BY クエリ名.[契約番号]
PIVOT Format([月],"oooo") In ("1月","2月","3月","4月","5月","6月","7月","8月","9月","10月","11月","12月");
-----------------------------------------------------------
 
この時、抽出されている[月額マスタ]のレコードの[月]の範囲が
特定の年(の 1 〜 12 月 の 12 ヶ月以内)に限られているのであれば
問題ないでしょう。
(列見出しも固定されていますし)
 
しかし、もし指定された[月]の範囲が 13 ヶ月以上である
(複数年にまたがっている)場合、上記のクロス集計クエリでは
年(あるいは年度)ごとの集計が行なわれていないため、
例えばクロス集計クエリの実行結果において[1月]の値が
「 2020 年 1 月と 2021 年 1 月の[金額]の合計」になる、
といったことが起こりえます。
 
引用:
ここで3カ月分引かれているのは何故でしょうか?

年単位(当年 1 月から当年 12 月まで)ではなく
会計年度単位(当年 4 月から翌年 3 月まで)で区切って
グループ化し、行見出しとするためです。
 
引用:
年度全体の売掛金を把握したい際

 
引用:
差し出がましい質問をして申し訳ありませんでした…

お気になさらず。

投稿日時: 21/03/24 17:36:00
投稿者: だいふくもち

お返事が遅くなってしまい申し訳ありません…
TRANSFORMステートメントとSELECT文の理解に、だいぶ苦戦しておりました。
 
sk様のご指摘の通り、たしかに年度が複数またがると
年度がちがうのに全ての月が集計されてしまう…
という事態に気付き青ざめました…
 

引用:
年単位(当年 1 月から当年 12 月まで)ではなく
会計年度単位(当年 4 月から翌年 3 月まで)で区切って
グループ化し、行見出しとするためです。

最初は、TRANSFORMステートメントの構文がよく分からなかったので、
なぜ3カ月前の月の話が急に出てくるんだ??という感じでしたが、
[月]列を年度別に分けるためだとやっと合点がいきました。
 
拙い内容および質問にも関わらず、順を追って
丁寧にご説明いただき本当にありがとうございました。
おかげさまで、最善策を知ることができ、大変な学びになりました。
もっと勉強して少しでも詳しくなりたいと思います!