LibreOfficeのBaseで団体の名簿と会計を管理する（前編） – 浅野直樹の学習日記では、なぜLibreOfficeのBaseを使うのかというところから、必要なテーブルの作成までやりました。この記事ではExcel等のすでにあるデータのインポートと、クエリーを使用した残高確認（現金出納帳など）を扱います。
１．データのインポート
すでにExcelやCalcにあるデータをBaseに移行したいということがよくあると思います。もちろんそれは可能です。Base でのデータのインポート/エクスポート – LibreOffice Helpにそのやり方が書いてあるのですが、わかりにくかったので、詳しく説明します。
（１）準備
ExcelやCalcで作りたいデータベースと同じ項目のシートを作ります。私が作った「会員名簿」なら次のような感じです。
名前や住所はプライバシーの都合で空欄にしていますが、本番では埋めてください。
ここで注意点が２つあります。１つは日付型のデータをインポートする際には、 図のように2015-12-31のような形にしないとうまくいきません。2015/12/31といった形だとエラーになります。もう１つは、見出しを除いたデータの１行目は全て埋めておいたほうがよいです。
（２-a）テーブルを先に作ってからインポートする
インポート元のExcelやCalcのファイルのシート上で、インポートしたい部分を見出し行を含めて選択してコピーします。
次に、Baseのテーブル選択画面からインポート先のテーブルを選択し、右クリックから「貼り付け」を選びます。
「データの添付」が選択され、「第１行目をカラム名として使用」にチェックが入っていることを確認し、「次へ」を押します。
ソーステーブルの全てにチェックが入っていることを確認し、「完了」をクリックします。
（２-b）テーブルを作らずいきなりインポートする
インポート元のExcelやCalcのファイルのシート上で、インポートしたい部分を見出し行を含めて選択してコピーします。
次に、Baseのテーブル選択画面からどのテーブルでもよいので選択して、右クリックから「貼り付け」を選びます。
「データの添付」ではなく「定義とデータ」を選択して、「第１行目をカラム名として使用」にチェックが入っていることを確認し、テーブル名を適当に変更して「次へ」を押します。
「>>」を押して既存の列を全て右側に移し、「次へ」をクリックします。
それぞれのフィールドにふさわしいフィールドの種類を指定していって、最後に「完了」をクリックします。項目名のところで右クリックをするとプライマリーキーを設定できます。
２．クエリー
LibreOfficeのBaseではデザインやウィザードからクエリーを作ることができますが、複雑なことをするためにはSQL表示によることになるので、最初からSQLでやります。
（１）テーブルの内容をそのまま表示する
まずは「その他収入」テーブルの内容をそのまま表示してみましょう。
SELECT * FROM その他収入
これは「その他収入」テーブルから全てを選んでくれという意味です。
納入日付順に並べるなら、次のように書きます。
SELECT * FROM その他収入 ORDER BY 納入日付
特定の列だけ選ぶなら、次にように書きます。
SELECT 納入日付, 金額 FROM その他収入
条件に合う列だけを抽出するなら、次のように書きます。
SELECT * FROM その他収入 WHERE 納入方法 = 1
（２）共通項目でテーブルを横に結合する（INNERJOIN ~ ON …）
納入方法で、1は現金、2は当座預金、3は普通預金というように番号を割り振っているとして、その番号のままだと他の人に見せるときに不便です。そこで、その番号の代わりにきちんとした名称を表示させるようにしましょう。
まず、番号と名称を対応させる「納入支出方法」テーブルを作ります。
| フィールド名 | フィールドタイプ |
| 納入支出方法ID | 整数[INTEGER] |
| 納入支出方法番号 | 整数[INTEGER] |
| 納入支出方法名称 | テキスト[VARCHAR] |
そしてそのテーブルに、納入支出方法番号と納入支出方法名称を対応させたデータを入力します。
それから次のようなSQL文をクエリーで実行すると、番号が共通する部分で２つの表が結合されます。
SELECT * FROM その他収入 INNER JOIN 納入支出方法 ON その他収入.納入方法 = 納入支出方法.納入支出方法番号
番号を表示させないようにするには、「*」のところを必要な列の名前だけにすればよいです。
SELECT 納入日付, 納入支出方法名称, 科目, 摘要, 金額, 備考 FROM その他収入 INNER JOIN 納入支出方法 ON その他収入.納入方法 = 納入支出方法.納入支出方法番号
「納入支出方法名称」という名前が長くて別名にしたければ、asを用いて別名にすればよいです。
SELECT 納入日付, 納入支出方法名称 AS 方法, 科目, 摘要, 金額, 備考 FROM その他収入 INNER JOIN 納入支出方法 ON その他収入.納入方法 = 納入支出方法.納入支出方法番号
こうしてクエリーが完成したら、「その他収入完成」のように名前をつけてクエリーを保存しておくと後々便利です。
科目についても同様のやり方で番号ではなく名称の表示にすることができます。
（３）単純にテーブルを縦に結合する（UNION）
「会費納入」テーブルと「その他収入」テーブルを縦に結合して全収入を一覧しましょう。「SQLコマンドを直接実行」というボタンを押さないとうまく動かないかもしれません。
SELECT 納入方法, 納入日付, 会員名, 納入合計, 備考 FROM 会費納入
UNION ALL
SELECT 納入方法, 納入日付, 摘要, 金額, 備考 FROM その他収入
このように、SELECTの後の選ぶ項目の数を一致させるのがコツです。ここでの使途からすればUNION ALLで重複するデータも表示させたほうがよいです。
（４）残高確認（現金出納帳など）
さていよいよ本題の残高確認（現金出納帳など）です。
まずは「現金出納帳準備」クエリーを作ります。以下のSQL文です。
SELECT 納入日付 AS 日付, 1 AS 科目, 会員名 AS 摘要, 納入合計 AS 収入, 0 AS 支出 FROM 会費納入 WHERE ( 納入方法 = 1)
UNION ALL
SELECT 納入日付 AS 日付, 科目 AS 科目, 摘要 AS 摘要, 金額 AS 収入, 0 AS 支出 FROM その他収入 WHERE ( 納入方法 = 1)
UNION ALL
SELECT 支出日付 AS 日付, 科目 AS 科目, 摘要 AS 摘要, 0 AS 収入, 金額 AS 支出 FROM 支出 WHERE ( 支出方法 = 1)
大きくやりたいのは、納入方法が1（現金）のすべてのデータに関して、「会費納入」、「その他収入」、「支出」の各テーブルをUNION ALLで単純に縦に結合するということです。各テーブルで項目名が異なるところもあるので、ASで別名をつけています。現金出納帳には収入と支出の両方の項目が必要なので、収入系のテーブルでは0というデータを支出に、支出のテーブルでは0というデータを収入に入れています。このクエリーを「現金出納帳準備」という名前で保存します。
次に、新しく次のようなクエリーを作ります。テーブルではなくクエリーから抽出しているので、「SQLコマンドを直接実行」をoffにしないと動かないかもしれません。
SELECT 日付, 科目, 摘要, 収入, 支出, ( ( SELECT SUM( 収入 ) FROM 現金出納帳準備 AS t2 WHERE ( t2.日付 <= t1.日付 ) ) – ( SELECT SUM( 支出 ) FROM 現金出納帳準備 AS t2 WHERE ( t2.日付 <= t1.日付 ) ) ) AS 残高 FROM 現金出納帳準備 AS t1 ORDER BY 日付
「残高」という別名をつけている項目がどう見てもややこしいですね。このクエリー全体では、「現金出納帳準備」という先ほどのクエリーから抽出しています。その抽出元のクエリーにt1という別名をつけ、残高を計算するために使う同じ「現金出納帳準備」クエリーにはt2という別名をつけて区別しています。各行ごとに、日付がその行のデータの日付と同じか小さい部分の収入を全部合計して、そこから支出を全部合計したものを引いて残高を計算しています。
当座預金などについても同様に残高計算ができます。
その都度残高が一致するかを確認するのは会計の基本です。Excel等では簡単に残高を計算できるのですが、Baseでは苦労しました。ともかくできたので、これで実用に耐えます。
[…] LibreOfficeのBaseで団体の名簿と会計を管理する（中編） – 浅野直樹の学習日記では残高確認（現金出納帳など）までやりました。あとは「会費納入」テーブルをもとにして「会員名簿」 […]