Archive for the ‘OBA開発’ Category
SQL Serverの実習につかいたい、ECS 超小型 Bay Trail-M 搭載デスクトップ LIVA-C0-2G-64G-W
これを見つけたのはソフマップの店頭だった。実習用のネットワークでサーバーとして働く小型のコンピュータを探していたところ、みつけた。
これにSQL Serverをインストールし、勉強会の実習でいつでも使えるようにしておきたい。とにかく、小型で軽いことが特徴だ。スペックは、Intel Bay Trail-M SoC Celeron N2807搭載、最大TDPわずか4.3Wでファンレス設計による完全無音動作。MMC64GBストレージ、容量2GBの低電圧DDR3Lメモリ内蔵、Wi-Fi 802.11a/b/g/n、Bluetooth 4.0標準搭載、本体重量約190g、筐体容積0.4リットル、最大消費電力約14.9Wという圧倒的な省電力性能によりACアダプタで動作。HDMI、D-sub同時出力、マルチモニタに対応、USB 3.0×1、USB 2.0×1、ギガビットLANポート×1搭載、5V/Micro USB形状の電源コネクタを採用。とても斬新な設計だ。
本体パネルの手前には、電源ボタンとイヤホンジャックのみがある。合理的なデザインだ。その他のコネクタは、すべて後ろ側のパネルにまとめられている。
大きさは、まさに「手のひらサイズ」だ。片手で楽々つまめる大きさで、しかも軽い。本体がプラスチックでできているために、とても軽い。ファンレスであることも軽量化に寄与している。
質量を測ってみよう。手持ちのキッチンスケールに載せて重さを測定する。小型のキッチンスケールにも十分乗る大きさだ。実測値は188g。
電源供給がmicro USBであるところも面白い。専用のACアダプターが同梱されているが、使うことはないだろう。micro USBならどこででも給電できる環境があるからだ。
久しぶりにレビューしたい面白いコンピュータに出会ったが、実際これにどんなOSをインストールするかだ。本来はデスクトップPCとして使うように設計されているようだが、できればWindows Serverをインストールし、SQL Serverを動かしてみたい。そうすれば実習環境で使える便利なサーバーになるからだ。
SQL Server に接続した InfoPath フォームのデータ接続を手作業で変更する – 繰り返しテーブルのデザインを生かすため、繰り返しテーブルのバインドを保持したまま接続するテーブルやビューを変更する
InfoPath はデータ接続を SQL Server にすることができ、SQL Server に対するクライアントアプリケーションとして利用することができる。Visual Studio を使ってアプリケーションを開発することが簡単になったとはいえ、データベースアプリケーションを作るのは、やはり、それなりに難しい。そこでクライアントに InfoPath を使うメリットがある。
InfoPath は SQL Server へのデータ接続をウィザードで構成することができ、一行もコードを書かずにクライアントアプリケーションにすることができる。また InfoPath フォームに繰り返しテーブルを作ると、簡単にデータ接続にバインドできる。このとき、繰り返しテーブルの列幅やコントロールの変更、コントロールのプロパティ変更、条件付き書式、動作規則などを設定した後で、データ接続を変更したいことがある。データ元のテーブルやビューを切り替えたい場合だ。
いちど構成したデータ接続は、ウィザードでいつでも変更することができる。しかしウィザードでデータ接続を変更すると、デザインされた繰り返しテーブルとデータ接続の関連付けが壊れ、もう一度繰り返しテーブルのデザインをやり直さなければならなくなる場合がある。繰り返しテーブルの関連付けが壊れると、フィールドに赤の「×」が表示される。
＜Fig.1 : デザインされた繰り返しテーブルをもつフォームのデータ接続を変更して関連付けが壊れた＞
手間をかけてデザインした繰り返しテーブルは、できればそのまま使いたい。このとき、次のようにデータ接続の SQL 文を手作業で変更すると、データ接続と繰り返しテーブルの関連付けを壊すことなく接続元の SQL Server のテーブルやビューを変更することができる。
データ接続ウィザードを起動してデータ接続の編集をする。あらかじめ設定されたデータ接続を表示し、接続する SQL Server のフィールドが表示されたら「SQL の編集」ボタンをクリックして SQL 文を直接編集する。
＜Fig.2 : データ接続ウィザードでデータソースを編集する＞
「SQL の変種」をクリックすると、設定された SQL Server へ接続する SQL 文が表示される。ここで接続先のテーブルまたはビューの名前だけを変更する。
ここでは、もともと「v_講座一覧」というビューに接続していたが、表示したいフィールドを増やした新しいビュー「v_テスト用の講座一覧」というビューに変更している。
＜Fig.3 : 変更する前のデータ接続の SQL 文＞
＜Fig.4 : 変更後のデータ接続の SQL 文＞
SQL 文を変更すると、変更したテーブルまたはビューのフィールドがデータ接続ウィザードに表示される。追加したい必要なフィールドにチェックを入れる。
＜Fig.5 : 接続先を変更したビューのフィールドが表示される＞
＜Fig.6 : 表示したいフィールドにチェックを入れる＞
データ接続の編集を終わってフォームのデザインに戻ると、繰り返しテーブルにエラーは表示されず、テーブルとデータ接続のバインドが保持され、壊れていないことがわかる。あとは繰り返しテーブルに列を追加し、新たに接続したフィールドを構成すればいい。
＜Fig.7 : 繰り返しフィールドとデータ接続のバインドは保持され、必要なフィールドを追加する＞
InfoPath の特徴は、データ接続をウィザードで簡単に構成することができ、繰り返しテーブルの項目も単なるテキストボックスからドロップダウンリストボックス、チェックボックス、ラジオボタンなどに簡単に変更することができること、また条件付き書式や動作規則で表示を変えたり、データを変更したときに動作をさせたりする設定ができることだ。しかし使いやすいフォームを構成するには、繰り返しテーブルの要素を、それなりの手間と時間をかけて編集することになる。繰り返しテーブルを構成した後でデータ接続を変更し、せっかく時間をかけてデザインした繰り返しテーブルのバインドが壊れて使えなくなってしまうのはもったいない。データ接続の SQL 文をうまく編集して、時間と手間をかけて作った繰り返しテーブルのデザインが活かせるようにしよう。
神戸SQL Server Users GroupのSQL Serverセミナー（初級１）を実施した
校務の情報化にはデータベースの知識と技術が必要なことから、SQL Serverを勉強するコミュニティを作った。とりわけ今年度から指導要録のデータベース化が一歩前進することから、各学校の担当者はデータベース化に取り組まざるを得ないはずだ。
神戸SQL Server Users Group
http://kobesqlserverusersgroup.wordpress.com/
昨日、2013年11月9日（土）には神戸SQL Server Users Groupと特定非営利活動法人 情報技術相互支援協会の共催で、SQL Serverセミナー（初級１）を開催した。
このSQL Serverセミナーでは、SQL Serverの初心者がSQL Serverの基礎を学び、データベースを使ったシステムを作り運用することができるようにすることを目標としている。1回のトレーニングを90分とし、初級講座を1、2と分けて企画した。初級1ではSQL Management Studioの使い方、テーブル作成、データベースダイアグラム、ビューとリレーションシップ、データの型、簡単なSQL文、を内容とした。初級2ではストアドプロシージャ、トリガ、ユーザー定義関数、データベースダイアグラム、SQLの応用、を計画している。
初級セミナーは1と2でおおよそ基本を網羅するように考えたが、やはり90分では深まりに欠ける。あらかじめ用意した初級1のプリントをひととおり網羅したが、やりたいと思っていた直積やPIVOTまで説明できなかったし、SQL文もごく基本的なものに留まった。次回は初級1.5的なセミナーとし、フォローアップを考えている。
今後もSQL Serverセミナーは神戸三宮で定期的に開催する予定だ。日程は決まり次第、神戸SQL Server Users Groupのblog http://kobesqlserverusersgroup.wordpress.com/ に公開するので、興味がある人はチェックしてほしい。
2012年12月29日（土）、「第９回アドミンティーチャーズ勉強会（大阪）～それで使ってるつもりですか？Microsoft Office、Microsoft MVPが正しいOfficeの使い方を伝えます～」のご案内
年末のあわただしい折ではあるが、2012年12月29日（土）に表記の勉強会を開催することにした。この勉強会では、Excel、Word、PowerPoint、の著名なMicrosoft MVPの方々にお話をいただく。不肖私はInfoPathやSQL Serverについてのお話をさせていただく。
4人のMVPでテーマを相談したとき、いくつかの案があった。それは、たとえば「誰も教えてくれなかった、Officeソフトの使い方」、「知って得するOfficeの作法」、「あなたの知らないOffice」といったものだった。とにかくこの4人のMVPはOfficeが大好きで日々仕事や日常生活の中で使いこなしている方々だ。特にExcelの田中さんは数々の著書もある有名な方で、きっとセッションの中で役に立つことをたくさんお話しいただけると思う。休憩時間での質問も大歓迎だ。
いろいろと忙しい年末だと思うが、ぜひ多くの方に来ていただきたい。懇親会もあります。
参加は、アドミンティーチャーズWebサイトから電子メールで申し込みを。
アドミンティーチャーズWebトップページ
Access – DoCmd.OpenReportを利用してフォームのテキストボックスに絞り込み条件を入力してレポートを開くには
（追記）2020.09.28
————————————————————————————————–
この記事で紹介している DoCmd.OpenReport メソッドについては、次のマイクロソフトの Docs.com を参照すればよいだろう。
Microsoft Docs.com「DoCmd.OpenReport メソッド (Access)」リファレンス
https://docs.microsoft.com/ja-jp/office/vba/api/access.docmd.openreport?WT.mc_id=DP-MVP-10329
————————————————————————————————–
あるデータから様々な条件でレポートを出力したいとき、レポートをひとつだけ作ってデータの中身を動的に絞り込むといい。レポートのデザインは手間がかかる作業だし、レポートが共通なら後からのデザイン変更もしやすい。レポートをひとつだけ作りデータを動的に絞り込んで表示するにはDoCmdオブジェクトのOpenReportメソッドを使用する。DoCmd.OpenReportの引数は6つあり、それぞれ次のような意味を持つが、最初の引数以外は省略可能だ。
DoCmd.OpenReport レポート名 , ビューの種類 , クエリ名 , 抽出条件 , ウィンドウモード , レポートに渡す文字列
この例では「抽出条件」と「レポートに渡す文字列」を使ってみる。用意したテーブルは「生徒」という名前にし、データは次のとおり。
＜Fig.1 : 用意した「生徒」テーブル＞
まずこのテーブルをもとにして全件表示する「生徒」レポートを作る。
＜Fig.2 : 「生徒」データを全件表示する「生徒」レポート＞
「作成」メニューの「空白のフォーム」でデータを絞り込むためのフォームを作る。フォームに「ラベル」と「テキストボックス」、そして「ボタン」を作る。ラベルには「『男』または『女』を入力する」といった説明を書いておく。テキストボックスは非連結、ボタンもウィザードを使わず何も設定しないでおく。テキストボックスの名前は「性別選択」とし、ボタンの名前は「レポート表示」とする。ボタンの表題は「レポートを開く」としよう。
＜Fig.3 : データを絞り込みレポートを表示するフォームを作る＞
「レポート表示」ボタンのプロパティを開き、クリック時のイベントプロシージャを記述しよう。Microsoft Visual Basic for Applicationエディタが起動したら次のような式を書く。
DoCmd.OpenReport “生徒”, acViewPreview, , “[性別] = ‘” & [性別選択] & “‘”, , “生徒名簿（” & [性別選択] & “）”
最初の引数、”生徒”、は表示するレポート名を示す。2番目の引数は印刷プレビューを指定しており、これがないといきなりプリンタに出力されてしまう。3番目の引数は省略。4番目の引数は絞り込む条件を記述している。データの[性別]がフォームの[性別選択]テキストボックスに入力されたものと同じものだけを絞り込む記述だ。5番目の引数は省略。最後6番目の引数で、レポートに「生徒名簿（男）」のように文字列を返すようにしている。
＜Fig.4 : Microsoft Visual Basic for Applicationエディタにコードを記述した＞
コードが記述できたらフォームを開きテキストボックスに「男」または「女」と入力して「レポートを開く」ボタンをクリックしてみよう。
＜Fig.5 : 作ったフォームを開きテキストボックスに「男」と入力して「レポートを開く」ボタンをクリックする＞
＜Fig.6 : データが絞り込まれた「生徒」レポートが表示した＞
最後にDoCmd.OpenReport式の最後の6番目の引数がレポートに反映されるようにしよう。この引数の値は、[OpenArgs]の値で表示することができる。
引数の値を表示するためレポートをデザインし、「生徒」となっているラベルをテキストボックスに変更し、テキストボックスのコントロールソースを「[OpenArgs]」とする。
＜Fig.7 : DoCmd.OpenReport式の6番目の引数を表示するためテキストボックスのレポートに反映されるようにコントロールソースを「[OpenArgs]」とする＞
レポートを保存し、フォームからデータを絞り込んでレポートを表示してみよう。
＜Fig.8 : 絞り込み条件を「男」にしてレポートを開く＞
＜Fig.9 : 条件が「男」に絞り込まれ表題のテキストボックスに引数が表示されている＞
＜Fig.10 : 絞り込み条件を「女」にしてレポートを開く＞
＜Fig.11 : 条件が「女」に絞り込まれ表題のテキストボックスに引数が表示されている＞
（追記）2020.09.28
————————————————————————————————–
この記事で紹介している DoCmd.OpenReport メソッドについては、次のマイクロソフトの Docs.com を参照すればよいだろう。
Microsoft Docs.com「DoCmd.OpenReport メソッド (Access)」リファレンス
https://docs.microsoft.com/ja-jp/office/vba/api/access.docmd.openreport?WT.mc_id=DP-MVP-10329
————————————————————————————————–
SQL Server – 「基準値」テーブルでコードを一括管理する
SQL Serverによるシステムがある程度になると、いろいろな処理をするためのコードを効率よく処理することが必要になる。学校の場合は、たとえば「教科科目コード」や「時限コード」のような時間割に関するコード、「在籍コード」や「異動コード」など生徒の在籍状態に関するコード、などだ。
これらのコードをシステム上どのようにして処理するかだが、単純に考えると「教科科目コード」や「時限コード」といった名前のテーブルを作りコードを記録することだが、それぞれのコードに対してテーブルを作ると、テーブルの数が増えてシステム全体の見通しが悪くなる。ちなみに、このようにテーブルを作る場合でも、テーブル名は「コード教科科目」や「コード時限」のように「コード」といった共通の名前を前にするとテーブル名を一覧表示したときに見通しがよくなる。もちろん「C_教科科目コード」や「C_時限コード」のようにシンボリックな文字を前につけてもよい。
システムが大きくなると、このようなコードを記録する必要も大きくなる。そこでコードを一括して「基準値」テーブルにストアして管理する方法がよい。そのためにコードはすべて二桁の文字char(2)か、または日付smalldatetimeと統一し、次のようなテーブルを作る。
——————————————————————————-
テーブル名：基準値
フィールド：
基準値管理番号 int IDENTITY(1,1) NOT NULL
基準分類 varchar(50)
基準内容 varchar(50)
基準値 char(2)
基準日 smalldatetime
表示順 int
——————————————————————————-
このテーブルで、「基準分類」によってコードの分類をし、コードの内容は「基準内容」に記述する。たとえば次のようにデータをストアする。
——————————————————————————-
基準分類 基準内容 基準値 基準日 表示順
性別 男 01 NULL 1
性別 女 02 NULL 2
異動 入学 01 NULL 1
異動 休学 02 NULL 2
異動 復学 03 NULL 3
異動 留学 04 NULL 4
異動 転学 05 NULL 5
異動 退学 06 NULL 6
異動 卒業 07 NULL 7
教科 国語 01 NULL 1
教科 地歴 02 NULL 2
教科 数学 03 NULL 3
教科 理科 04 NULL 4
教科 保健体育 05 NULL 5
教科 芸術 06 NULL 6
教科 外国語 07 NULL 7
教科 家庭 08 NULL 8
教科 情報 09 NULL 9
教科 商業 10 NULL 10
日程 前期開始日 NULL 2011/04/01 1
日程 前期終了日 NULL 2011/09/30 2
日程 後期開始日 NULL 2011/10/01 3
日程 後期終了日 NULL 2012/03/31 4
——————————————————————————-
この「基準値」テーブルを使って必要なコードを取得する。たとえば「性別」のコードを使うときは、
select 基準内容,基準値 from 基準値 where 基準分類=’性別’ order by 表示順
といったクエリを使う。これを「v_性別コード」のようなビューにしておくのもよいだろう。
「日程」データを取得するには、
select 基準内容,基準日 from 基準値 where 基準分類 = ‘日程’ order by 表示順
とする。
このように、コードを一元管理することで「あのコードを格納したのはなんというテーブルだったっけ」というように探し出す必要がなくなるのだ。
SQL Serverの文字列処理 – 職員コードから先頭のアルファベット3文字分を判定して取り除く
職員コードが6桁のコードでできているが、使われる記号が数字とアルファベットの混合であり、次の4つのパターンがあるとする。
（１）すべて数字でできてきている。 ＜例＞123456
（２）アルファベット1文字と数字5桁でできている。 ＜例＞A12345
（３）アルファベット2文字と数字4桁でできている。 ＜例＞AB1234
（４）アルファベット3文字と数字3桁でできている。 ＜例＞ABC123
このとき、「職員」テーブルにある「職員番号」フィールドについて、数字部分だけを取り出して昇順に並べたいとする。たとえばSQL Serverのビューでは「LIKE」演算子とパターン指定によって次のようにする。
———————————————————————-
SELECT 職員番号, CASE
WHEN 職員番号 LIKE ‘[a-z][a-z][a-z]%’ THEN substring(職員番号, 4, 3)
WHEN 職員番号 LIKE ‘[a-z][a-z]%’ THEN substring(職員番号, 3, 4)
WHEN 職員番号 LIKE ‘[a-z]%’ THEN substring(職員番号, 2, 5)
ELSE 職員番号
END AS 職員番号の数字部分, 職員姓名, 職員姓名ふりがな
FROM dbo.職員
———————————————————————-
結果は次のとおり。
＜Fig.1 職員コードから先頭のアルファベット3文字分を判定して取り除いた例＞
この例は、あくまでも文字列の先頭部分に連続してアルファベットが続く場合にコードを処理する例であるが、複数のデータを統合する際などにこのような文字列処理によるデータの加工が必要になる。コード体系が異なる場合、新しくコード体系を作り直す場合、いわゆる「名寄せ」が必要な場合、などだ。
システムの更新、統合には、実際にシステムを作る作業に匹敵するほど、データの整理に手間がかかる場合が少なくない。それは逆にいえば、いかに合理的で永続的なコード体系を作ることが重要であるか、ということも示している。
正しく作られたシステムでも、運用を誤り破綻する恐れをゼロにできない
いささか大上段に構えたタイトルにしたが、実際におこったことは些細なことである。しかし、今日はあらためてシステムを作ることの難しさを考えさせられた。
今日のテーマはシステム構築における純粋な技術の話ではなく、人の思考や行動といったヒューマンな側面についてである。しかしシステム開発は技術的に完全であればよいものではなく、ヒューマンな要素を十分に考慮しなければならない。また狭い意味でのコンピュータシステムだけを考えるのではなく、データ入力の帳票のあり方、作業の方法なども見直さなければならないケースもある。
私は勤務校でSQL Serverをデータベースにし、InfoPathとAccessを組み合わせた、いわゆる「OBA開発」の手法でクライアントサーバー型の「校務システム」を構築し、運用している。このシステムの基本は、単位制高校である本校の講座編成、時間割、履修登録、出欠、考査点、成績、修得単位など、教務処理を行うものである。それに加えて、通知表などを家庭に発送するための住所管理、職員の勤務時間を集計する従事時間集計、学校評価のアンケート集計など校内の情報管理を一元的に行うものへ発展させている。「OBA開発」の利点は、運用しながらシステムを改良することがやりやすいところだ。
このシステムに今年度から生徒の保健情報も扱うことにした。身長、体重などの健康記録に加えて、内科検診など検査結果も処理できなければならない。これらのデータをどのようにデータベース化するかについては、養護教諭つまり保健の先生と相談しながら設計し、実際のデータに対応できるものにした。このあたりの詳細は、また別にblogにまとめるつもりだ。
さて前置きが長くなったが、このシステムに「結核検診結果」を入力することになった。結核検診について、SQL Serverのテーブル構造は次のようになっている。
＜SQL Serverのテーブル構造＞
学籍番号 char(7)
年度 char(2)
結核検診 char(2)
結核検診詳細 varchar(50)
「結核検診」フィールドはコード管理し、00が「未受診」、01が「異常なし」、02が「異常あり」とし、所見があったときは「結核検診詳細」フィールドに自由記述することとした。
これにデータを入力するためのInfoPathフォームは次のようなものである。
＜Fig.1 結核検診結果を入力するInfoPathフォーム＞
SQL Serverで「結核検診」フィールドのデフォルト値を00にしておき、ボタンで01または02に変更できるようにする。「結核検診詳細」テキストボックスは、「結核検診」フィールドが02でなければグレーアウトし、読み取り専用になるようにしておく。これはInfoPathのテキストボックスのプロパティで「条件付き書式」で設定する。結核検診の結果が「異常あり」でなければ、詳細は入力できないようにしておくのだ。入力間違いを少なくする仕掛けだ。
さて、入力作業をしているところに、ふと、立ち寄って後ろから見ていると、なにかおかしいことに気づいた。次のような入力画面が見えたのだ。
＜Fig.2 目にとまったおかしな入力画面＞
結核検診の結果を入力しているのに、詳細が「脊柱側湾」となっている。入力担当は若い男性教員だ。どうやら養護教諭に頼まれてかわりに入力しているらしい。
「脊柱側湾」って、結核と関係ないんじゃない」「はあ。」「それは内科検診の項目だから、入力フォームを間違っていると思うよ」「はあ。でも結核検診の結果に書いてあるんです。養護教諭の先生がとりあえずそこに入力しておいて、って言ったので」
そこで入力のために使っている検診結果の表を見ると、確かに次のように書かれている。氏名はもちろん仮名である。
＜Fig.3 データ入力に使った結核検診の結果表＞
「結核検診」の記録のはずなのに、結核と関係ない「脊柱側湾」の所見が書かれている。これはおかしいのではないか。そこで養護教諭に事情を問いただした。すると、こういうことである。
結核検診はレントゲンなので、結核の疑いのあるなしだけでなく、脊柱側湾つまり脊柱が曲がっている症状もわかることが多いのだ。そこで慣習として、いわばサービスみたいなものとして、脊柱側湾の症状がレントゲンからわかれば、検査機関が所見に書いてくれるということなのだ。
養護教諭の立場からすると、少しでも多くの症状が早く発見できればよいのだろうが、データ入力上は間違いの原因になる。この生徒は、脊柱側湾であるが、結核の異常はないのである。しかし、上のような入力では、結核検診で異常が発見されたことに集計されてしまう。
何が問題なのか。まず入力に使う結果用紙の様式が問題である。脊柱側湾を結核検診で所見に書くなら、所見の欄を2つに分け、まず結核検診の結果を書き、それとは別にその他の所見を書くべきである。検査結果の用紙を見直したい。
もし検査用紙の見直しができないならば、データ入力において、やはりそのデータに関する、ある程度の知識を持っていること、データがどのように集計されるべきなのかという意味を理解していることが必要である。書式がデータ入力に即していなかったり、記入の仕方があいまいであっても、きちんと判断できる人間が入力するなら問題ない。
今回のケースは些細なこと、また結核検診という、まず全員が異常なしとなるだろう記録であったので、このまま間違い入力をしてしまっても、後で間違いが発見されただろう。しかしこのようなケースが他の例でも起こりうることであり、いかに正しく設計されたシステムであっても、間違ったデータ入力が見過ごされて信頼性のないデータで汚染され、全体として機能しないシステムに陥る危険を垣間見た気がする。
InfoPathとSQL Serverで「学校評価」の集計をする
年度末に近づき、多くの学校では一年間のまとめをする時期になったと思う。本校でもこの一年の取り組みのまとめとして、学校評価を行うことになった。
本校の学校評価では、いくつかの大項目の下に、小項目として50弱の項目を目標として設定した。これらの小項目に対して、それぞれ教員が1から4までの評価を与えることにしている。このようなアンケート式のデータ集計は、InfoPathとSQL Serverが最も得意とするところである。しかしテーブル構造や集計については、少し工夫を要するところがある。
InfoPathフォームはSQL Serverのテーブルに対して1対1の対応が得意である。そこでやや強引ではあるが、テーブル構造として次のようなものを作成した。
———————-（ここからテーブル作成SQL）———————-
CREATE TABLE gakkohyoka_t_hyoka (
[shokuinbango] [char] (6) ,
[inputtime] [datetime] ,
[01] [char] (2) ,
[02] [char] (2) ,
[03] [char] (2) ,
[04] [char] (2) ,
[05] [char] (2) ,
[06] [char] (2) ,
[07] [char] (2) ,
[08] [char] (2) ,
[09] [char] (2) ,
[10] [char] (2) ,
[11] [char] (2) ,
[12] [char] (2) ,
[13] [char] (2) ,
[14] [char] (2) ,
[15] [char] (2) ,
[16] [char] (2) ,
[17] [char] (2) ,
[18] [char] (2) ,
[19] [char] (2) ,
[20] [char] (2) ,
[21] [char] (2) ,
[22] [char] (2) ,
[23] [char] (2) ,
[24] [char] (2) ,
[25] [char] (2) ,
[26] [char] (2) ,
[27] [char] (2) ,
[28] [char] (2) ,
[29] [char] (2) ,
[30] [char] (2) ,
[31] [char] (2) ,
[32] [char] (2) ,
[33] [char] (2) ,
[34] [char] (2) ,
[35] [char] (2) ,
[36] [char] (2) ,
[37] [char] (2) ,
[38] [char] (2) ,
[39] [char] (2) ,
[40] [char] (2) ,
[41] [char] (2) ,
[42] [char] (2) ,
[43] [char] (2) ,
[44] [char] (2) ,
[45] [char] (2) ,
[46] [char] (2) ,
[47] [char] (2) ,
[48] [char] (2) ,
[49] [char] (2) ,
[50] [char] (2) ,
[hyokaiken] [varchar] (2000) ,
[unneiiken] [varchar] (2000)
)
———————-（ここまでテーブル作成SQL）———————-
フィールド[shokuinbango]に職員番号を生成しておき、[inputtime]にInfoPathフォームで入力したときの日付を入れる、フィールド[01]から[50]までにそれぞれの項目の1から4評価をストアし、最後の[hyokaiken]と[unneiiken]には自由記述で文を入力できるようにする。このようなテーブルにすると、InfoPathフォームは作りやすい。
しかし問題は集計の方法だ。これがExcelの表ならば、各列の最後にcountif()文を使って各評価の個数を集計するところだが、SQL Serverではそうはいかない。
そこで、まず、このテーブルの値を正規化するビューを作る。次のようなビューだ。
———————-（ここからビューのSQL）———————-
SELECT ’01’ AS 項目, [01] AS 評価, COUNT(01) AS 回答数
FROM gakkohyoka_t_hyoka
GROUP BY [01]
UNION
SELECT ’02’ AS 項目, [02] AS 評価, COUNT(02) AS 回答数
FROM gakkohyoka_t_hyoka AS gakkohyoka_t_hyoka_02
GROUP BY [02]
UNION
SELECT ’03’ AS 項目, [03] AS 評価, COUNT(03) AS 回答数
FROM gakkohyoka_t_hyoka AS gakkohyoka_t_hyoka_03
GROUP BY [03]
UNION
SELECT ’04’ AS 項目, [04] AS 評価, COUNT(04) AS 回答数
FROM gakkohyoka_t_hyoka AS gakkohyoka_t_hyoka_04
GROUP BY [04]
UNION
SELECT ’05’ AS 項目, [05] AS 評価, COUNT(05) AS 回答数
FROM gakkohyoka_t_hyoka AS gakkohyoka_t_hyoka_05
GROUP BY [05]
UNION
SELECT ’06’ AS 項目, [06] AS 評価, COUNT(06) AS 回答数
FROM gakkohyoka_t_hyoka AS gakkohyoka_t_hyoka_06
GROUP BY [06]
UNION
SELECT ’07’ AS 項目, [07] AS 評価, COUNT(07) AS 回答数
FROM gakkohyoka_t_hyoka AS gakkohyoka_t_hyoka_07
GROUP BY [07]
UNION
SELECT ’08’ AS 項目, [08] AS 評価, COUNT(08) AS 回答数
FROM gakkohyoka_t_hyoka AS gakkohyoka_t_hyoka_08
GROUP BY [08]
UNION
SELECT ’09’ AS 項目, [09] AS 評価, COUNT(09) AS 回答数
FROM gakkohyoka_t_hyoka AS gakkohyoka_t_hyoka_09
GROUP BY [09]
UNION
SELECT ’10’ AS 項目, [10] AS 評価, COUNT(10) AS 回答数
FROM gakkohyoka_t_hyoka AS gakkohyoka_t_hyoka_10
GROUP BY [10]
UNION
SELECT ’11’ AS 項目, [11] AS 評価, COUNT(11) AS 回答数
FROM gakkohyoka_t_hyoka AS gakkohyoka_t_hyoka_11
GROUP BY [11]
UNION
SELECT ’12’ AS 項目, [12] AS 評価, COUNT(12) AS 回答数
FROM gakkohyoka_t_hyoka AS gakkohyoka_t_hyoka_12
GROUP BY [12]
UNION
SELECT ’13’ AS 項目, [13] AS 評価, COUNT(13) AS 回答数
FROM gakkohyoka_t_hyoka AS gakkohyoka_t_hyoka_13
GROUP BY [13]
UNION
SELECT ’14’ AS 項目, [14] AS 評価, COUNT(14) AS 回答数
FROM gakkohyoka_t_hyoka AS gakkohyoka_t_hyoka_14
GROUP BY [14]
UNION
SELECT ’15’ AS 項目, [15] AS 評価, COUNT(15) AS 回答数
FROM gakkohyoka_t_hyoka AS gakkohyoka_t_hyoka_15
GROUP BY [15]
UNION
SELECT ’16’ AS 項目, [16] AS 評価, COUNT(16) AS 回答数
FROM gakkohyoka_t_hyoka AS gakkohyoka_t_hyoka_16
GROUP BY [16]
UNION
SELECT ’17’ AS 項目, [17] AS 評価, COUNT(17) AS 回答数
FROM gakkohyoka_t_hyoka AS gakkohyoka_t_hyoka_17
GROUP BY [17]
UNION
SELECT ’18’ AS 項目, [18] AS 評価, COUNT(18) AS 回答数
FROM gakkohyoka_t_hyoka AS gakkohyoka_t_hyoka_18
GROUP BY [18]
UNION
SELECT ’19’ AS 項目, [19] AS 評価, COUNT(19) AS 回答数
FROM gakkohyoka_t_hyoka AS gakkohyoka_t_hyoka_19
GROUP BY [19]
UNION
SELECT ’20’ AS 項目, [20] AS 評価, COUNT(20) AS 回答数
FROM gakkohyoka_t_hyoka AS gakkohyoka_t_hyoka_20
GROUP BY [20]
UNION
SELECT ’21’ AS 項目, [21] AS 評価, COUNT(21) AS 回答数
FROM gakkohyoka_t_hyoka AS gakkohyoka_t_hyoka_21
GROUP BY [21]
UNION
SELECT ’22’ AS 項目, [22] AS 評価, COUNT(22) AS 回答数
FROM gakkohyoka_t_hyoka AS gakkohyoka_t_hyoka_22
GROUP BY [22]
UNION
SELECT ’23’ AS 項目, [23] AS 評価, COUNT(23) AS 回答数
FROM gakkohyoka_t_hyoka AS gakkohyoka_t_hyoka_23
GROUP BY [23]
UNION
SELECT ’24’ AS 項目, [24] AS 評価, COUNT(24) AS 回答数
FROM gakkohyoka_t_hyoka AS gakkohyoka_t_hyoka_24
GROUP BY [24]
UNION
SELECT ’25’ AS 項目, [25] AS 評価, COUNT(25) AS 回答数
FROM gakkohyoka_t_hyoka AS gakkohyoka_t_hyoka_25
GROUP BY [25]
UNION
SELECT ’26’ AS 項目, [26] AS 評価, COUNT(26) AS 回答数
FROM gakkohyoka_t_hyoka AS gakkohyoka_t_hyoka_26
GROUP BY [26]
UNION
SELECT ’27’ AS 項目, [27] AS 評価, COUNT(27) AS 回答数
FROM gakkohyoka_t_hyoka AS gakkohyoka_t_hyoka_27
GROUP BY [27]
UNION
SELECT ’28’ AS 項目, [28] AS 評価, COUNT(28) AS 回答数
FROM gakkohyoka_t_hyoka AS gakkohyoka_t_hyoka_28
GROUP BY [28]
UNION
SELECT ’29’ AS 項目, [29] AS 評価, COUNT(29) AS 回答数
FROM gakkohyoka_t_hyoka AS gakkohyoka_t_hyoka_29
GROUP BY [29]
UNION
SELECT ’30’ AS 項目, [30] AS 評価, COUNT(30) AS 回答数
FROM gakkohyoka_t_hyoka AS gakkohyoka_t_hyoka_30
GROUP BY [30]
UNION
SELECT ’31’ AS 項目, [31] AS 評価, COUNT(31) AS 回答数
FROM gakkohyoka_t_hyoka AS gakkohyoka_t_hyoka_31
GROUP BY [31]
UNION
SELECT ’32’ AS 項目, [32] AS 評価, COUNT(32) AS 回答数
FROM gakkohyoka_t_hyoka AS gakkohyoka_t_hyoka_32
GROUP BY [32]
UNION
SELECT ’33’ AS 項目, [33] AS 評価, COUNT(33) AS 回答数
FROM gakkohyoka_t_hyoka AS gakkohyoka_t_hyoka_33
GROUP BY [33]
UNION
SELECT ’34’ AS 項目, [34] AS 評価, COUNT(34) AS 回答数
FROM gakkohyoka_t_hyoka AS gakkohyoka_t_hyoka_34
GROUP BY [34]
UNION
SELECT ’35’ AS 項目, [35] AS 評価, COUNT(35) AS 回答数
FROM gakkohyoka_t_hyoka AS gakkohyoka_t_hyoka_35
GROUP BY [35]
UNION
SELECT ’36’ AS 項目, [36] AS 評価, COUNT(36) AS 回答数
FROM gakkohyoka_t_hyoka AS gakkohyoka_t_hyoka_36
GROUP BY [36]
UNION
SELECT ’37’ AS 項目, [37] AS 評価, COUNT(37) AS 回答数
FROM gakkohyoka_t_hyoka AS gakkohyoka_t_hyoka_37
GROUP BY [37]
UNION
SELECT ’38’ AS 項目, [38] AS 評価, COUNT(38) AS 回答数
FROM gakkohyoka_t_hyoka AS gakkohyoka_t_hyoka_38
GROUP BY [38]
UNION
SELECT ’39’ AS 項目, [39] AS 評価, COUNT(39) AS 回答数
FROM gakkohyoka_t_hyoka AS gakkohyoka_t_hyoka_39
GROUP BY [39]
UNION
SELECT ’40’ AS 項目, [40] AS 評価, COUNT(40) AS 回答数
FROM gakkohyoka_t_hyoka AS gakkohyoka_t_hyoka_40
GROUP BY [40]
UNION
SELECT ’41’ AS 項目, [41] AS 評価, COUNT(41) AS 回答数
FROM gakkohyoka_t_hyoka AS gakkohyoka_t_hyoka_41
GROUP BY [41]
UNION
SELECT ’42’ AS 項目, [42] AS 評価, COUNT(42) AS 回答数
FROM gakkohyoka_t_hyoka AS gakkohyoka_t_hyoka_42
GROUP BY [42]
UNION
SELECT ’43’ AS 項目, [43] AS 評価, COUNT(43) AS 回答数
FROM gakkohyoka_t_hyoka AS gakkohyoka_t_hyoka_43
GROUP BY [43]
UNION
SELECT ’44’ AS 項目, [44] AS 評価, COUNT(44) AS 回答数
FROM gakkohyoka_t_hyoka AS gakkohyoka_t_hyoka_44
GROUP BY [44]
UNION
SELECT ’45’ AS 項目, [45] AS 評価, COUNT(45) AS 回答数
FROM gakkohyoka_t_hyoka AS gakkohyoka_t_hyoka_45
GROUP BY [45]
UNION
SELECT ’46’ AS 項目, [46] AS 評価, COUNT(46) AS 回答数
FROM gakkohyoka_t_hyoka AS gakkohyoka_t_hyoka_46
GROUP BY [46]
UNION
SELECT ’47’ AS 項目, [47] AS 評価, COUNT(47) AS 回答数
FROM gakkohyoka_t_hyoka AS gakkohyoka_t_hyoka_47
GROUP BY [47]
UNION
SELECT ’48’ AS 項目, [48] AS 評価, COUNT(48) AS 回答数
FROM gakkohyoka_t_hyoka AS gakkohyoka_t_hyoka_48
GROUP BY [48]
UNION
SELECT ’49’ AS 項目, [49] AS 評価, COUNT(49) AS 回答数
FROM gakkohyoka_t_hyoka AS gakkohyoka_t_hyoka_49
GROUP BY [49]
UNION
SELECT ’50’ AS 項目, [50] AS 評価, COUNT(50) AS 回答数
FROM gakkohyoka_t_hyoka AS gakkohyoka_t_hyoka_50
GROUP BY [50]
———————-（ここまでビューのSQL）———————-
ひたすら長いSQL文だが、構造は簡単なUNIONクエリの繰り返しなので、機械的に作ることができるはずだ。このSQL文によって、「項目」、「評価」、「回答数」というビューが得られる。結果はSQL Server Management Studioの編集画面を見てもらうと一目瞭然だろう。
＜Fig.1 : UNIONクエリによるデータの途中集計＞
この後はPIVOTによる集計を行う。クエリは以下のとおり。
———————-（ここからPIVOTのSQL）———————-
SELECT 項目
,SUM(CASE WHEN 評価 = ‘Ａ’ THEN 回答数 ELSE 0 END) AS 評価Ａ,
,SUM(CASE WHEN 評価 = ‘Ｂ’ THEN 回答数 ELSE 0 END) AS 評価Ｂ,
,SUM(CASE WHEN 評価 = ‘Ｃ’ THEN 回答数 ELSE 0 END) AS 評価Ｃ,
,SUM(CASE WHEN 評価 = ‘Ｄ’ THEN 回答数 ELSE 0 END) AS 評価Ｄ
FROM v_gakkohyoka_shu
GROUP BY 項目
———————-（ここまでPIVOTのSQL）———————-
これもSQL Server Management Studioの編集画面を見てもらうと直感的にわかるだろう。
＜Fig.2 : PIVOT集計のビュー＞
あとはこのビューに対して、画面に表示する単純なInfoPathフォームを作るだけだ。こうしておけば、アンケートの入力が終わった時点で、即集計結果を出すことができる。
SQL ServerのようなデータベースとExcelを単純に比べることは意味がないが、このような集計をするにはExcelは得意であるが、SQL Serverでやろうとすると手間がかかることは間違いない。しかし一度作っておけば、毎年同じ処理をこのテーブルとビューでできるので、省力化できる。項目がいくら増えても、選択肢が増えても、職員数が変わっても、ほとんど変更することなく集計ができる。
SQL Server 2000のデータをSQL Server 2005にインポートするにはAccess Projectを使おう
つい先日のことだが、古いサーバーでSQL Server 2000で管理されていたデータを、新しいサーバーのSQL Server 2005へ移行する作業を行った。このとき、データ移行でエラーとなり、試行錯誤した。
データ移行には、DTSを使った。DTS（Data Transformation Services）である。旧サーバーのSQL Server 2000からDTSを使ってデータをExcel形式で書き出し、SQL Server 2005のDTSでインポートしようとした。このとき「エラー0xc02020f6:データフロータスク : 列****では、Unicod形式の文字列データ型とUnicode以外の形式の文字列データ型を変換できません」といったエラーが出てインポートができない現象がおきた。
＜Fig.1 : DTSのインポートウィザードが停止した状態＞
＜Fig.2 : DTS停止のレポート表示＞
もとのSQL Server 2000のテーブルは、単純なvarchar型のテーブルであり、なんら文字コードに特殊な仕掛けをしていない。さて困ったところで、DTSでデータを書き出すためにExcel形式はやめてCSVにしたり文字コードを決め打ちしたりいろいろとやっても解決できない。かなり行き詰まった状況になり、焦燥感が充満した。
Excel形式でもCSVでもだめだったので、データをAccessファイルに書き出すことを考えた。しかしSQL Server 2000のDTSで書き出したAccessファイルをSQL Server 2005のDTSで読み込んでもエラーで止まってしまう。
しばらく頭を冷やし、何か方法がないか、と考え、Access Projectを使うことを思いついた。つまり、こうするのだ。
１．移転先のSQL Server 2005に対してAccess Projectを作成する。
２．そのAccess Projectでデータのインポートを実行する。
３．インポートするファイルは、SQL Server 2000のDTSで書き出したExcelファイルでもCSVファイルでもよい。
４．念のため新しいテーブルにインポートし、その後SQL文でインポートしたいテーブルにINSERTする。
上記のような手順で、うまくデータをインポートすることができた。つまり、データを書き出すにはSQL ServerのDTSでよいが、インポートするにはDTSよりもAccess Projectのインポートウィザードを使った方がよかった、ということだ。これがなぜなのか、といった追求までしていないが、DTSでうまくいかない場合はAccessを間に介することで解決できることもある、ということはTipsとして知っておいていいだろう。
