すべてのカテゴリ

【例題】選択クエリの作成以下仕様に基づき、商品ごとの[理論在庫数](注)が[最小在庫数]を下回る商品を抽出のうえ、必要な[発注数量]・[仕入金額]を算出する選択クエリを作成しなさい。(1) クエリ名は『仕入先別発注対象商品』とすること。(2) 表示するフィールドは下記とすること。① [仕入先ＩＤ]② [仕入先名]③ [電話番号]④ [ＦＡＸ番号]⑤ [商品ＩＤ]⑥ [商品名]⑦ [仕入単価（税抜）]⑧ [最大在庫数]⑨ [最小在庫数]⑩ [理論在庫数]⑪ [発注数量]⑫ [仕入金額](3) [理論在庫数]のフィールドについては、下記の値を算出し設定すること。●下記（ア）から（イ）を減算した値（ア）「商品ＩＤ」ごとの「入出庫トラン」テーブル「入庫数」の合計値（イ）「商品ＩＤ」ごとの「入出庫トラン」テーブル「出庫数」の合計値(4) [発注数量]のフィールドについては、下記の値を算出し設定すること。●[最大在庫数]から、上記(3) [理論在庫数]を減算した値(5) [仕入金額]のフィールドについては、下記の値を算出し設定すること。●[仕入単価（税抜）]に、上記(4) [発注数量]を乗算した値(6) 抽出条件として、下記を設定すること。●上記(3)[理論在庫数]が、[最小在庫数]を下回るレコードのみ抽出(7) [仕入先ＩＤ]の昇順で、並び替えを設定すること。(8) クエリ内の各テーブル間に適切な結合および結合プロパティが設定されていること。学習メモ1-9.でひと通り作成「例題⑪：発注数量」演算で失敗【×】発注数量:Nz(Sum([最大在庫数]),0)　　　-(Nz(Sum([入庫数])

テーブル上の特定レコードの全フィールドを表示させる案SELECT テーブル名.*FROM テーブル名WHERE テーブル名.[フィールド名]=抽出条件;☆学習メモ7-1で用いた、データベースの中身を全確認したいときの標準的なデータ取得SQLSELECT テーブル名.*FROM テーブル名;☆学習メモ7-3で用いた、選択済みフィールド情報(列)の中から特定のレコード(行)抽出SQLWHERE テーブル名.[フィールド名]=抽出条件;ふたつの☆の合わせ技で、タイトルに「メカ」とついたレコードを表示させようとした。クエリデザイン画面を使う場合の操作手順：選択クエリ画面にテーブルを置き、*（アスタリスク）をダブルクリックする。あるいは*（アスタリスク）をフィールドグリッドにドラッグする。　↓テーブル内「タイトル」フィールドをダブルクリックする。あるいはテーブル内「タイトル」フィールドをフィールドグリッドにドラッグする。　↓「タイトル」フィールドグリッドの「抽出条件」に「メカ」と直打ち。（Accessは賢いので、文字列と判断し"メカ"と変換してくれる。）　↓データシートビューでクエリ実行結果を確認する。※「！(実行)」ボタンは不要。・・・　↓1件もヒットしない。そんなバカな( ´ﾟдﾟ｀)Accessが自動形成してくれたSQLで、ヒントを見つけていこう。　↓SELECT M_配信リスト.*, M_配信リスト.[タイトル]FROM M_配信リストWHERE (((M_配信リスト.[タイトル])="メカ"));読みにくいのでWHERE句の((()))を消去。※効果は変わらない。　↓SELEC

選択クエリのデザイングリッドに、アニメの「配信リスト」と「キャラクター名簿」をなんとなく置いて「クエリを実行」しただけで...Accessは「配信リスト：全111レコード」と「名簿：全20レコード」を掛け合わせた2,220行のレコードを表示させてくれる。「リレーションシップ（結合線）」を組んだわけでもなく、そのような命令式を書いたわけでもないのに、なぜなのか不思議。リレーションの話題になると、よく目にする「一対多（いち-たい-た）」テーブル同士において、1件のレコードに複数のレコードが結びつく様子を表現するらしいけど、日本語として不自然だしどちらが「多」なのか毎回こんがらがってしまう。でも、こういう場面に出会うとなんとなく腑に落ちる。配信リスト側が「一(一意)」、そこに群れるキャラ達が「多」で合ってる？人間の目では「これは配信リスト」「これは名簿」って各々のテーブルを独立した存在と理解する。でもAccessは違う。テーブル同士の関係性（ルール）に基づくデータ整理が目的のクエリデザイン画面に、命令をもたない双方が並ぶと、「とりあえず全ての組み合わせ（全パターン）を表示しとけば間違いないだろう！」と判断するのだろう。たまたま双方のテーブルに「Season」という共通フィールド。・「配信リスト」上のSeasonは、そのお話がどのSeasonに紐づくかを表示したもの・「名簿」上のSeasonは、そのキャラクターがどのSeasonから参加し始めたかを表示したものそれぞれ役割が異なる。ためしに「Season」同士をリレーションさせたが「名簿」マスタのSeason定義のあいまいさにより、実態

Accessのテーブルはこんな感じです。Excelのシートと変わりないですね。でもデータベースは、こんな感じの歯抜けを嫌います。空欄でも情報は情報なので、これだと「I0001のJANコードはNullですよ」ということになっています。Nullというのはなにもない、という意味なのですが、本当になにもないならなにもないはずです（よくわからない笑）。つまり枠をこしらえてしまうとそこにはなにかの情報を入れなくてはならず、Nullというのも立派な情報なので、本来I0001のJANコードは地球上に存在しないのにテーブル上ではI0001のJANコード＝Nullとなってしまうわけです。これはクエリを組む際など、いろいろ問題が出てきます。SQLも複雑になりますね。じゃあどうすりゃいいんだよ、という話ですね。こうします。たとえばI0001のJANコードを入力する場合は、縦に足します。これでJANコードのない商品には枠を設けずに済みます。空欄ができないので無駄なデータなし、速度と安定性も向上します。ただこの形式だと扱いづらいので、2つ上の画像みたいな作業用のテーブルを用意して、ふつう形式に変換するプログラムも合わせて用意します。もちろんぜんぶ必須項目ならふつう形式のテーブルで構わないのですが、なかなかそうはいかないですよね。Accessを学ばれている方は、こういう格納の仕方もあるんだよ、ということを覚えていただければと思います。変換プログラムはやや面倒なので、もし必要であれば安価でご提供いたしますよ～。

FROM句の対象テーブルからSELECT句で選び取ったフィールド(列)表示ここまでが既存の学習メモ内容今回は、選択済みフィールド情報の中から特定のレコード(行)のみ抽出だんだんややこしくなってきてる(ﾟдﾟlll)SELECTM_配信リスト.[話数],M_配信リスト.[タイトル], M_配信リスト.[放送日], M_配信リスト.[Season]FROMM_配信リストWHEREM_配信リスト.[フィールド名]=抽出条件;  　←【New】1. SQLの構造SELECT～FROM～に加え「WHERE句」が仲間入り。SELECT 句 SELECT M_配信リスト.[話数], M_配信リスト.[タイトル], M_配信リスト.[放送日], M_配信リスト.[Season]役割:どの列（フィールド）のデータを取得するかを指定詳細:テーブル名「M_配信リスト」の内、[話数][タイトル][放送日][Season]フィールドを表示。具体的なフィールド名をカンマ（,）で区切って「どのテーブルの、どの項目か」を正確に指定する。FROM 句 FROM M_配信リスト;役割:データの取得元となるテーブルを指定詳細:「M_配信リスト」テーブルからデータを取るように指定。これまでと違う点：テーブル名の後につける;（セミコロン）は、この後に続くWHERE句が引き継ぐため、今回は不要。WHERE 句WHEREM_配信リスト.Season = 3;役割:データの取得元となるテーブルから、必要なレコードのみ抽出。詳細:「M_配信リスト」テーブルの内、[Season]フィールドに「3」と入ったレコードのみ表示するよう

データベースの中身を全て確認したいときに用いられる、標準的なデータ取得SQLです。SELECT テーブル名.*FROM テーブル名;1. SQLの構造ここではふたつの「句」が登場している。SELECT 句 (SELECT M_配信リスト.*)役割:どの列（フィールド）のデータを取得するかを指定詳細:テーブル名「M_配信リスト」末尾の *（アスタリスク）は「すべての列」を意味し、「M_配信リスト」テーブルにあるすべての項目を取得できる。ただし、SQLの文法上、テーブル名やアスタリスクは独立した単語(トークン)として扱われ、それらを結びつける「ドット(演算子)」が必須とする取扱いが、リレーショナルデータベース業界の共通ルール。小さくて見つけにくいが、テーブル名と *（アスタリスク）の間には「.(ドット)」がちょこんと入る。この場合の「.(ドット)」は、「〜の」や「〜に属する」を意味する修飾子(クオリファイア)の働きをする。これを省略するとAccessはSQLの指示内容を読み取ることができず、「演算子エラー」となり、クエリ実行は中止される。FROM 句 (FROM M_配信リスト;)役割:データの取得元となるテーブルを指定詳細:「M_配信リスト」テーブルからデータを取るように指定。末尾の ;（セミコロン）は、SQL文の終わりを示す。2. 本SQLの役割「M_配信リスト」テーブルに格納されたすべてのレコード（行）と、すべての項目（列）を抽出して表示補足：「列」の呼び方についてAccessの設計画面やMicrosoft公式ヘルプなどでは、伝統的に「フィールド」という言葉が使われる。MyS

結合したテーブルから特定のフィールドを選び特定のフィールドが持つ値の順に並べ替え、特定のレコードだけを表示する。これまで作成したテーブル①M_名簿②M_登場人物③M_配信リスト④T_視聴各テーブルの共通フィールド(列の見出し)を内部結合(INNER JOIN)表示するフィールドを選択：[ゴジNo][キャラクター名][話数][タイトル][放送日][Seasoon][視聴数(万回)]指定したレコード(行)を抽出：[ゴジNo][キャラクター名]のレコード(行)を抽出し[話数][タイトル]の並べ替え：[視聴数(万回)]を降順表示抽出条件：今年度(Season4)は現時点で配信途中であるため、出力対象から除外。「条件：Season4未満」目的：キャラクター別に人気作品を調査します。SQLはこんな感じ：SELECTM_名簿.[ゴジNo], M_名簿.[キャラクター名], M_登場人物.[話数], M_配信リスト.[タイトル], M_配信リスト.[放送日], M_配信リスト.[Season], T_視聴.[視聴数(万回)]FROM ((M_名簿 INNER JOIN M_登場人物 ON M_名簿.[ゴジNo] = M_登場人物.[ゴジNo]) INNER JOINM_配信リスト ON M_登場人物.[話数] = M_配信リスト.[話数]) INNER JOINT_視聴 ON M_配信リスト.[話数] = T_視聴.[話数]WHERE (((M_名簿.[ゴジNo])="CH001") AND ((M_配信リスト.Season)<4))ORDER BYT_視聴.[視聴数(万回)] DESC

テーブル上の全フィールドを表示させ特定のフィールド順に、レコードの並べ替えをおこないます。特定のフィールドとは、「視聴数(万回)」です。アニメの放送話ごとの再生数を記録したフィールドです。「視聴数(万回)」フィールドの場所：既存のマスタテーブルに追加する方法と新たにトランザクションテーブルを追加しマスタテーブルに内部結合させる方法がある。再生回数の集計結果は日々変化し得る情報でありトランザクションテーブルで管理を行うのが王道なので今回はトランザクションテーブルを新たに設け既存のマスタテーブルと結合(JOIN)させる方法を採用する。1.トランザクションテーブルの構成を決める名称：T_視聴目的：[視聴数(万回)]を記録したい↓[視聴数(万回)]フィールドを置くことは確定。[視聴数(万回)]の記録は複数行(レコード)必要か？↓動画の再生数なので、集計結果は刻々と変化し得る。毎日せっせと集計したいほどではないけど、ないよりはあった方がいいかな・・・↓集計日を記録するための「記録日」フィールドを設けておこう。今回のトランザクションテーブル(T_視聴)は時系列データの保管場所となり「記録日」を軸に進行するため、「記録日」フィールドを主役とする。【注】1レコード1記録日であれば、データ型が日付型であっても頓着することなく「記録日」フィールドを主キーに設定することは可能だが今回のように、複数レコード(放送話)にわたって同じ記録日の入力が想定される場合（他と重複する場合）主キーにはできない。主キーを設けたい場合は「記録ID」など一意の値が入るフィールドを別途、用意する。2.マスタテーブル(M_配

テーブル上の全フィールド(列)・全レコード(行)を表示させ特定のフィールド順に、レコードの並べ替えをおこないます。特定のフィールドとは、「視聴数(万回)」です。アニメの放送話ごとの再生数を記録したフィールドです。「視聴数(万回)」の降順に並べ替えを行い、人気順を調査します。「視聴数(万回)」フィールドの場所：既存のマスタテーブルに追加する方法と新たにトランザクションテーブルを追加しマスタテーブルに内部結合させる方法がある。再生回数は流動性を持つ集計情報であり、トランザクションテーブルで管理を行うのが王道。ただし集計日別の管理をおこなう必要がなく、単純に集計時点の累計視聴数を上書きする形で問題なければ、M_配信リスト内で記録をとり、レポート機能で都度、集計日ごとの読み取りデータを出力しPDF等で情報を残せば足りる。基礎学習⑤では同一テーブル内に「視聴数(万回)」フィールドを準備。基礎学習⑥では別テーブルを用意し、記録日ごとに「視聴数(万回)」の累計数を書き留めていく方法をとってみる。⑥の方法では追加項目が一気に増えるため、順を追って整理していきたい。並び替えのために使用する関数DESC：降順ASC：昇順クエリを使わず、テーブルプロパティ「並べ替え」で降順設定をおこなう方法もある。[M_配信リスト].[視聴数(万回)] DESC同一テーブル(※)にて[視聴数(万回)]集計(※)マスタテーブルM_配信リスト全フィールド(列)・全レコード(行)表示SELECT M_配信リスト.*, FROM M_配信リスト【New】ORDER BY 句ORDER BY テーブル名.[フィールド名] D

データベースのテーブル全フィールドの内、特定フィールドを表示したいときに用いられる、標準的なデータ取得SQLです。SELECTテーブル名.[1つ目の指定フィールド名],テーブル名.[2つ目の指定フィールド名], テーブル名.[3つ目の指定フィールド名]FROM テーブル名;1. SQLの構造7-1.SQL基礎学習①と同じく、ここでもふたつの「句」が登場する。SELECT 句:SELECTM_配信リスト.[話数], M_配信リスト.[タイトル], M_配信リスト.[放送日], M_配信リスト.[Season]役割:どの列（フィールド）のデータを取得するかを指定詳細:テーブル名「M_配信リスト」前回のように全フィールドを一括取得する場合、*（アスタリスク）を用いた命令句が成立するが、一部のフィールドのみ取得する場合においては、具体的なフィールド名をカンマ（,）で区切って「どのテーブルの、どの項目か」を正確に指定する必要がある。今回はテーブル名「M_配信リスト」の内、[タイトル][放送日][Season]フィールドを表示。FROM 句:FROM M_配信リスト;役割:データの取得元となるテーブルを指定詳細:「M_配信リスト」テーブルからデータを取るように指定。末尾の ;（セミコロン）は、SQL文の終わりを示す。2. 本SQLの役割「M_配信リスト」テーブルに格納されたすべてのレコード（行）の内、指定した項目（列）を抽出して表示。補足：「フィールド名」の[](角かっこ)の意味「プログラムの命令(予約語)の内、ここからここまでが名称」と固有名詞の存在をAccessに伝えるための境界線。一

右外部結合（RIGHT JOIN）SQLをつくらないといけないパターン複数テーブルを用いてSQLを構成する際に用いる結合(JOIN)方法には、内部結合と(左/右)外部結合がある。【内部結合（INNER JOIN）】結合相手となるレコードが存在しない（またはNullである）場合結合を中止しお互いのテーブル(レコード)は非表示となる。【外部結合（LEFT JOIN/RIGHT JOIN）】結合先テーブルに一致するレコードがなくても（Nullでも）お互いのテーブル(レコード)を必ず表示する。【注意点】内部結合/外部結合の対応差によって影響を受けるのは「レコード（行）が表示されるか、されないか」であり設計したフィールド(レコードの見出し)自体はデータの有無に応じて消失することはない。【今回のテーマ】右外部結合(RIGHT JOIN)の必要性クエリのデザインビュー画面でテーブル間に結合線を引きJOIN(結合)させる際テーブル置き順、或いは結合線(矢印)の引く際のドラッグの方向によっては画面上の配置と、Accessが認識する「左右」が逆転するケースがある。Accessの「結合プロパティ」における左右は「画面上の見た目の位置」ではなく「結合線を引いた順番やAccess側の内部順序」で決まることがあり、「左外部結合にしたいから2番（左の全レコード）を選ぼう」と機械的に選ぶと失敗する。外部結合を選ぶ場合、左右どちらを主フィールドとするかは画面上の左右ではなく、ダイアログ内の具体的な説明文で判断しないと危険！第1段階のJOIN(結合プロパティ)上から2番め: M_登場人物の全レコードを表示する設定

左外部結合（LEFT JOIN）SQLをつくる複数テーブルを用いてSQLを構成する際に用いる結合(JOIN)方法には、内部結合と(左/右)外部結合がある。【内部結合（INNER JOIN）】結合相手となるレコードが存在しない（またはNullである）場合結合を中止しお互いのテーブル(レコード)は非表示となる。【外部結合（LEFT JOIN/RIGHT JOIN）】結合先テーブルに一致するレコードがなくても（Nullでも）お互いのテーブル(レコード)を必ず表示する。【注意点】内部結合/外部結合の対応差によって影響を受けるのは「レコード（行）が表示されるか、されないか」であり設計したフィールド(レコードの見出し)自体はデータの有無に応じて消失することはない。【今回のテーマ】現時点における全放送話リストの記録状況を出力し、未記入や条件不適合のレコード情報をチェックする。（「M_配信リスト」の話数に「M_登場人物」、  「M_登場人物」のゴジNoに「M_名簿」を繋ぎ、リストを見える化。）【左外部結合（LEFT JOIN）】【例】それぞれの放送話に登場するキャラクター名を記録していく。ただし、114話のうち、「M_登場人物」にデータがあるのは70話まで。また、71話めのゴジNoとして、「ココナラ」とダミー情報が混在。もちろん、「M_名簿」にはそのようなキャラクター登録はない。左外部結合の結果、データはどのように表示されるか？SELECT  M_配信リスト.[話数],   M_配信リスト.[タイトル],   M_登場人物.[ゴジNo],   M_名簿.[キャラクター名]FROM  (M_配信

内部結合（INNER JOIN）SQLをつくる複数テーブルを用いてSQLを構成する際に必要な結合(JOIN)方法には、内部結合と(左/右)外部結合がある。【内部結合（INNER JOIN）】結合相手となるレコードが存在しない（またはNullである）場合結合を中止しお互いのテーブル(レコード)は非表示となる。【外部結合（LEFT JOIN/RIGHT JOIN）】結合先テーブルに一致するレコードがなくても（Nullでも）お互いのテーブル(レコード)は必ず表示される。【注意点】内部結合/外部結合の対応差によって影響を受けるのは「レコード（行）が表示されるか、されないか」であり設計したフィールド(レコードの見出し)自体はデータの有無に応じて消失することはない。【今回のテーマ】それぞれの放送話に登場するキャラクター名を記録していく。すでにほかの記事で作成した「M_名簿」マスタテーブルを使用。各放送話への出演記録は別のマスタテーブルで管理をおこなう。【New】マスタテーブル「M_登場人物」を新設。114話分のレコードを設けたが、1話1レコードの構成。それぞれの放送話には複数名のキャラクターが登場する。そこで、複数の「ゴジNo」フィールドを試みたがAccessに拒否られる。1話に複数の登場人物を記録するためには、レコードを増やすしかない。当たり前のことかもしれないが、こういうところで躓くのだ。そこで、レコードを増やすための作業にかかる。2行目に新しいレコードを追加しようとしても114行目の下(最下段)に流されてしまう1話から順にレコード増やそうにも新規作成は最終行とかこういうとこホント、融

INNER JOIN句を用いた検算方法（請求書）管理職の代理で、請求書の内容チェック～決裁をおこなう必要が生じた。私の人生にAccessは必要ない...と断言して早1ヶ月。電卓を叩く工程だけでもAccessに任せられないものか、画面と睨めっこ中。方法を調べる内、これまで取り組んできたINNER JOINの学習が活かせる可能性が生じた。別々のオブジェクト(テーブルやクエリ)に存在する同一データのレコード(行)を探し当て各オブジェクトの列(フィールド)をあたかもひとつのオブジェクト上のデータのように繋ぎ合わせることができる。ココナラのユーザー登録を例に挙げると...(会員IDを各テーブルの仲介役と仮定）テーブル1: ココナラ登録会員リストテーブル2: ココナラブログ投稿歴のある会員リストテーブル3: ココナラサービス出品歴のある会員リストテーブル4: ココナラサービス購入歴のある会員リストそれぞれのテーブルに、各条件を満たす会員IDが格納された状況下において「ココナラに会員登録している」かつ「ココナラブログを1度でも書いたことがある」かつ「ココナラで何らかの商品を出品したことがある」かつ「ココナラで何らかの商品を購入したことがある」INNER JOIN句を用いたSQLにより命令を与えることで、テーブル別に分類された上記条件をすべて満たす会員データを抽出することも可能。INNER JOIN句のこうした働きを応用することで、日頃は別々に運用されているテーブル同士またはテーブルをもとに作成されたクエリ同士を呼び集め一定の条件下における演算にも展開できる・・・はず。社内情報を社外に持ち出す

YouTubeでも紹介しています。是非ご覧ください。世の中は便利になり、power query　power pivotとうがあります。 なんちゃってデータベースです。 しかし、バッタもんは、どこまで行ってもバッタもんです。 Power Query Power Pivotなんて使わない 今回は、クエリについて説明します。 以前に、Excelユーザーが知らないデータベースの作成方法等を紹介しました。 私の個人的な意見としては、私の趣味における競馬には、全く利用できません。 なぜかというと、私は、膨大なデータの中から、統計で、競馬の必勝法を、探しています。 四半期とか、先月とか、そんな話ではありません。 競馬は通常、土日で、6開催あり、約1,000頭の出走があります。 1ヶ月で、約4,000頭です。 そして、検索機能を付けて、その結果を、また、テーブルに保存するという内容です。 その中で、周知の事実かもしれませんが、結構、役に立つ競馬の必勝法を紹介します。 このブログはこんな人にお勧め ACCESSを勉強している人 ACCESSでクエリを勉強している人 競馬で高確率で、的中する情報が欲しい人 このブログを、読み終わるころには・・・・ データベースに関しては、欲しい情報が、すぐに抽出できなければ意味がありません。 実務においても、競馬においてもです。 今回は、グループ毎に抽出します。 フラグを立てたデータから、グループ全体を抽出します。 クエリを利用して、個人から全体を見る 木を隠すなら森。 このような言葉がります。 確かにその通りですね。 隠した木が、他の木に紛れるからです。 競