店舗ごとの収支集計表を作る①

店舗や部門ごとの収益の集計表を作ります。

一見難しそうに感じるかもしれませんが、やることは至って単純です。
売上と経費を店舗ごとに入力すれば、あとの展開はAccessの仕組みで回します。

経費の入力はこんなフォームから入力していきます。

Accessから入力しなくても、Excelだったり会計ソフトから掃き出したCSVファイル等があれば
それを形式を整えて、コピペでAccessのテーブルに取り込むことも可能です。
データの入力の仕方は一番手っ取り早い方法でやります。

フォームで打つよりもExcelでまとめてデータを作って流し込む方が楽かな。

データを登録していくと、こんな集計表を作ることができます。
店舗ごと・月ごとの収益の一覧表

経費の店舗ごとの比較表

多店舗・他部門で事業をされている方は、会計ソフトだったり、それ以外のソフトで管理している
ことと思いますが、Accessでも当然作れます。

一度入力したデータを二度入力するのは不効率なので、
①会計ソフトのデータを出力して、Accessに流し込む
②Accessに入力して、それを出力して、会計ソフトに流し込む
①と②は流れが逆なだけなのですが、極力手間が増えない形で運用したいところです。

Accessで管理するメリットは、データを詳細までたどることができます。

たとえば、広告宣伝費が50万円とあったときに、どこにいくら払ったのか簡単に
辿ることができます。その方法で、有効に経費が使われているかどうか、さっと
確認する仕組みが作れます。

今後その作り方を記していきます。

============================================
今まで父親と15年間一緒に仕事をしてきましたが、この度独立しました。
今までいろいろと守られていましたが、今後はすべては自分の責任。
迷惑をかけないように頑張ります。

本日も最後までお読みいただきありがとうございます。

行を入れ替える

行間にレコードを追加できるようになったら、今度は行の入れ替えです。
これは何も難しいことはありません。
ここで1行目と5行目のレコードを入れ替えます。

まずは変えたい行の番号の順番をダイレクトに直してしまいます。

すでに順番通りに並べ替える設定になっているので、再クエリを実行すれば順番の入れ替え
は完了します。

ホームタブの「すべて更新」をクリックすれば変更完了です。

ただこの方法だと、正規版でしか実行できないので、コマンドボタンを押して実行できるようにします。

メインフォームにコマンドボタンを作って、クリック時に「埋込みマクロ」を選択します。
アクションで「再クエリ」を選択して完了。

コマンドボタンを実行すると入れ替わります。

今回は当たり前と言えば当たり前の話でした。

順番を表示させるフィールドがあれば行の削除、挿入、入替ができます。
Accessはどうしても硬直的なシステムになり気味です。このようにシステムに
柔軟さを入れることで、入力する方の負担を軽くすることができます。

============================================
この4月から東海税理士会静岡支部の副支部長になりました。
またまた出世してしまいました。。

本日も最後までお読みいただきありがとうございます。

行間にレコードを追加するときの注意点

行間に追加したときは、次の注意点があります。

①レコードの並び方を指定しておきます。


折角順番を付けているので、並び替えは順番を選択します。

これでVBAでクエリを実行したときに、順番通りに並べ替えられます。
何も設定しないと、オートナンバーで表示されるので、順番通りになりません。
注意してください。

②絶対にレコードを入れなければいけないフィールドがある場合

クエリはリレーションシップで、商品明細の商品CDを商品テーブルの商品CDと関連付けをした場合、
商品CDにデータがない状態ではレコードの追加ができません。

そんなときは、VBAコードを次のように変更します。
rs.AddNew
rs!順番1 = a
rs!商品CD = 1
rs!納品書NO = Forms!納品書!納品書NO
rs.Update
仮の商品CDを入れる必要があります。

当然後から正しい商品CDをいれないといけません。
それ以外にテーブルの設定で「値要求」が「はい」になっているレコードについても
仮で何か値を入れる必要があります。

注意点はこれくらいです。

納品書等を作る場合、やはり行間にレコードを追加できるようにしておく必要があると
思います。こんな感じで参考にしてもらえればと思います。

サンプルファイルを上げておきますので、必要であればダウンロードしてください。

======================================
コロナワクチン1回目打ってきました。
1回目は特に副反応はありません。(今のところ)
問題は2回目だな。
家族に走ることを止められて、それがストレスです。。

本日も最後までお読みいただきありがとうございます。

レコードを行間に追加する

前回はレコードを削除した場合の行番号を整えました。

今回は、行と行の間にレコードを追加する場合のやり方です。

Accessを使っていてなんでこれが簡単にできないんだろうというものの一つが
行の挿入です。Excelでは簡単ですよね。右クリックで行の挿入、これで終わりです。

AccessではVBAを使わないとできません。

下の画面の1と2の間と2と3の間にレコードを挿入します。

コマンドボタンを作って、そこに以下のVBAコードを入れます。

Private Sub コマンド13_Click()
Dim a As Integer

Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset

Set cnn = CurrentProject.Connection
Set rs = New ADODB.Recordset

rs.Open “納品明細”, cnn, adOpenKeyset, adLockOptimistic

a = Me!順番1

rs.MoveFirst

Do Until rs.EOF
If rs!納品書NO = Forms!納品書!納品書NO Then
If rs!順番1 >= a Then
rs!順番1 = rs!順番1 + 1
rs.Update
End If
End If
rs.MoveNext
Loop

rs.AddNew
rs!順番1 = a
rs!納品書NO = Forms!納品書!納品書NO

rs.Update

rs.Close
Set rs = Nothing
cnn.Close
Set cnn = Nothing

Forms!納品書!納品明細.Form.Requery
End Sub

このプログラムを実行すると、下記のようになります。

無事レコードを挿入して、番号もそろえることができました。
データベースの構造上、常に一番下にレコードが追加されていくので、これの入力順を
変更することは不可能です。ですので順番というフィールドを作って、これで操作して
いきます。

テーブルを見るとこんな感じですね。

ちなみにIDはオートナンバー型です。

Excelばかりつかっているとこの考え方を理解するのに手間取るかもしれませんが、
大事な考え方なので、是非分かってあげてください。

=======================================
9月になりました。
やっと明後日にワクチン1回目です。
いろいろ不安はありますし、打たなくてよければ打ちませんが、仕方がありません。
副反応が少なければいいなあ。。

本日も最後までお読みいただきありがとうございます。

Excelの正しい使い方

仕事柄いろいろなExcelシートを目にしますが、皆さんいろいろなやり方でExcelを
管理されています。

Excelでデータを管理するうえでの大原則があります。
それは
①データは行で貯める(縦に展開)
②列やシートは増やさない(横には展開しない)
③表はピボットテーブルで作る

ということです。
続きを読む

請求明細から請求書を作る④~請求明細データを追加する

Excelからテーブルにインポートしたレコードから明細を作り直します。

最初に請求明細を入れるテーブルを用意します。
フィールドの設定はこんな感じです。インポートしたときはたくさんのフィールドが
ありましたが、必要最低限のものでいいと思います。

続きを読む