浅野直樹の学習日記

この画面は、簡易表示です

2016 / 1月

LibreOfficeのBaseで団体の名簿と会計を管理する(後編)

LibreOfficeのBaseで団体の名簿と会計を管理する(中編) – 浅野直樹の学習日記では残高確認(現金出納帳など)までやりました。あとは「会費納入」テーブルをもとにして「会員名簿」の最終会費納入のデータを更新することと、「会員名簿」テーブルから発送用の宛名ラベルを作成することの2つをやります。

 

1.会費納入データの更新

SQLでデータを更新するためにはUPDATE文を用います。詳しくはOracle SQL (データの更新 :UPDATE文の基礎)をご覧ください。

 

このリンク先を熟読して、最終的には以下のようなSQL文を作りました。これをBaseのテーブルやクエリーを選ぶ画面で「ツール」→「SQL」で実行してください。

UPDATE 会員名簿
SET 最終会費納入 = (
NVL(
(
SELECT MAX(終了) FROM 会費納入
WHERE 会費納入.会員名 = 会員名簿.名前
GROUP BY 会費納入.会員名
),
会員名簿.最終会費納入
)
);

最も外側は、「会員名簿」テーブルの最終会費納入という項目を更新するというものです。どの値に更新するのかというと、「会費納入」テーブルを会員名でグループ化して、その中で終了という項目の最大値です。ただ、その最大値がない場合にNULLになるのを防ぐために、NULLになりそうな場合は元の値と同じ値に更新するというように、NVL関数を用いています。

 

上の更新SQLは名前の一致に頼っているので、名前が違っているとうまく働きません。そのようなことがないかどうかを確かめるために、以下のクエリーを実行しておくとよいでしょう。

SELECT 名前, 終了, 最終会費納入 FROM 会費納入 LEFT JOIN 会員名簿
ON 会費納入.会員名 = 会員名簿.名前

LEFT JOINによりON以下の条件に合わないものも表示されるので、変な空白があれば何かがおかしいと気づきます。

 

2.宛名ラベルの作成

(1)送付先全体のクエリーを作成する

会報や総会の案内を会員やその他の送付先に送るときには宛名ラベルが活躍します。そのために別のソフトを用いてもよいのですが、どうせなら間違いをなくすためにもデータをBaseで一元的に管理して、そこから印刷してしまいましょう。

 

「会員名簿」とは別に「会員以外送付先」テーブルを作りましょう。「会員名簿」テーブルの項目で必要のないものを減らす感じで作ればよいです。

 

そして「会員名簿」と「会員以外送付先」の2つのテーブルから、在籍している人(種別が0ではない人)を選んで、会員を五十音順に並べてから会員以外を五十音順に並べます。これがそのためのSQL文です。

 

SELECT 0 テーブルの順番, 名前, フリガナ, 郵便番号, 住所1, 住所2 FROM 会員名簿 WHERE ( 種別 != 0)
UNION ALL
SELECT 1 テーブルの順番, 名前, フリガナ, 郵便番号, 住所1, 住所2 FROM 会員以外送付先 WHERE ( 種別 != 0)
ORDER BY テーブルの順番, フリガナ

 

テーブルの順番に並べるために、「会員名簿」テーブルには0、「会員以外送付先」には1という数字を割り振っています。このクエリーを「送付先全体」とでも名前をつけて保存しておきましょう。

 

(2)ラベルの差し込み印刷をする

Baseにはラベルを作成する機能が備わっているので、比較的簡単にできます。

 

テーブルやクエリーを選ぶBaseの初期画面から、「ファイル」→「新規作成」→「ラベル」を選びます。今使っているデータベースを選び、先ほどの「送付先全体」というクエリーを選んで、郵便番号、住所1、住所2、名前の必要項目を1つずつ左側の画面に追加していきます。

 

「書式」タブを選んで、お手元のラベル用紙の通りに幅や余白などを設定します。「オプション」タブでページ全体というところを選んで、「新規ドキュメント」を押すと、ラベル用の差し込み印刷ができているはずです。

 

後は大きさや配置を好みに調整するだけですが、ここでコピーアンドペーストを使うと差し込み印刷が崩れるので注意が必要です。

 

このBaseのテーブルやクエリーからの差し込み印刷を活用すれば、会費をいつまで納入しているかのお知らせなども自動で作ることができます。

 

 

3回に渡る長い記事になりましたが、これで効率的かつ間違いが少なく団体の名簿や会計を管理できるようになりました。

 

 

 



LibreOfficeのBaseで団体の名簿と会計を管理する(中編)

LibreOfficeのBaseで団体の名簿と会計を管理する(前編) – 浅野直樹の学習日記では、なぜLibreOfficeのBaseを使うのかというところから、必要なテーブルの作成までやりました。この記事ではExcel等のすでにあるデータのインポートと、クエリーを使用した残高確認(現金出納帳など)を扱います。

 

1.データのインポート

すでにExcelやCalcにあるデータをBaseに移行したいということがよくあると思います。もちろんそれは可能です。Base でのデータのインポート/エクスポート – LibreOffice Helpにそのやり方が書いてあるのですが、わかりにくかったので、詳しく説明します。

 

(1)準備

ExcelやCalcで作りたいデータベースと同じ項目のシートを作ります。私が作った「会員名簿」なら次のような感じです。

07

名前や住所はプライバシーの都合で空欄にしていますが、本番では埋めてください。

 

ここで注意点が2つあります。1つは日付型のデータをインポートする際には、 図のように2015-12-31のような形にしないとうまくいきません。2015/12/31といった形だとエラーになります。もう1つは、見出しを除いたデータの1行目は全て埋めておいたほうがよいです。

 

(2-a)テーブルを先に作ってからインポートする

インポート元のExcelやCalcのファイルのシート上で、インポートしたい部分を見出し行を含めて選択してコピーします。

 

次に、Baseのテーブル選択画面からインポート先のテーブルを選択し、右クリックから「貼り付け」を選びます。

 

「データの添付」が選択され、「第1行目をカラム名として使用」にチェックが入っていることを確認し、「次へ」を押します。

 

ソーステーブルの全てにチェックが入っていることを確認し、「完了」をクリックします。

 

(2-b)テーブルを作らずいきなりインポートする

インポート元のExcelやCalcのファイルのシート上で、インポートしたい部分を見出し行を含めて選択してコピーします。

 

次に、Baseのテーブル選択画面からどのテーブルでもよいので選択して、右クリックから「貼り付け」を選びます。

 

「データの添付」ではなく「定義とデータ」を選択して、「第1行目をカラム名として使用」にチェックが入っていることを確認し、テーブル名を適当に変更して「次へ」を押します。

 

「>>」を押して既存の列を全て右側に移し、「次へ」をクリックします。

 

それぞれのフィールドにふさわしいフィールドの種類を指定していって、最後に「完了」をクリックします。項目名のところで右クリックをするとプライマリーキーを設定できます。

 

2.クエリー

LibreOfficeのBaseではデザインやウィザードからクエリーを作ることができますが、複雑なことをするためにはSQL表示によることになるので、最初からSQLでやります。

(1)テーブルの内容をそのまま表示する

まずは「その他収入」テーブルの内容をそのまま表示してみましょう。

SELECT * FROM その他収入

これは「その他収入」テーブルから全てを選んでくれという意味です。

 

納入日付順に並べるなら、次のように書きます。

SELECT * FROM その他収入 ORDER BY 納入日付

 

特定の列だけ選ぶなら、次にように書きます。

SELECT 納入日付, 金額 FROM その他収入

 

条件に合う列だけを抽出するなら、次のように書きます。

SELECT * FROM その他収入 WHERE 納入方法 = 1

 

(2)共通項目でテーブルを横に結合する(INNERJOIN ~ ON …)

納入方法で、1は現金、2は当座預金、3は普通預金というように番号を割り振っているとして、その番号のままだと他の人に見せるときに不便です。そこで、その番号の代わりにきちんとした名称を表示させるようにしましょう。

 

まず、番号と名称を対応させる「納入支出方法」テーブルを作ります。

フィールド名 フィールドタイプ
納入支出方法ID 整数[INTEGER]
納入支出方法番号 整数[INTEGER]
納入支出方法名称 テキスト[VARCHAR]

08

 

そしてそのテーブルに、納入支出方法番号と納入支出方法名称を対応させたデータを入力します。

 

それから次のようなSQL文をクエリーで実行すると、番号が共通する部分で2つの表が結合されます。

SELECT * FROM その他収入 INNER JOIN 納入支出方法 ON その他収入.納入方法 = 納入支出方法.納入支出方法番号

 

番号を表示させないようにするには、「*」のところを必要な列の名前だけにすればよいです。

SELECT 納入日付, 納入支出方法名称, 科目, 摘要, 金額, 備考 FROM その他収入 INNER JOIN 納入支出方法 ON その他収入.納入方法 = 納入支出方法.納入支出方法番号

 

「納入支出方法名称」という名前が長くて別名にしたければ、asを用いて別名にすればよいです。

SELECT 納入日付, 納入支出方法名称 AS 方法, 科目, 摘要, 金額, 備考 FROM その他収入 INNER JOIN 納入支出方法 ON その他収入.納入方法 = 納入支出方法.納入支出方法番号

こうしてクエリーが完成したら、「その他収入完成」のように名前をつけてクエリーを保存しておくと後々便利です。

 

科目についても同様のやり方で番号ではなく名称の表示にすることができます。

 

(3)単純にテーブルを縦に結合する(UNION)

「会費納入」テーブルと「その他収入」テーブルを縦に結合して全収入を一覧しましょう。「SQLコマンドを直接実行」というボタンを押さないとうまく動かないかもしれません。

SELECT 納入方法, 納入日付, 会員名, 納入合計, 備考 FROM 会費納入
UNION ALL
SELECT 納入方法, 納入日付, 摘要, 金額, 備考 FROM その他収入

 

このように、SELECTの後の選ぶ項目の数を一致させるのがコツです。ここでの使途からすればUNION ALLで重複するデータも表示させたほうがよいです。

 

 

(4)残高確認(現金出納帳など)

さていよいよ本題の残高確認(現金出納帳など)です。

 

まずは「現金出納帳準備」クエリーを作ります。以下の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では苦労しました。ともかくできたので、これで実用に耐えます。

 

 




top