作成者別アーカイブ: miura

フォームの作成③~抽出条件を加える

できたままのフォームでは使い物になりません
これを形を整えて、②のフォームのようにしていきます。


デザインビューで開いて下の図になるようにテキストボックスをまずは手直しします。
最初はテキストボックスを並べ替えます。
次は集計用のテキストボックスを作成します。

詳細の費用合計は、テキストボックスを配置して次の算式をコントロールソース
に入れます。
=Nz([家賃])+Nz([広告宣伝費])+Nz([その他])+Nz([消耗品費])+Nz([人件費])+Nz([仕入高])
粗利益については、同じくテキストボックスを配置して次の算式をコントロールソース
に入れます。

=[売上高]-[テキスト26]
※配置したテキストボックスの名前は異なるので、実際に作成してテキストボックスの
名前を使って算式を組んでください。以下同じ。

クロス集計クエリでは科目名ではなく、集計科目を使います。

科目名をそのまま使うと、横に広く枠が取れなくなってしまうので、集計科目という
フィールドを作り、そこに科目名を当てはめます。

フォームの上部に、抽出用のテキストボックスとコマンドボタンを配置します。
日付の横のコマンドボタンには次のVBAコードをクリック時に入れます。
Private Sub コマンド17_Click()
Me.Filter = “日付2='” & Me!テキスト15 & “‘”
Me.FilterOn = True
End Sub

店舗名はコンボボックスから店舗を選択できるようにします。
コンボボックスを貼り付ける際には、コントロールウィザードを起動して設定します。
①テーブルから値を選択
②T店舗を選択
③店舗CDと店舗名をフィールドに追加
④次の画面はそのまま次へ
⑤キー列を表示しないにチェックが入っていることを確認して完了

隣のコマンドボタンには次のVBAコードを入れます。
Private Sub コマンド22_Click()
Me.Filter = “店舗CD=” & Me!コンボ20 & “”
Me.FilterOn = True
End Sub

これで月毎、又は店舗毎に抽出できるようになりました。

==================================
昨年の1月からマラソンのトレーニングには某社の厚底シューズを履いて
いましたが、時々薄底シューズを履くと、筋力の衰えを感じました。
色々調べると、以前から厚底シューズによる筋力低下は問題になっているようです。
そんなわけで厚底シューズはレースまで温存し、練習は薄底に変えました。

足への負担が軽い分、筋肉は付きにくいということですね。
当たり前といえば当たり前ですが、気付かなかったー

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

フォームの作成②

F経費入力で経費を入力していくと、下のようにテーブルにレコードがたまって
行きます。

この貯めたデータを店舗ごとに集計していきます。
クエリの設定は下の通りです。
集計にはクロス集計クエリを利用します。
集計と行列の入れ替えは図の通り設定してください。

注意が必要なのが、日付です。データは月毎に集計したいので
年月で集計できるように
日付2: Format([日付],”yymm”)
として形式を整えます。
Q経費集計で保存します。

実行すると下の図のようになります。

このクエリを元に、フォームを作り店舗ごとの収益を表示できる
ようにします。

フォームの作り方は、フォームウィザードから
①Q経費集計のすべてのフィールドを選択
②表形式を選択
③完了で編集します。
とりあえず下の図がウィザード完了後のフォームです。

ここから形を整えていきます。

クロス集計クエリはExcelで言うところの、ピボットテーブルに似ている機能です。
AccessではExcelほど、クロス集計クエリは使いませんが、Excel的な集計を
Accessでやろうとするのであれば、非常に重宝します。

======================================
やっとコロナワクチン2回目接種完了しました。
これでコロナにかかりにくいし、仮に罹っても重症化することもないと思うと
安心です。
副反応も1回目よりも2回目の方が軽かったな。
こんなこともあるんですね。

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

明細に集計行を追加する①

請求書の小計・消費税・合計金額の出し方は大きく分けて二通りあります。

①一番下の行に表示させる場合

②明細行の一番下に表示させる場合

どちらにするかは使う方の好みの問題ですが、私が現場で選択をする場合
やはり経営者の好みに合わせて使い分けています。

実際に動作の設定は、①よりも②の方が圧倒的に面倒です(笑)

①の設定で面倒なのは、枠の行数を固定することで、データの出力自体は
特に設定は不要です。

②の設定は、出力する項目をいったん違うテーブルにデータを掃き出して
それを元にレポートで印刷します。
集計項目もクエリで計算してテーブルのレコードにしてしまいます。
そうすることで自由に設定ができるようになります。

一度出力したレコードは次の印刷時にすべて削除して、新しいレコードに
差し替えます。

流れ的には
追加クエリでレコードを追加し、
削除クエリでレコードを削除し、新たに追加クエリで追加する
こんな感じです。

これをマクロでするのか、VBAでやるのかです。マクロでも十分できます。

==========================================
最近業務改善の仕事をいくつか頂けるようになりました。
いくら書籍等で学んでも、現場で学べることには敵わないと感じます。
これからもまだまだ知識を増やして、お役に立てる情報を発信していきます!

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

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

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

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

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

続きを読む

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

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

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


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

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

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