はじめに
前回は、作成した食品成分表(八訂)から成分を、食品番号、食品名、エネルギー、タンパク質、脂質、炭水化物、食塩相当量などから検索して、検索結果を表示するフォームを作成しました。
今回は、作成した食品成分表(本表)を利用して食事の料理献立を作成し、その献立の各種栄養素の栄養価計算をしてみたいと思います。
なお、私のパソコン環境やソフトのバージョンは以下の通りです。
パソコンOS : Windows10 Pro
Windowsバージョン : 21H1
Microsoft 365 Accessのバージョン 64ビット版: 2201
料理献立のためのテーブルの作成
料理献立を作成するためには、どのような項目が必要か挙げて見ると次のようになります。
上述にしたがって、テーブルを作成します。
作成したテーブルにリレーションの設定
Accessのリボンの上部メニューの「データベースツール」⇒「リレーションシップ」をクリックします。
作成したテーブルMS_ryouritable、MS_ryourimeisaitableと前に作成した食品成分表(八訂、本表)間でリレーションを設定します。
先ず、MS_ryouritableの料理NOからMS_ryourimeisaitableの料理NOにドラッグ&ドロップします。
さらに、MS_Foodcomptable(食品成分表(八訂、本表))のFoodcompNOとMS_ryourimeisaitableの料理NOの間でリレーションを設定します。
このようにして設定したリレーション設定画面を次に示します。
料理マスターテーブルと料理明細テーブルのクエリーの作成
料理マスターテーブル(MS_ryouritable)を選択した状態でAccessの上部のリボンメニューの「作成」⇒「クエリウィザード」をクリックすると、「新しいクエリ」が立ち上がりますので、その中から「選択クエリウィザード」を選択して「OK」をクリック、MS_ryouritableの全てのフィールドを「>>」をクリックして「選択したフィールド」に移し「次へ」をクリック、クエリ名を「Q_ryouritable」として「完了」をクリックします。(この部分の画像は省略、次の料理マスターテーブルからのクエリを参照)
次に献立料理詳細テーブル(MS_ryourimeisaitable)を選択した状態でAccessの上部のリボンメニューの「作成」⇒「クエリウィザード」をクリックすると、「新しいクエリ」が立ち上がりますので、その中から「選択クエリウィザード」を選択して「OK」をクリック、MS_ryourimeisaitableフィールドを「>」をクリックして、栄養素から「廃棄率」を含む31成分を「選択したフィールド」に移し(全てを移すとフォームを作成する際に失敗する)「次へ」をクリック、「各レコードのすべてのフィールドを表示する(D)」にチェックが入っていることを確認後「次へ」をクリック、クエリ名を「Q_ryourimeisaitable」として「完了」をクリックします。
さらに、リレーションを設定している食品成分表(八訂、本表、MS_Foodcomptable)の食品名(Foodname)を「Q_ryourimeisaitable」の「材料NO」の次のフィールドに入れます。(この部分は省略)
料理フォームと料理明細フォームの作成
作成した料理マスターテーブルのクエリ(Q_ryouritable)を選択した状態で上部リボンメニューの「作成」⇒「フォームウィザード」をクリック、「フォームウィザード」が立ち上がりますので、「Q_ryouritable」の全てのフィールドを「>>」をクリックして「選択したフィールド」に移し、さらに上部にある「テーブル/クエリ」を「Q_ryourimeisaitable」に変更して表示されるフィールドを全て>>」をクリックして「選択したフィールド」に移し「次へ」をクリック、「サブフォームがあるフォーム(S)」にチェックが入っていることを確認後「次へ」をクリック、表形式にチェックを入れ「次へ」をクリック、フォーム名を「献立料理フォーム」、サブフォーム名を「献立料理明細フォーム」として「完了」をクリックします。
献立料理フォームの書式の変更
作成された献立料理フォームはAccessが勝手にレイアウトしたものなので、体裁を整えるために献立フォーム内のフィールドを移動して見易くします。
献立料理フォームを選択した状態で上部リボンメニューの「ホーム」⇒「表示」⇒「デザインビュー」をクリックします。
一行目に「日付」、2行目に「料理NO」、「料理分類コード」、3行目に「料理大分類」、「料理中分類」、「料理小分類」、4行名に「料理名」、5行名に「メモ」を配置し、それぞれのフィールドの幅、高さは適当に調整します。
さらに、献立明細フォームもラベルと詳細画面を分離しラベルの下に詳細画面を配置します。
文字が分かり易いようにフォントを「メイリオ」、背景を黒、文字色を白にします。
下図は上述の設定した後で献立料理フォームを表示を「フォームビュー」に切り替えた状態の画面です。
献立料理明細フォームの書式の変更
献立料理明細フォームを選択した状態で上部リボンメニューの「ホーム」⇒「表示」⇒「デザインビュー」をクリックします。
次のようにフィールドの幅高さ、フォントなどをプロパティーシートで設定します。
上述のように設定後の献立料理フォームのフォームビューの画像が以下の通りです。
献立料理詳細クエリ(Q_rourimeisaitable)内の栄養成分フィールドに式を設定
廃棄率などを含め31成分の栄養成分に式を設定しますが、ここでは例として廃棄率とエネルギーの部分のみを紹介します。
他の栄養成分については、エネルギーと同様に設定してください。
先ず、廃棄率のフィールドには、次のような式を設定します。
献立料理詳細フォームのヘッダー部分に各栄養成分の合計欄の設定
合計欄の配置
献立料理明細フォームをデザインビューで開き、プロパティシートでフォームヘッダーを選択後、高さを4.5cmに変更し、ヘッダーにある全てのフィールドラベルを選択してヘッダーの下端近くまで移動し、上部に隙間を作ります。
Accessの上部のリボンメニューの「フォームデザイン」からラベルを選択後、ラベル廃棄率の上部に配置し、ラベル名を「合 計」とし、幅1.4cm、高さ0.6cm、フォントをメイリオ、フォントサイズを12ポイント、フォントの色を赤(#ED1C24)、文字配置を中央、フォント太さ中太に設定します。
次にテキストボックスを選択し、「合 計」のラベル横で「エネルギー」ラベルの上に配置し、ラベル部分を消去して、値ボックスのみとし、幅1.4cm、高さ0.6cm、フォントをメイリオ、フォントサイズを11ポイント、フォントの色を赤(#ED1C24)、文字配置を中央、フォント太さ中太に設定します。
このようにして作成したテキストボックスの値ボックスをコピーして、各栄養成分ラベルの上に配置していきます。(全部で30成分)
上述のようにして作成した献立料理明細フォームのヘッダー部分を下記に示します。
各栄養成分の合計欄に合計式を設定
献立料理明細フォームのデザインビューでエネルギーなどを含め30成分について、式を作成していきます。
各栄養成分の値ボックスをクリックして、プロパティーシートのデータタブのコントロールソースの横にある「…」をクリックして、式ビルダ―を表示させ、式を記入する欄に「=Sum([栄養成分値])」を記入して「OK」をくリクします。
下図は栄養成分としてエネルギーを例にして式を作成した画面です。
献立料理明細テーブルの各栄養成分への値の代入
上述のフォームでの各栄養成分の値は表示はされていますが、実際に各栄養成分のデータは献立料理明細テーブル(MS_ryourimeisaitable)の各栄養成分のフィールドデータに書き込まれてはいません。
これをするためにVBAコードでフォームの各栄養成分データを献立料理明細テーブル(MS_ryourimeisaitable)の各栄養成分のフィールドデータに代入してやり、書き込む必要があります。
例えば、エネルギーを例で示すと次のようになります。
rs!エネルギー_kcal = Me.エネルギー
この式は、MS_ryourimeisaitableのレコードセットのエネルギーのフィールド値(rs!エネルギー_kcal
)に献立料理明細フォームのエネルギーの値(Me.エネルギー)を代入するという意味になります。
また、フォームに幅の制限から作成できなかった献立料理明細テーブル(MS_ryourimeisaitable)の栄養成分のフィールドデータ(例えば、アミノ酸組成によるたんぱく質、脂肪酸のトリアシルグリセロール当量、コレステロールなど)は、リレーションを設定してある食品成分表(八訂、本表、[MS_Foodcomptable)から該当するデータを引っ張ってくる必要があります。
例えば、アミノ酸組成によるたんぱく質を例で示すと次のようになります。
rs!PROTCAA_g = rs1!PROTCAA_g * Me![使用量(g)] / 100
この式は、MS_ryourimeisaitableのレコードセットのアミノ酸組成によるたんぱく質のフィールド値(rs!PROTCAA_g)に食品成分表(八訂、本表、MS_Foodcomptable)のアミノ酸組成によるたんぱく質の値(rs1!PROTCAA_g)に献立料理明細フォームの使用量を乗じてやり、100で割り算した値を代入するという意味で、食品成分の使用量に相当するアミノ酸組成によるたんぱく質を計算で求める式となります。
そして、これらの値は献立料理明細フォームの使用量の欄に記入してリターンを押した段階で自動的に処理されるようにします。
その為には、献立料理明細フォームの使用量の欄をクリックし、プロパティーシートを表示させ、そのイベントタグをクリックし、その中の「更新後処理」の右にある「…」をクリックすると「ビルダー選択」ウインドウが表示されますので、「コードビルダー」を選択して「OK」をクリックすると、Visual Basicエディター(VBE)に飛び、「Private Sub 使用量_g__AfterUpdate() End Sub」のサブルーチンが挿入されます。
このサブルーチンの「Private Sub 使用量_g__AfterUpdate()」と 「End Sub」の間に、先に解説した各栄養成分データを献立料理明細テーブル(MS_ryourimeisaitable)の各栄養成分のフィールドデータに代入して記録する次に示すVBAコードを書くことになります。
Private Sub 使用量_g__AfterUpdate()
Dim dbs As Database
Dim rs, rs1 As Recordset
Set dbs = CurrentDb
Set rs = dbs.OpenRecordset("MS_ryourimeisaitable")
Set rs1 = dbs.OpenRecordset("MS_Foodcomptable")
rs.AddNew
rs!NO = Me.NO
rs!料理NO = Me.料理NO
rs!材料NO = Me.材料NO
rs!使用量_g = Me.使用量_g
rs![廃棄率_%] = Me.廃棄率 '通常「%」文字はフィールド名などには使えないのでフィールド名を[]で囲った。
rs!エネルギー_kcal = Me.エネルギー
rs!水分_g = Me.水分
rs!PROTCAA_g = rs1!PROTCAA_g * Me!使用量_g / 100
rs!たんぱく質_g = Me.たんぱく質
rs!ATNLEA_g = rs1!FATNLEA_g * Me!使用量_g / 100
rs!コレステロール_mg = rs1!CHOLE_mg * Me!使用量_g / 100
rs!脂質_g = Me.脂質
rs!CHOAVLM_g = rs1!CHOAVLM_g * Me!使用量_g / 100
rs!CHOAVL_g = rs1!CHOAVL_g * Me!使用量_g / 100
rs!CHOAVLDF_g = rs1!CHOAVLDF_g * Me!使用量_g / 100
rs!食物繊維総量_g = Me.食物繊維総量
rs!糖アルコール_g = rs1!POLYL_g * Me!使用量_g / 100
rs!炭水化物_g = Me.炭水化物
rs!有機酸_g = Me.有機酸
rs!灰分_g = Me.灰分
rs!NA_mg = Me.Na
rs!K_mg = Me.K
rs!CA_mg = Me.Ca
rs!MG_mg = Me.Mg
rs!P_mg = Me.P
rs!FE_mg = Me.Fe
rs!ZN_mg = Me.Zn
rs!CU_mg = Me.Cu
rs!MN_mg = Me.Mn
rs!I_μg = rs1!ID_μg * Me!使用量_g / 100
rs!SE_μg = rs1!SE_μg * Me!使用量_g / 100
rs!CR_μg = rs1!CR_μg * Me!使用量_g / 100
rs!MO_μg = rs1!MO_μg * Me!使用量_g / 100
rs!レチノール_μg = rs1!RETOL_μg * Me!使用量_g / 100
rs![α-カロテン_μg] = rs1!CARTA_μg * Me!使用量_g / 100
rs![β-カロテン_μg] = rs1!CARTB_μg * Me!使用量_g / 100
rs![β-クリプトキサンチン_μg] = rs1!CRYPXB_μg * Me!使用量_g / 100
rs![β-カロテン当量_μg] = rs1!CARTBEQ_μg * Me!使用量_g / 100
rs!VITA_RAE_μg = Me.VITA_RAE_μg
rs!ビタミンD_μg = Me.ビタミンD
rs![α-トコフェロール_mg] = Me.[α-トコフェロール]
rs![β-トコフェロール_mg] = rs1!TOCPHB_mg * Me!使用量_g / 100
rs![γ-トコフェロール_mg] = rs1!TOCPHG_mg * Me!使用量_g / 100
rs![δ-トコフェロール_mg] = rs1!TOCPHD_mg * Me!使用量_g / 100
rs!ビタミンK_μg = Me.ビタミンK
rs!ビタミンB_1mg = Me.ビタミンB1
rs!ビタミンB2_mg = Me.ビタミンB2
rs!ナイアシン_mg = rs1!NIA_mg * Me!使用量_g / 100
rs!ナイアシン当量_mg = Me.ナイアシン当量
rs!ビタミンB6_mg = Me.ビタミンB6
rs!ビタミンB12_μg = Me.ビタミンB12
rs!葉酸_μg = Me.葉酸
rs!パントテン酸_mg = Me.パントテン酸
rs!ビオチン_μg = rs1!BIOT_μg * Me!使用量_g / 100
rs!ビタミンC_mg = Me.ビタミンC
rs!アルコール_g = rs1!ALC_g * Me!使用量_g / 100
rs!食塩相当量_g = Me.食塩相当量
rs.Update
rs.Close: Set rs = Nothing
rs1.Close: Set rs1 = Nothing
End Sub
上述のVBAコードでMS_ryourimeisaitableへ書き込んだデータは、なぜか同じレコードが2つずつ記録されてしまいました。
そして、2つの内の片方のレコードには廃棄率、以下栄養成分値が全て0で記録されているのです。
以下がその例です。
この理由が分かりません。
どなたか、分かる方がおられればお教えいただけると助かります。
したがって、利用する場合は図にあるように薄赤で網掛けしてあるレコードは削除して利用してください。
献立料理フォーム及び献立料理明細フォームにレコード操作関係のコマンドボタンの配置
これらコマンドボタンの配置の仕方は前の記事「食品成分表(八訂)の食品成分の検索フォームの作成、Microsoft Access備忘録(2)」の「コマンドボタンなどの配置」で書いていますので参照ください。
レコード操作のコマンドを配置し終えた画面を下図に示します。
おわりに
如何だったでしょうか?
料理献立のためのテーブルの作成、作成したテーブルにリレーションの設定、料理マスターテーブルと料理明細テーブルのクエリーの作成、料理フォームと料理明細フォームの作成、献立料理フォームの書式の変更、献立料理明細フォームの書式の変更、献立料理詳細クエリ(Q_rourimeisaitable)内の栄養成分フィールドに式を設定、献立料理詳細フォームのヘッダー部分に各栄養成分の合計欄の設定、合計欄の配置、各栄養成分の合計欄に合計式を設定、献立料理明細テーブルの各栄養成分への値の代入、献立料理フォーム及び献立料理明細フォームにレコード操作関係のコマンドボタンの配置などついて紹介してきました。
次は、Excelで作成した保育園用栄養計算プログラム内の料理献立メニュー(566個)を料理マスターテーブル(MS_ryouritable)と料理明細テーブル(MS_ryourimeisaitable)に移して、これらをAccessで利用できるようにしたいと思います。
お楽しみに・・・。
以上です。
コメント