食品成分表（八訂）を使った食事の献立フォームの作成、Microsoft Access備忘録（3）

目　次

はじめに
料理献立のためのテーブルの作成
作成したテーブルにリレーションの設定
料理マスターテーブルと料理明細テーブルのクエリーの作成
1. 料理フォームと料理明細フォームの作成
2. 献立料理フォームの書式の変更
献立料理明細フォームの書式の変更
献立料理詳細クエリ（Q_rourimeisaitable）内の栄養成分フィールドに式を設定
献立料理詳細フォームのヘッダー部分に各栄養成分の合計欄の設定
献立料理フォーム及び献立料理明細フォームにレコード操作関係のコマンドボタンの配置
おわりに

はじめに

　前回は、作成した食品成分表（八訂）から成分を、食品番号、食品名、エネルギー、タンパク質、脂質、炭水化物、食塩相当量などから検索して、検索結果を表示するフォームを作成しました。

食品成分表（八訂）の食品成分の検索フォームの作成、Microsoft Access備忘録（2）

検索フォームを作成するためのクエリーデータの作成、QFoodcomptableから分割フォームの作成、単票形式のフォームの見映えを整える、フィールド名を分かり易い和名に変更、フィールド幅の調整、フィールドを見易くするために移動して調整、ヘッダーを広げ中に検索用のコンボボックスやテキストボックスの配置、ヘッダーを広げタイトルを変更、検索用のコンボボックスやテキストボックスを配置、コマンドボタンなどの配置、食品成分の検索、クリアのコマンドボタンのVBAの作成などついて紹介した記事です。

　今回は、作成した食品成分表（本表）を利用して食事の料理献立を作成し、その献立の各種栄養素の栄養価計算をしてみたいと思います。

　なお、私のパソコン環境やソフトのバージョンは以下の通りです。

パソコンOS　：　Windows10 Pro
Windowsバージョン　：　21H1
Microsoft 365 Accessのバージョン　64ビット版：　2201

料理献立のためのテーブルの作成

　料理献立を作成するためには、どのような項目が必要か挙げて見ると次のようになります。

【料理マスターテーブル】　：　MS_ryouritable

料理を作成した日時　：　日付型
料理NO　：　短いテキスト型、主キー
料理大分類（朝食、昼食、夕食、間食など）：　短いテキスト型
料理中分類（和食、洋食、中華、その他など）：　短いテキスト型
料理小分類（主食、主菜、副菜、汁物、飲み物、デザートなど）：　短いテキスト型
料理分類コード（前の保育園で利用していた料理献立の分類コード）：　短いテキスト型
料理名　：　短いテキスト型
メモ　：　　短いテキスト型

【料理明細テーブル】 : 　MS_ryourimeisaitable

No　：　数値型
トータル数　：　オートナンバー型、主キー
料理NO　：　数値型
材料NO　：　　短いテキスト型
使用量_g　：　数値型
食品成分表（八訂、本表）の各栄養素（エネルギー、他タンパク質、脂質、炭水化物、カルシウム、カリウム、鉄分などのミネラル、ビタミン類、塩分相当量など）：　数値型。

　上述にしたがって、テーブルを作成します。

作成したテーブルにリレーションの設定

　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が勝手にレイアウトしたものなので、体裁を整えるために献立フォーム内のフィールドを移動して見易くします。

　献立料理フォームを選択した状態で上部リボンメニューの「ホーム」⇒「表示」⇒「デザインビュー」をクリックします。

　一行目に「日付」、２行目に「料理NO」、「料理分類コード」、３行目に「料理大分類」、「料理中分類」、「料理小分類」、４行名に「料理名」、５行名に「メモ」を配置し、それぞれのフィールドの幅、高さは適当に調整します。

　さらに、献立明細フォームもラベルと詳細画面を分離しラベルの下に詳細画面を配置します。

　文字が分かり易いようにフォントを「メイリオ」、背景を黒、文字色を白にします。

　下図は上述の設定した後で献立料理フォームを表示を「フォームビュー」に切り替えた状態の画面です。

献立料理明細フォームの書式の変更

　献立料理明細フォームを選択した状態で上部リボンメニューの「ホーム」⇒「表示」⇒「デザインビュー」をクリックします。

　次のようにフィールドの幅高さ、フォントなどをプロパティーシートで設定します。

フォームヘッダーの高さを3.5cm、背景を濃紺（#2F3699）、ラベルの文字色を白（＃FFFFFF）に設定
全てのフィールドのラベルは横書きになっているので、縦書きにするために、全てのフィールドラベルを選択してプロパティーシートの「その他」⇒「縦書き」を「はい」に設定
フォントをメイリオ、フォントサイズ10ポイント、文字配置を均等割り付けとします。
ただし、レチノール活性当量(μg)とα-トコフェロール(mg)のフォントサイズを７ポイント、ナイアシン当量(mg)、ビタミンＢ６(mg)、ビタミンＢ12(μｇ)、パントテン酸(mg)のフォントサイズ８ポイント
NO（ラベル、値）の幅を0.6cm、材料NO幅を0.5cm、材料名幅を3.5cm、他のフィールド幅を1.5cm
全てのラベルの高さを3cm、値の高さ0.5cm
ヘッダーのフォントラベルの境界線スタイル実線、線幅を細線に設定
上述の設定後にフィールドのラベル及び値フィールドを全て選択し、Accessのリボンメニューの「配置」⇒「配置/間隔」⇒「左右の間隔を均等にする(Q)」に設定

　上述のように設定後の献立料理フォームのフォームビューの画像が以下の通りです。

献立料理詳細クエリ（Q_rourimeisaitable）内の栄養成分フィールドに式を設定

　廃棄率などを含め31成分の栄養成分に式を設定しますが、ここでは例として廃棄率とエネルギーの部分のみを紹介します。

　他の栄養成分については、エネルギーと同様に設定してください。

　先ず、廃棄率のフィールドには、次のような式を設定します。

廃棄率(%): [MS_Foodcomptable]![REFUSE]

　この意味は、Q_rourimeisaitableを基に作成したフォームに材料NOを記入た際に、廃棄率の欄の材料NOに対応する廃棄率を自動的に食品成分表（八訂、本表、MS_Foodcomptable）から抽出して表示するようにするものです。

　次にエネルギーのフィールドには、次の式を設定します。

エネルギー(kcal): [MS_Foodcomptable]![ENERC_KCAL]*[使用量(g)]/100

　この意味は、Q_rourimeisaitableを基に作成したフォームに材料NOを記入た際に、エネルギーの欄に材料NOに対応するエネルギに使用量を乗じて100で割った値を自動的に食品成分表（八訂、本表、MS_Foodcomptable）から抽出したエネルギ値を使って計算し表示するようにするものです。

　他の栄養成分については、[MS_Foodcomptable]![ENERC_KCAL]の部分を各栄養素の部分に変更すればよいことになります。

　つまり、次のようになります。

栄養素フィールド名：[MS_Foodcomptable]![栄養素フィールドに紐づいた値フィールド名]*[使用量(g)]/100

　なお、この部分の各食品成分の使用量は、加食量（廃棄率を考慮した料理の各食品成分の実際の量）を意味します。

献立料理詳細フォームのヘッダー部分に各栄養成分の合計欄の設定

　合計欄の配置

　献立料理明細フォームをデザインビューで開き、プロパティシートでフォームヘッダーを選択後、高さを4.5cmに変更し、ヘッダーにある全てのフィールドラベルを選択してヘッダーの下端近くまで移動し、上部に隙間を作ります。

　Accessの上部のリボンメニューの「フォームデザイン」からラベルを選択後、ラベル廃棄率の上部に配置し、ラベル名を「合計」とし、幅1.4cm、高さ0.6cm、フォントをメイリオ、フォントサイズを１２ポイント、フォントの色を赤（#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![α-カロテン_μｇ] = rs1!CARTA_μg * Me!使用量_g / 100
    rs![β-カロテン_μｇ] = rs1!CARTB_μg * Me!使用量_g / 100
    rs![β-クリプトキサンチン_μｇ] = rs1!CRYPXB_μg * Me!使用量_g / 100
    rs![β-カロテン当量_μｇ] = 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へ書き込んだデータは、なぜか同じレコードが２つずつ記録されてしまいました。

　そして、２つの内の片方のレコードには廃棄率、以下栄養成分値が全て0で記録されているのです。

　以下がその例です。

　この理由が分かりません。

　どなたか、分かる方がおられればお教えいただけると助かります。

　したがって、利用する場合は図にあるように薄赤で網掛けしてあるレコードは削除して利用してください。

献立料理フォーム及び献立料理明細フォームにレコード操作関係のコマンドボタンの配置

　これらコマンドボタンの配置の仕方は前の記事「食品成分表（八訂）の食品成分の検索フォームの作成、Microsoft Access備忘録（2）」の「コマンドボタンなどの配置」で書いていますので参照ください。

レコード操作のコマンドを配置し終えた画面を下図に示します。

おわりに

　如何だったでしょうか？

　料理献立のためのテーブルの作成、作成したテーブルにリレーションの設定、料理マスターテーブルと料理明細テーブルのクエリーの作成、料理フォームと料理明細フォームの作成、献立料理フォームの書式の変更、献立料理明細フォームの書式の変更、献立料理詳細クエリ（Q_rourimeisaitable）内の栄養成分フィールドに式を設定、献立料理詳細フォームのヘッダー部分に各栄養成分の合計欄の設定、合計欄の配置、各栄養成分の合計欄に合計式を設定、献立料理明細テーブルの各栄養成分への値の代入、献立料理フォーム及び献立料理明細フォームにレコード操作関係のコマンドボタンの配置などついて紹介してきました。

　次は、Excelで作成した保育園用栄養計算プログラム内の料理献立メニュー（566個）を料理マスターテーブル（MS_ryouritable）と料理明細テーブル（MS_ryourimeisaitable）に移して、これらをAccessで利用できるようにしたいと思います。

　お楽しみに・・・。

以上です。