保育園用栄養計算で作成した料理献立（566個）をAccessで利用できるように変換、Microsoft Access備忘録（4）

Access
この記事は約23分で読めます。

はじめに

　前回は、料理献立のテーブル（メインとサブ）を作成し、そのテーブルを基に選択クエリを作り、作成したクエリを基に料理献立メニューの登録、削除、追加などが行え、作成した料理献立の栄養価計算もフォーム上に表示させるようにしました。

食品成分表（八訂）を使った食事の献立フォームの作成、Microsoft Access備忘録（3）
料理献立のためのテーブルの作成、作成したテーブルにリレーションの設定、料理マスターテーブルと料理明細テーブルのクエリーの作成、料理フォームと料理明細フォームの作成、献立料理フォームの書式の変更、献立料理明細フォームの書式の変更、献立料理詳細クエリ（Q_rourimeisaitable）内の栄養成分フィールドに式を設定、献立料理詳細フォームのヘッダー部分に各栄養成分の合計欄の設定、合計欄の配置、各栄養成分の合計欄に合計式を設定、献立料理明細テーブルの各栄養成分への値の代入、献立料理フォーム及び献立料理明細フォームにレコード操作関係のコマンドボタンの配置などついて紹介した記事です。
yanai-ke.com
2022.03.16

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

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

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

保育園用栄養計算プログラム内の料理献立をAccessへの取込

　前回の料理献立フォームを作成したAccessのファイル（献立料理データ.accdb）を起動し、このファイルに保育園用栄養計算アプリ（保育園用栄養計算アプリ Eiyo-NCalc for nursery school .xlsm）から直接インポートしようとしましたが、「定義 XML によるスキーマの確認が失敗しました。XML ドキュメントの行|にエラーがあります。」と言うエラーが表示され、取り込むことができませんでした。

　そこで、保育園用栄養計算アプリ（保育園用栄養計算アプリ Eiyo-NCalc for nursery school .xlsm）のファイルから料理献立が記録されているシート（料理名一覧）を表示させ、シート全てをコピーして、新たに作成したExcelファイル（Book1）の「Sheet1」にペーストし、これを「名前を付けて保存」として、保存ファイル名を「料理一覧.xlsx」で保存しました。

　このファイルを良く見ると材料欄に記入されている材料コードは、保育園用栄養計算アプリでの独自の材用コードを利用しているために、食品成分表（八訂）を利用するためには、食品成分表（八訂）の食品番号にするために対応表を作成し、その対応表にしたがってVlookup関数を利用して、独自の材料コードを食品成分表（八訂）の食品番号に変換することにしました。

　ここで注意することは、フィールドラベルになるExcelの１行目のタイトルには、空白文字を入れないこと、加食量の欄には、数値型のデータを入れること（空白行は、””ではなく数値の「0」に置き換えること）です。

　変換した表は次の通りです。

　これで、Accessに取り込む準備ができました。

　上部メニューの「外部データ」⇒「新しいデータソース」⇒「ファイルから(F)」⇒「Excel(X)」と選択してクリックすると、「外部データの取り込み」- Excelスプレッドシート」のウインドウが表示されますので、その下にあるファイル名欄の横にある「参照(R)…」をクリック、ファイル選択画面で先に作成した「料理一覧.xlsx」を指定して「開く」をクリック、さらに「現在のデータベースの新しいテーブルにソースデータをインポート」にチェックを入れ「OK」をクリックします。

　「スプレッドシートインポートウィザード」が表示されますので、ワークシートにチェックを入れその横のシート選択部分で取り込むシート（Access取り込む表）を選択、下にその取り込む表が表示、「次へ(N) >」をクリック、さらに「スプレッドシートインポートウィザード」で「先頭行をフィールド名として使う(I)」にチェックが入っていることを確認後「次へ(N) >」をクリック、「フィールドのオプション」でフィールド名(M)の「献立No」のデータ型を「長整数型」を選択ご「次へ(N >)」をクリック、次に「次のフィールドに主キーを設定する(C)」にチェックを入れ、その横の欄が「献立No」が選択されていることを確認後「次へ(N) >」をクリック、「インポート先にテーブル(I)」を「MS_excelryouri」と書き換え「完了」をクリック、「インポート先の保存」画面がでますが「閉じる」をクリックします。

これで、Excelの料理献立の表がAccessのテーブルに取り込むことができました。

取り込んだExcelの料理データテーブルから料理マスターテーブルと料理明細テーブルに移し替え

　Excelから取り込んだ料理テーブルはこのままでも利用できますが、前回の料理献立フォーム作成で作成した料理マスターテーブルと料理明細テーブルに移し替えられれば、そのまま料理献立フォームが利用できるようになります。

　また、Excelで取り込んだテーブルは１つの料理に対して最大20食品素材までしか入っておりませんが、中には1つの料理に対して20以上の食品素材を使う料理もあると思います。

　そのような場合も、料理マスターテーブルと料理明細テーブルに分けて料理を登録できれば、１つの料理に対して食品素材の数は２０素材以上にも対応することが可能となります。

　Excelから取り込んだ料理テーブルから料理マスターテーブルと料理明細テーブルに分けて料理を登録するためには一つ一つ料理献立フォームから入力することも可能ですが、566個もの料理を登録するには大変な作業となります。

　したがって、ここではVBAを利用して登録作業を実施することにしました。

　料理フォーム内のフィールド値とExceから取り込んだ料理テーブル（MS_excelryouri）の対応付けは次の通りとしました。

【料理マスターテーブル】
日付フィールド　：　取り込んだ日付、Date
料理NOフィールド　：　Excelで取り込んだテーブルの「献立No」フィールド
料理大分類　：　文字列の”昼食”を代入
料理中分類　：　空白文字の””を代入
料理小分類　：　空白文字の””を代入
料理分類コード　：　Excelで取り込んだテーブルの「料理献立分類コードド」フィールド
料理名フィールド　：　Excelで取り込んだテーブルの「献立名」フィールド
メモフィールド　：　文字列の”保育園用”を代入

【料理明細テーブル】
NOフィールド　：　材料数により１、２、３、４　…　と代入
トータル数フィールド　：　オートナンバーなので特に代入する必要なし
料理NOフィールド　：　Excelで取り込んだテーブルの「献立No」フィールド（料理マスターテーブルで記入されているので料理明細フォーム内では記入する必要なし）
材料NOフィールド　：　Excelで取り込んだテーブルの材料1～材料20までを代入
材料名フィールド　：　材料NOフィールド値に対応する食品成分表（八訂、本表）の食品名を表示
使用量フィールド　：　Excelで取り込んだテーブルの加食量1～加食量20までを代入
廃棄率、その他の各栄養成分フィールド　：　Dlookup関数を用いて材料NOに対応する各栄養成分値を食品成分表（八訂、本表）から取り出し計算

　標準モジュールを呼び出し、その名前を「料理献立変換_エクセルからアクセスへ()」とし、上述にしたがって作成したVBAコードは次の通りです。
 
Public Sub 料理献立変換_エクセルからアクセスへ()
    Dim dbs As DAO.Database
    Dim rs, rs1, rs2, rs3 As DAO.Recordset
    Dim i As Integer
        Set dbs = CurrentDb
        Set rs = dbs.OpenRecordset("MS_ryourimeisaitable")
        Set rs1 = dbs.OpenRecordset("MS_Foodcomptable")
        Set rs2 = dbs.OpenRecordset("MS_excelryouri")
        Set rs3 = dbs.OpenRecordset("MS_ryouritable")
        
       rs2.MoveFirst
       Do Until rs2.EOF
            rs3.AddNew
            rs3!日付 = Date
            rs3!料理NO = rs2!献立No
            rs3!料理大分類 = "昼食"
            rs3!料理中分類 = ""
            rs3!料理小分類 = ""
            rs3!料理分類コード = rs2!料理献立分類コード
            rs3!料理名 = rs2!献立名
            rs3!メモ = "保育園用"
            rs3.Update
                    
            For i = 1 To 20
                If rs2("材料" & i) <> "" Then
                    rs.AddNew
                    rs!NO = i
                    rs!料理NO = rs2!献立No
                    rs!材料NO = rs2("材料" & i)
                    rs!使用量_g = rs2("加食量" & i)
                    rs![廃棄率_%] = DLookup("MS_Foodcomptable!REFUSE", "MS_Foodcomptable", "MS_Foodcomptable!FoodcompNO = '" & rs!材料NO & "'")
                    rs!エネルギー_kcal = DLookup("MS_Foodcomptable!ENERC_KCAL", "MS_Foodcomptable", "MS_Foodcomptable!FoodcompNO = '" & rs!材料NO & "'") * rs!使用量_g / 100
                    rs!水分_g = DLookup("MS_Foodcomptable!WATER_g", "MS_Foodcomptable", "MS_Foodcomptable!FoodcompNO = '" & rs!材料NO & "'") * rs!使用量_g / 100
                    rs!PROTCAA_g = DLookup("MS_Foodcomptable!PROTCAA_g", "MS_Foodcomptable", "MS_Foodcomptable!FoodcompNO = '" & rs!材料NO & "'") * rs!使用量_g / 100
                    rs!たんぱく質_g = DLookup("MS_Foodcomptable!PROTen_g", "MS_Foodcomptable", "MS_Foodcomptable!FoodcompNO = '" & rs!材料NO & "'") * rs!使用量_g / 100
                    rs!ATNLEA_g = DLookup("MS_Foodcomptable!FATNLEA_g", "MS_Foodcomptable", "MS_Foodcomptable!FoodcompNO = '" & rs!材料NO & "'") * rs!使用量_g / 100
                    rs!コレステロール_mg = DLookup("MS_Foodcomptable!CHOLE_mg", "MS_Foodcomptable", "MS_Foodcomptable!FoodcompNO = '" & rs!材料NO & "'") * rs!使用量_g / 100
                    rs!脂質_g = DLookup("MS_Foodcomptable!FATe_g", "MS_Foodcomptable", "MS_Foodcomptable!FoodcompNO = '" & rs!材料NO & "'") * rs!使用量_g / 100
                    rs!CHOAVLM_g = DLookup("MS_Foodcomptable!CHOAVLM_g", "MS_Foodcomptable", "MS_Foodcomptable!FoodcompNO = '" & rs!材料NO & "'") * rs!使用量_g / 100
                    rs!CHOAVL_g = DLookup("MS_Foodcomptable!CHOAVL_g", "MS_Foodcomptable", "MS_Foodcomptable!FoodcompNO = '" & rs!材料NO & "'") * rs!使用量_g / 100
                    rs!CHOAVLDF_g = DLookup("MS_Foodcomptable!CHOAVLDF_g", "MS_Foodcomptable", "MS_Foodcomptable!FoodcompNO = '" & rs!材料NO & "'") * rs!使用量_g / 100
                    rs!食物繊維総量_g = DLookup("MS_Foodcomptable!FIB_g", "MS_Foodcomptable", "MS_Foodcomptable!FoodcompNO = '" & rs!材料NO & "'") * rs!使用量_g / 100
                    rs!糖アルコール_g = DLookup("MS_Foodcomptable!POLYL_g", "MS_Foodcomptable", "MS_Foodcomptable!FoodcompNO = '" & rs!材料NO & "'") * rs!使用量_g / 100
                    rs!炭水化物_g = DLookup("MS_Foodcomptable!CHOCDF_g", "MS_Foodcomptable", "MS_Foodcomptable!FoodcompNO = '" & rs!材料NO & "'") * rs!使用量_g / 100
                    rs!有機酸_g = DLookup("MS_Foodcomptable!OA_g", "MS_Foodcomptable", "MS_Foodcomptable!FoodcompNO = '" & rs!材料NO & "'") * rs!使用量_g / 100
                    rs!灰分_g = DLookup("MS_Foodcomptable!ASH_g", "MS_Foodcomptable", "MS_Foodcomptable!FoodcompNO = '" & rs!材料NO & "'") * rs!使用量_g / 100
                    rs!NA_mg = DLookup("MS_Foodcomptable!NA_mg", "MS_Foodcomptable", "MS_Foodcomptable!FoodcompNO = '" & rs!材料NO & "'") * rs!使用量_g / 100
                    rs!K_mg = DLookup("MS_Foodcomptable!K_mg", "MS_Foodcomptable", "MS_Foodcomptable!FoodcompNO = '" & rs!材料NO & "'") * rs!使用量_g / 100
                    rs!CA_mg = DLookup("MS_Foodcomptable!CA_mg", "MS_Foodcomptable", "MS_Foodcomptable!FoodcompNO = '" & rs!材料NO & "'") * rs!使用量_g / 100
                    rs!MG_mg = DLookup("MS_Foodcomptable!MG_mg", "MS_Foodcomptable", "MS_Foodcomptable!FoodcompNO = '" & rs!材料NO & "'") * rs!使用量_g / 100
                    rs!P_mg = DLookup("MS_Foodcomptable!P_mg", "MS_Foodcomptable", "MS_Foodcomptable!FoodcompNO = '" & rs!材料NO & "'") * rs!使用量_g / 100
                    rs!FE_mg = DLookup("MS_Foodcomptable!FE_mg", "MS_Foodcomptable", "MS_Foodcomptable!FoodcompNO = '" & rs!材料NO & "'") * rs!使用量_g / 100
                    rs!ZN_mg = DLookup("MS_Foodcomptable!ZN_mg", "MS_Foodcomptable", "MS_Foodcomptable!FoodcompNO = '" & rs!材料NO & "'") * rs!使用量_g / 100
                    rs!CU_mg = DLookup("MS_Foodcomptable!CU_mg", "MS_Foodcomptable", "MS_Foodcomptable!FoodcompNO = '" & rs!材料NO & "'") * rs!使用量_g / 100
                    rs!MN_mg = DLookup("MS_Foodcomptable!MN_mg", "MS_Foodcomptable", "MS_Foodcomptable!FoodcompNO = '" & rs!材料NO & "'") * rs!使用量_g / 100
                    rs!I_μg = DLookup("MS_Foodcomptable!ID_μg", "MS_Foodcomptable", "MS_Foodcomptable!FoodcompNO = '" & rs!材料NO & "'") * rs!使用量_g / 100
                    rs!SE_μg = DLookup("MS_Foodcomptable!SE_μg", "MS_Foodcomptable", "MS_Foodcomptable!FoodcompNO = '" & rs!材料NO & "'") * rs!使用量_g / 100
                    rs!CR_μg = DLookup("MS_Foodcomptable!CR_μg", "MS_Foodcomptable", "MS_Foodcomptable!FoodcompNO = '" & rs!材料NO & "'") * rs!使用量_g / 100
                    rs!MO_μg = DLookup("MS_Foodcomptable!MO_μg", "MS_Foodcomptable", "MS_Foodcomptable!FoodcompNO = '" & rs!材料NO & "'") * rs!使用量_g / 100
                    rs!レチノール_μg = DLookup("MS_Foodcomptable!RETOL_μg", "MS_Foodcomptable", "MS_Foodcomptable!FoodcompNO = '" & rs!材料NO & "'") * rs!使用量_g / 100
                    rs![α-カロテン_μｇ] = DLookup("MS_Foodcomptable!CARTA_μg", "MS_Foodcomptable", "MS_Foodcomptable!FoodcompNO = '" & rs!材料NO & "'") * rs!使用量_g / 100
                    rs![β-カロテン_μｇ] = DLookup("MS_Foodcomptable!CARTB_μg", "MS_Foodcomptable", "MS_Foodcomptable!FoodcompNO = '" & rs!材料NO & "'") * rs!使用量_g / 100
                    rs![β-クリプトキサンチン_μｇ] = DLookup("MS_Foodcomptable!CRYPXB_μg", "MS_Foodcomptable", "MS_Foodcomptable!FoodcompNO = '" & rs!材料NO & "'") * rs!使用量_g / 100
                    rs![β-カロテン当量_μｇ] = DLookup("MS_Foodcomptable!CARTBEQ_μg", "MS_Foodcomptable", "MS_Foodcomptable!FoodcompNO = '" & rs!材料NO & "'") * rs!使用量_g / 100
                    rs!VITA_RAE_μg = DLookup("MS_Foodcomptable!VITA_RAE_μg", "MS_Foodcomptable", "MS_Foodcomptable!FoodcompNO = '" & rs!材料NO & "'") * rs!使用量_g / 100
                    rs!ビタミンD_μg = DLookup("MS_Foodcomptable!VITD_μg", "MS_Foodcomptable", "MS_Foodcomptable!FoodcompNO = '" & rs!材料NO & "'") * rs!使用量_g / 100
                    rs![α-トコフェロール_mg] = DLookup("MS_Foodcomptable!TOCPHA_mg", "MS_Foodcomptable", "MS_Foodcomptable!FoodcompNO = '" & rs!材料NO & "'") * rs!使用量_g / 100
                    rs![β-トコフェロール_mg] = DLookup("MS_Foodcomptable!TOCPHB_mg", "MS_Foodcomptable", "MS_Foodcomptable!FoodcompNO = '" & rs!材料NO & "'") * rs!使用量_g / 100
                    rs![γ-トコフェロール_mg] = DLookup("MS_Foodcomptable!TOCPHG_mg", "MS_Foodcomptable", "MS_Foodcomptable!FoodcompNO = '" & rs!材料NO & "'") * rs!使用量_g / 100
                    rs![δ-トコフェロール_mg] = DLookup("MS_Foodcomptable!TOCPHD_mg", "MS_Foodcomptable", "MS_Foodcomptable!FoodcompNO = '" & rs!材料NO & "'") * rs!使用量_g / 100
                    rs!ビタミンK_μg = DLookup("MS_Foodcomptable!VITK_μg", "MS_Foodcomptable", "MS_Foodcomptable!FoodcompNO = '" & rs!材料NO & "'") * rs!使用量_g / 100
                    rs!ビタミンB_1mg = DLookup("MS_Foodcomptable!THIA_mg", "MS_Foodcomptable", "MS_Foodcomptable!FoodcompNO = '" & rs!材料NO & "'") * rs!使用量_g / 100
                    rs!ビタミンB2_mg = DLookup("MS_Foodcomptable!RIBF_mg", "MS_Foodcomptable", "MS_Foodcomptable!FoodcompNO = '" & rs!材料NO & "'") * rs!使用量_g / 100
                    rs!ナイアシン_mg = DLookup("MS_Foodcomptable!NIA_mg", "MS_Foodcomptable", "MS_Foodcomptable!FoodcompNO = '" & rs!材料NO & "'") * rs!使用量_g / 100
                    rs!ナイアシン当量_mg = DLookup("MS_Foodcomptable!NE_mg", "MS_Foodcomptable", "MS_Foodcomptable!FoodcompNO = '" & rs!材料NO & "'") * rs!使用量_g / 100
                    rs!ビタミンB6_mg = DLookup("MS_Foodcomptable!VITB6A_mg", "MS_Foodcomptable", "MS_Foodcomptable!FoodcompNO = '" & rs!材料NO & "'") * rs!使用量_g / 100
                    rs!ビタミンB12_μg = DLookup("MS_Foodcomptable!VITB12_μg", "MS_Foodcomptable", "MS_Foodcomptable!FoodcompNO = '" & rs!材料NO & "'") * rs!使用量_g / 100
                    rs!葉酸_μg = DLookup("MS_Foodcomptable!FOL_μg", "MS_Foodcomptable", "MS_Foodcomptable!FoodcompNO = '" & rs!材料NO & "'") * rs!使用量_g / 100
                    rs!パントテン酸_mg = DLookup("MS_Foodcomptable!PANTAC_mg", "MS_Foodcomptable", "MS_Foodcomptable!FoodcompNO = '" & rs!材料NO & "'") * rs!使用量_g / 100
                    rs!ビオチン_μg = DLookup("MS_Foodcomptable!BIOT_μg", "MS_Foodcomptable", "MS_Foodcomptable!FoodcompNO = '" & rs!材料NO & "'") * rs!使用量_g / 100
                    rs!ビタミンC_mg = DLookup("MS_Foodcomptable!VITC_mg", "MS_Foodcomptable", "MS_Foodcomptable!FoodcompNO = '" & rs!材料NO & "'") * rs!使用量_g / 100
                    rs!アルコール_g = DLookup("MS_Foodcomptable!ALC_g", "MS_Foodcomptable", "MS_Foodcomptable!FoodcompNO = '" & rs!材料NO & "'") * rs!使用量_g / 100
                    rs!食塩相当量_g = DLookup("MS_Foodcomptable!NACL_EQ_g", "MS_Foodcomptable", "MS_Foodcomptable!FoodcompNO = '" & rs!材料NO & "'") * rs!使用量_g / 100
                    rs.Update
                End If
            Next i
            rs2.MoveNext
        Loop
        rs.Close: Set rs = Nothing
        rs1.Close: Set rs1 = Nothing
        rs2.Close: Set rs1 = Nothing
        rs3.Close: Set rs1 = Nothing
End Sub

　全ての料理を変換して登録するのに約２分弱かかりました。

　下図は、料理NOが１番と料理NOが最終の566番を表示した画面です。

おわりに

　 如何だったでしょうか？

　保育園用栄養計算プログラム内の料理献立をAccessへの取込、取り込んだExcelの料理データテーブルから料理マスターテーブルと料理明細テーブルに移し替えなどついて紹介してきました。

　次は、これまでの料理関係とは直接関係しませんが、健康を考える上では必要なBMI、理想体重、基礎代謝量、一日の総消費カロリーなどを年齢、性別、身長、運動量などを記入して、計算・表示させるフォームをAccessで作成して見たいと思います。

　お楽しみに・・・。

以上です。

コメント

タイトルとURLをコピーしました