ファン
最新記事
写真ギャラリー
日別アーカイブ

広告

この広告は30日以上更新がないブログに表示されております。
新規記事の投稿を行うことで、非表示にすることが可能です。
posted by fanblog

2019年02月03日

Accessクエリ,クロス集計クエリ




送料・手数料・査定料・梱包材がすべて無料【パソコン買取アローズ】

マイクロソフトエクセルのまとめ記事はこちら


Microsoft Office ブログランキングへ

クロス集計クエリについて

クロス集計クエリを作ってみましょう。

例えばある年度の毎月の切手種類別の入庫数を出したいと

思いませんか。

クロス集計クエリ1.png

イメージとしては上のようなものです。

このような表をAccessで作ってみます。

クロス集計クエリ2.png

作成リボンから「クエリウィザード」を選択して「クロス集計クエリ」を

選択して「OK」を押してください。

クロス集計クエリ3.png

「クロス集計クエリウィザード」が表示されるので、「クエリ」から

「Q_入庫」を選択してください。

「Q_入庫」は「T_入出庫」から選択クエリで作成しております。

「次へ」を押してください。

クロス集計クエリ4.png

最初に「行見出し」を決めます。

ここでは「切手種類」だけあればいいので「種類」だけ選択します。

「次へ」を押してください。(行見出しは3つまで選択できます)

クロス集計クエリ5.png

次は「列見出し」を決めます。

今回は「「日付」を選択して「次へ」を押してください。

クロス集計クエリ6.png

ここでは日付/事故型フィールドの中からグループ化する単位を指定します。

とりあえず毎月なので「月」を指定します。

「次へ」を押してください。

クロス集計クエリ7.png

ここで集計するフィールドを選択します。

「入庫数」を選択して「集計方法」を選択します。

今回は「合計」が欲しいので「合計」を選択します。

「次へ」を押してください。

クロス集計クエリ8.png

ここでクエリに名前を付けます。

そして「完了」を押してください。

クロス集計クエリ9.png

上記のような表が完成します。

【ゲーム買取ブラザーズ】



クロス集計クエリ10.png

それからリボンの「集計」ボタンを押すと「集計行」が表示されます。

ここで「合計」を選択すると各月の合計が表示されます。

これも便利ですね。

クロス集計クエリ11.png

もう一つクロス集計クエリを載せておきます。

これは「行見出し」で「種類」と「購入先」の2つを選択した場合です。

種類別・購入先別で集計されています。

このように行見出しをうまく選択すると様々な条件でクエリが作成できます。

Excelのピボットテーブルでも同じようなものができますね。

色々条件を変えて試してください。




ノートパソコンのことならパソコン工房通販ショップ


2019年02月21日

Accessクエリ,重複クエリ,不一致クエリ,更新クエリ






マイクロソフトエクセルのまとめ記事はこちら


Microsoft Office ブログランキングへ

Accessその他のクエリ


【重複クエリ】

重複クエリとはテーブル内で重複したデータを抽出します。

何かの名簿があって同じ方を登録している可能性がある場合などに

使用すると便利です。

名前が同じでも他の項目が違う場合とかありますね。

その時は別人として区別されます。

今まで使ってきた切手在庫管理から重複クエリを作ってみましょう。

「T_入出庫」テーブルをもとにして、同じ日付で同じ種類を登録している

ものを抽出してみましょう。

重複1.png

クエリウィザードから「重複クエリ」を選択して「OK」を押してください。

重複2.png

「T_入出庫」テーブルを選択して「次へ」を押してください。

重複3.png

ここで重複データを調べるフィールドを設定します。

同じ日付で同じ種類を登録しているものを抽出したいので「日付」と

「種類」を選択します。

「次へ」を押してください。

重複4.png

ここはクエリで表示させたいフィールドを選択します。

一応全部選択します。

「次へ」を押してください。

重複5.png

ここで名前を付けます。「完了」を押してください。

重複6.png

このように同じ日付で同じ種類のものが抽出されました。

個人的にあまり使用頻度は高くないですね。

まあこのようなことができるということを覚えておいたらいいのかなと思います。


今会計関係でアプリを作っていますが、同じテーブルなのに入力タイミングが

違う場合があります。

(ひどいときは2カ月くらい離れる時があります)その際入力したかどうか

よく覚えていない時があるので、いったん入力して、後で重複クエリを実行

して重複を探す場合があります。

その他何か募集をして応募してきた方に重複がないかどうか調べるとか、

使用する場面は多々あると思います。

まずは15日間【無料】お試し利用から!大容量200GB〜レンタルサーバー『HETEML





【不一致クエリ】

不一致クエリとは2つのテーブルまたはクエリを比較して「不一致レコード」

を抽出するクエリです。

非常に単純な例を上げます。

ある月のある製品の売り上げをまとめたものです。

テーブル1とテーブル2があります。

(テーブル1)

不一致1.png

(テーブル2)

不一致2.png

上記の2つのテーブルを比較して日付の違うものを抽出することにします。

クエリウィザードから「不一致クエリ」を選択して「OK」を押してください。

不一致3.png

ここで比較するテーブル(クエリ)を選択します。

「次へ」を押してください。

不一致4.png

ここでもう一つのテーブル(クエリ)を選択します。

「次へ」を押してください。

不一致5.png

関連付けるフィールドを選択します。

今回は日付を比較したいので両方の日付を選択して「<=>」を押します。

「次へ」を押してください。

不一致6.png

ここは不一致クエリに表示させるフィールドを選択します。

今回はすべて選択して「次へ」を押してください。

不一致7.png

ここで名前を付けて「完了」を押してください。

不一致8.png

このように日付が一致しないレコードだけ抽出されました。

個人的にはこの不一致クエリもあまり使用しませんね。

ショッピングモール【カラメル】にも出店できるから
集客力抜群の【カラーミーショップ】

https://px.a8.net/svt/ejp?a8mat=2NR5KR+GC8AGI+348+I47XV




【更新クエリ】

このクエリは結構便利なので時々使っています。

よく使う場面としては、何かの処理をした際に「処理日を自動的に入れる」

ということが考えられますね。

また数値が入っていて、その数値を何倍にしたいとか、

何%割引きにしたいとかいうときに「更新クエリ」を使用すると

一気に更新できます。

私は入場券発券で使用しましたね。

申し込みがあればどんどん入力していき、時々まとめて入場券を発券します。

発券したら更新クエリで発券日に日付を入れていく。

発券日が記載されているテーブルは今後発券されないという仕組みですね。


一度住所録を使って更新クエリを体験しましょう。

「T_住所録」に「処理日」というフィールドを追加して、

何か作業した後に「更新クエリ」を実行して、

更新1.png

住所録に「処理日」というフィールドを追加しました。

「更新クエリ」の作り方ですが、一番簡単な方法を記載します。

クエリを作成するときの「クエリデザイン」を開いて、「T_住所録」を追加します。

更新2.png

ここから更新したいフィールドを選択します。

今回は処理日を更新したいので、処理日だけ選択します。

更新3.png

上のように「処理日」だけ選択してリボンの「更新」ボタンを押します。

このボタンを押すと「更新クエリ」になります。

更新4.png

ここの表示が変ります。

「並べ替え」から下が変りましたね。

「レコードの更新」「抽出条件」「または」になりました。

今回は「レコードの更新」を使用します。

更新は「処理日」を入れるように考えています。

処理日を入れるとしたら当然「当日」ですね。

ですからここに「Date()」と入力します。

「Date()」の意味は分かりますね。

「今日の日付」という意味です。

Excel関数の「Today()」と同じです。

更新5.png

上記のように入力します。

これで閉じてください。

閉じるときに名前を聞いてきますので「Q_処理日」にしました。

更新6.png

このように少し変わったアイコンで「Q_処理日」という更新クエリが出来ました。

実行するのは単にこのクエリを選択してダブルクリックすれば実行されます。

通常はフォームにボタンを作り何らかの作業をさせて、

最後にこの更新クエリを実行させるというやり方が一般的でしょう。

とりあえず一度やってみますね。

最初に確認のために「T_住所録」を開きます。

そして「処理日」に何も入っていないことを確認して閉じます。

次に「Q_処理日」をダブルクリックします。

表面上は何も変化がありません。

そして再度「T_住所録」を開きます。

更新7.png

処理日に日付が入っています。

これが「更新クエリ」です。

例えば後日新しい住所録を追加したとします。

その時は選択クエリで「処理日」が空白のレコードのみ抽出して

更新クエリを実行すると新しく追加されたレコードだけその日の日付が追加されます。

このようにすると何時処理されたのか履歴が残っていきますし、

処理忘れもなくなってきます。

この「更新クエリ」はうまく使えば非常に便利です。是非覚えておきましょう。




┏━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━[PR]━┓
 高速・多機能・高安定レンタルサーバー『エックスサーバー』
 ─────────────────────────────────
・月額900円(税抜)から、大容量200GBからの高コストパフォーマンス
・安定のサーバー稼働率99.99%以上
・高速性を重視し、最新20コアCPU(Xeon E5シリーズ)+192GBメモリ
 +RAID10を採用!
・幅広いバージョンのPHPやSSHに対応!
・初心者でも安心の24時間365日メールサポート!
 ─────────────────────────────────
  https://px.a8.net/svt/ejp?a8mat=2NR2G3+DG1FLE+CO4+5ZU2B
┗━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┛

2019年02月24日

Accessクエリ,ユニオンクエリ

ノートパソコンのことならパソコン工房通販ショップ





マイクロソフトエクセルのまとめ記事はこちら


Microsoft Office ブログランキングへ

【ユニオンクエリ】

ユニオンクエリはクエリの中でも比較的作成が難しいですね。

通常クエリを作成する場合は「クエリウィザード」か「クエリデザイン」

から作成すれば、ほぼ自動的に作成できますね。

しかしユニオンクエリはSQLビューで記述していく必要があります。

SQLビューというのはどのようなものか例を表示します。

以前使用した「切手在庫管理」で「Q_在庫数」というクエリがあります。

ユニオン1.png

このようなものでしたね。

これを「SQLビュー」にします。

ユニオン2.png

「表示」の部分で「SQLビュー」を選択します。

すると画面が右のような画面になりましたね。これがSQLです。

このようなものを自分で記述していく必要があります。

「SELECT Q_全入庫.種類, Q_全入庫.入庫数の合計, Q_全出庫.出庫数の合計,

[入庫数の合計]-[出庫数の合計] AS 在庫数

FROM Q_全入庫 INNER JOIN Q_全出庫 ON Q_全入庫.

種類 = Q_全出庫.種類

GROUP BY Q_全入庫.種類, Q_全入庫.入庫数の合計,

Q_全出庫.出庫数の合計,

[入庫数の合計]-[出庫数の合計];」ここに書かれている内容は

この通りです。

内容に関して、今は説明しません。

ユニオンクエリを説明している中で理解できてくると思います。





どのような時にユニオンクエリを使用するのか?

ユニオン3.png

例えばExcelファイルで上のように2016年度と2017年度で別ファイルが

あったとします。

これを結合するのは簡単ですね。

どちらかのデータを行コピーして別のファイルに張り付ければOKです。

ユニオン4.png

ここでAccessの場合はどうか考えてみましょう。

Accessで「T_2016入出庫」と「T_2017入出庫」の二つのテーブルが

あった場合どうすれば結合できるか考えてください。

ユニオン5.png

クエリデザインで上記のようにしたとして、ここからどうしますか?

両方のテーブルから各項目を選択したとして、横に並ぶだけですね。

縦には結合できないですね。

このような時に「ユニオンクエリ」を使用します。

【国認定】不用PCの宅配便回収<リネットジャパン>


「ユニオンクエリの作り方」

まず、クエリデザインを選択します。

「テーブルの表示」が出ますが、「閉じて」ください。

ユニオン6.png

リボンの「SQL表示」か「ユニオン」ボタンかどちらかを押してください。

若干表示内容が違います。SQL表示を押した場合で作成していきましょう。

「SQL表示」を押してください。

ユニオン7.png

上記のように「SELECT;」という内容になりますね。

この「SELECT」と「;」の間に記述していきます。

この「SELECT」と「;」は既定です。

先ほどのリボンのボタンで「ユニオン」を押した場合、

このこの「SELECT」と「;」は記述されていません。

白紙の状態でスタートします。

最初に複数テーブルを結合する場合の条件を考える必要があります。

各テーブルのフィールド名とフィールド数が同じ場合。

この場合は「追加クエリ」を使用することもできると思います。

また各テーブルのフィールド名とフィールド数が違う場合。

この場合はユニオンクエリでしか結合できません。

基本のユニオン

2つの同じフィールドのテーブルを結合する場合。

SELECT * FROMテーブル1

UNION SELCT * FROMテーブル2;

上記のようになります。

もしテーブルが3つあれば同じようにして「テーブル3」を

追記すればOKです。

ユニオン8.png

SELECT * FROM T_2016入出庫

UNION SELECT * FROM T_2017入出庫;

上記のように記述しました。

これで「通常表示」にしてみましょう。

ユニオン9.png

上記のように2016年度と2017年度が結合されて表示されています。

ユニオンクエリはこのように使用します。

項目を指定して作成することもできます。

上記の中から「ID」と「購入先」と「出庫先」を省いてみましょう。

ユニオン10.png

SELECT T_2016入出庫.日付,T_2016入出庫.種類,T_2016入出庫.入庫数,

T_2016入出庫.出庫数

FROM T_2016入出庫

UNION SELECT T_2017入出庫.日付,T_2017入出庫.種類,T_2017入出庫.

入庫数,T_2017入出庫.出庫数

FROM T_2017入出庫;

今度は少し記述内容が違いますね。

最初に表示したいフィールドを選択します。

「T_2016入出庫.日付,T_2016入出庫.種類,T_2016入出庫.入庫数,

T_2016入出庫.出庫数」の部分ですね。

「テーブル名.フィールド名」と記述して「,」で区切ります。

フィールド名が選択出来たら「FROM T_2016入出庫」で

どのテーブルから選択するのかを記述します。

その下の2017年度分は同じ内容ですね。

この状態で通常表示にします。

ユニオン11.png

「ID」と「購入先」と「出庫先」が無くなっていますね。

そしてこの下に2017年度分が結合されています。

アメコミ映画を観るなら<U-NEXT>






次はフィール名を変更したい場合。

名前を変更したい場合というのは結構あります。

その場合はどうするのか。

例として「日付」を「日」だけに変更してみましょう。

SELECT T_2016入出庫.日付 AS 日,T_2016入出庫.種類,

T_2016入出庫.入庫数,T_2016入出庫.出庫数

FROM T_2016入出庫

UNION SELECT T_2017入出庫.日付 AS 日,T_2017入出庫.種類,

T_2017入出庫.入庫数,T_2017入出庫.出庫数

FROM T_2017入出庫;

上記のようにします。

「T_2016入出庫.日付 AS 日」とありますね。

これは日付という名前を日に変更する場合「元のフィールド名」+

「AS」+「新しいフィールド名」で変更できます。

ユニオン12.png

上記のように日付が日になっていますね。

2つ以上のテーブルでたまたま名称が違うときとか、

名前を変更したい時などに便利です。





※ フィールド数が違う場合

たまにフィールド数が違うが結合させたいという時があります。

これもExcelなら簡単ですね。

例としてT_2016年入出庫の備考欄を削除します。

そのうえで結合したいと思います。

SELECT T_2016入出庫.ID,T_2016入出庫.日付,T_2016入出庫.種類,

T_2016入出庫.入庫数,T_2016入出庫.出庫数,T_2016入出庫.購入先,

T_2016入出庫.出庫先,NULL AS 備考

FROM T_2016入出庫

UNION SELECT T_2017入出庫.ID,T_2017入出庫.日付,T_2017入出庫.種類,

T_2017入出庫.入庫数,T_2017入出庫.出庫数,T_2017入出庫.購入先,

T_2017入出庫.出庫先,T_2017入出庫.備考

FROM T_2017入出庫;

上記のように記述します。

T_2016入出庫の最後の部分「T_2016入出庫.出庫先,NULL AS 備考」ですが、

「出庫先」の後に「NULL AS 備考」とあります。

ここがT_2016入出庫に無い部分なので「NULL」にします。

そしてその「NULLに「備考」というフィールド名を付けたということです。

ユニオン13.png

上記のようになりました。

また抽出とかを行いたいときも可能です。

ユニオンクエリで2つのテーブルを結合して「82円」だけ抽出したいと

思います。

SELECT T_2016入出庫.ID,T_2016入出庫.日付,T_2016入出庫.種類,

T_2016入出庫.入庫数,T_2016入出庫.出庫数,T_2016入出庫.購入先,

T_2016入出庫.出庫先,NULL AS 備考

FROM T_2016入出庫

WHERE ((T_2016入出庫.種類)=82)

UNION

SELECT T_2017入出庫.ID,T_2017入出庫.日付,T_2017入出庫.種類,

T_2017入出庫.入庫数,T_2017入出庫.出庫数,T_2017入出庫.購入先,

T_2017入出庫.出庫先,T_2017入出庫.備考

FROM T_2017入出庫

WHERE ((T_2017入出庫.種類)=82);

抽出の場合はWHERE ((T_2017入出庫.種類)=82)のように「WHERE」を

使用します。

ユニオン14.png

上記のように2つのテーブルが結合され、82円だけ抽出されました。

次に並べ替えを追加したいと思います。

上記のユニオンクエリに日付順の並べ替えを追記します。

SELECT T_2016入出庫.ID,T_2016入出庫.日付,T_2016入出庫.種類,

T_2016入出庫.入庫数,T_2016入出庫.出庫数,T_2016入出庫.購入先,

T_2016入出庫.出庫先,NULL AS 備考

FROM T_2016入出庫

WHERE ((T_2016入出庫.種類)=82)

UNION

SELECT T_2017入出庫.ID,T_2017入出庫.日付,T_2017入出庫.種類,

T_2017入出庫.入庫数,T_2017入出庫.出庫数,T_2017入出庫.購入先,

T_2017入出庫.出庫先,T_2017入出庫.備考

FROM T_2017入出庫

WHERE ((T_2017入出庫.種類)=82)

ORDER BY 日付;

最後に「ORDER BY 日付」を追記しました。

日付を並べ替えしなさいという記述です。

ユニオン15.png

上記のように日付順に並びました。

他の選択クエリとかクロス集計クエリとかありますが、

すべて「SQLビュー」で確認できます。

SQLビューで書かれているような記述をすれば同じ結果がでます。

まあクエリウィザードとかクエリデザインでできるクエリはそちらを

使用しましょう。

どうしてもできない場合だけユニオンクエリを利用することにしましょう。


ユニオンクエリは是非覚えてください。

世界中8000万人が利用する話題のホテル検索サイト『トリバゴ』





【追加クエリ】

ユニオンクエリの途中で追加クエリという名前を出しました。

これもついでに紹介しておきましょう。

通常のクエリデザインから作成します。

条件として同じフィールド数で同じフィールド名ということで

作成いたします。

ユニオンクエリで使用したT_2016入出庫とT_2017入出庫を使用します。

今回はどちらも備考欄は省いています。

とりあえず

T_2017入出庫を選択してすべての項目を選択してください。

追加1.png

リボンの「追加」というボタンを押してください。

「追加」というボックスが出てきますので「テーブル名」で

T_2016入出庫を選択して「OK」を押してください。

追加2.png

レコードの追加という行が出てきますね。

一応「ID」はオートナンバー型なので赤丸部分は消しておきましょう。

そしてリボン左上にある「実行!」を押してください。

そして通常表示にしてください。

追加3.png

2017年の下に2016年のレコードが追加されていますね。

これが追加クエリです。


大体ここまででクエリについて紹介してきたつもりです。

Accessの心臓部はクエリです。

あらゆる加工について、ほぼクエリで実行できると思ってください。

クエリでの計算等もあまり紹介していませんが、様々な計算ができます。

例えば生年月日というフィールドがあり、クエリで「年齢」という

フィールドを追加します。

そこに「年齢: DateDiff("yyyy",[生年月日],Now())-1」という式を

挿入すると「年齢」が表示されます。

Access用の関数が用意されていますので、

その関数を使用して様々な計算ができます。

そのあたりは「何がしたいか」という明確なものがあればネットで

探せばいくらでも出てきます。

そうやって自分で勉強していけば実力がついてくると思います。




■□■━━━━━━━━━━━━━━━━━━━━━━━━
     +++ 月額1,980円(1年間・税別)で使えるスマホ【UQmobile】 +++
 https://px.a8.net/svt/ejp?a8mat=2TIIN2+9W1A2Q+2CPQ+1BQJAB
------------------------------------------------------
 ・番号そのままお乗換え可能!
 ・当サイト限定初期費用&送料無料!
 ・0円端末など豊富なラインナップ!
━━━━━━━━━━━━━━━━━━━━━━━━■□■

最新コメント
プロフィール
さんの画像

60過ぎのおっちゃんです。手探りでブログを立ち上げました。至らない点が多々あると思いますがご容赦ください。
プロフィール
×

この広告は30日以上新しい記事の更新がないブログに表示されております。