How to Create a Recipe Database
Organizing your recipes all in one place is a great idea, but the thought process usually ends just as a thought. Putting all of your recipes in recipe organizers is difficult because you still have to organize all of the recipes and most of our recipes are either cutout of a magazine or written on paper that is all different sizes. In addition, if you are looking for a particular recipe, you still have to go through the binder to find it. In the long run, your life will be much simpler if you create a database in Access to hold your recipes.
Creating a Database in Access
Create a new Database in Microsoft Access
Open Microsoft Access and click on “New Blank Database” at the top of the screen. On the right hand side, a box will come up and ask you to give it a filename and select where you want to save the file. Click on the “Create” button and now you are ready to start building your database.
Table Creation in Access
Create Tables for your Recipe Database
Food Categories Table
The screen should be blank with a “Table1” in orange at the top with only “ID” as a category.
1. Click on the View icon in the upper left hand corner of the screen and select “Design View” which will cause a “Saves As” box to pop up. Save this table as “T001 - Food Categories”.
2. The key next to where it says “ID” only allows unique records. Replace “ID” with “Food Categories” and change the Data Type to “Text.” Click on the save disk icon at the top of your screen and then click on the View icon again (Should look like a picture of a spreadsheet) and enter the following categories plus any that you want to add:
Save and close the table when finished.
Fields for T002 - Recipes
This table will house all of the recipe data. Feel free to add more fields to this table as you see fit. Click on Access’ “Create” tab and then click on the “Design Table” button. A blank table will open up. Enter the following fields plus any that you want that I do not have:
- Recipe Name – Text Format
- Recipe Description – Text Format
- Source – Text Format
- Food Category – Lookup Wizard – Leave want to lookup from table or query checked and click next through each of the input box screens and finally finish. Access will ask you to save the table since this table is now linked to the first table. Save the table as “T002 – Recipes.”
- Preparation Time – Text Format
- Number of Servings – Number, set the decimal places at the bottom to 1 decimal.
- Calories per Serving – Number, set the decimal places at the bottom to 0 decimals.
- Ingredient – Text Format – Enter 19 more ingredient lines (Ingredient1, Ingredient2, etc) – One tip here is to enter the first line and copy and paste 19 more times. Then go back and number the Ingredients (Ingredient1, Ingredient2, etc.)
- Quantity – Text – Enter 19 more ingredient lines (quantity1, Quantity2, etc.)
- Instructions – Text Format – Enter 19 more ingredient lines (Ingredient1, Ingredient2, etc)
- Attachments – Attachment Format – This allows you to attach pictures or a copy of the actual recipe.
Note: It is important that you add all of the fields that you think you may want now. It is easier to either skip fields or ignore them than it is to add fields later because you have to add them to any queries, forms, or reports that you plan to use.
Create a Form for Each Table
Now we will create a form for the two tables. The Food Categories Form will be quick and very simple to do. The Recipes Form will be the fun one that will take some time to layout, but it will not be too difficult.
Food Categories - Design View
Food Categories Form
- Click on Access’ “Create” tab and then click on the “More Forms” drop down in the Forms section. Select “Form Wizard” and an input box will pop up.
- Click on the Tables/Queries drop down and select “T001 – Food Categories”.
- Click the right arrow to select “Food Category” as a selected field.
- Click next and change the selection to “Tabular.”
- Pick a style that you like. I prefer the “Windows Vista” myself.
- Click next again and name the form “F001 – Food Categories.” Change the bottom selection to “Modify the form’s design” and click Finish.
- Under the “Detail” area of the form, pull the box around “Food Category” over until it is lined up with the 3” mark. Pull up the light blue bar (Form Footer) until it touches the bottom of the “Food Category” detail box.
- Click on the “View” button and this will show you what you form looks like. You should see everything that you typed in when you built the table. Close and save the form when finished.
- Click on Access’ “Create” tab and then click on the “More Forms” drop down in the Forms section. Select “Form Wizard” and an input box will pop up. Click on the drop down box and select “T002 – Recipes.” Now click on the “>>” and all of the fields in our recipes table will be included on our form. Click next twice since the columnar form works the best for what we want. Select your favorite style and click next. Change the title to “F002 – Recipes”, change the bottom selection to “Modify the form’s design”, and then click Finish.
- A messy and disorganized form will pop up. On the far left side of the toolbar on Access’ “Design” tab, click on the drop down and select “Design View.” The best way to clean this up is to click near the top left of the detail box, hold shift down, and highlight all the way to the bottom right hand corner and hit the delete key. You can leave the form title above the line.
- If it is not already open, click on the “Add Existing Fields” button on the “Design” tab. Click “Recipe Name”, hold down the shift key, and click on the last field. Drag the highlighted section and put it a little ways down from the “Detail” bar.
- Layout the form however you want. I have attached a screenshot of how I laid my form out. For the top 7 fields, click on the title to drag them to where you want them. If you wish to put more or less space between the title and the field, click and drag the little brown box to the left of the field.
- Tip #1: One tip that I would offer is to click above the title for “Ingredients”, highlight all of the titles to the bottom, and delete the titles before reorganizing your fields.
- Tip #2: Remember to try to keep all of the fields visible when you switch back to “Form” view. If you are not sure, toggle between “Form View” and “Design View.”
- Tip #3: Be patient. It will take a while to align everything the way you want it.
F002 - Recipe Form
Recipe Form - Additional Steps
- To insert new titles, click on the “Label” button on the “Design” tab and draw a box on your form. Type the label and hit enter. Format the box so that it stands out as a label.
- Scroll all the way to the far right as you can and pull the right edge over to around the 13-inch mark on the ruler. Depending on your display settings, you may need to play with this a little. Do the same thing for the bottom. Toggle back and for between “Design View” and “Form View.” My goal is not to have either of the scroll bars being visible.
- Change the form title to whatever you want to call it.
- In a blank area of the form, right-click, which will bring up a menu, select “Tab Order. . .” This menu controls how the tab key works in your form as you move from field to field. I made my form start with recipe name and then have it going to recipe description. Then it moves across the rest of the fields at the top in the same way (top, bottom, top, bottom, etc.) Then I have it go from quantity to ingredient, quantity1 to ingredient1 and so on. Finally, I have it go straight down the instruction fields.
- Enter at least two different recipes into the “Recipe” form, preferably into two different food categories. This will make it easier to create queries and reports.
- On Access’ “Create” menu, click on “Macro”, and the macro screen will pop up. Under the action column, select “Open Form”. On the bottom section, select “F002 – Recipes” as the form name to open. Back up under the action column, select maximize. Next, select “GotoRecord” as your action. In the bottom section, choose form as the object type, choose “F002 – Recipes” as the object name, and select “new” from the record drop down. Save the macro as “autoexec”. This tells Access to open this form when the database is opened.
Query - Design View
Create Queries to Drive Report Generation
Create a Query to Pull an Individual Category of Recipes
- Go to Access’ “Create” tab and click on the “Query Design” button. A pop up box will appear asking which table to base the query on. Click on “T002 – Recipes” and click the add button. Now click close.
- Click on “Recipe Name” in the “T002 – Recipes” box, hold the shift key down, and then click on the bottom field name. It may take a second or two to respond. Once it is highlighted, drag all of the fields into the boxes below.
- Under “Food Category”, go down to the criteria row and enter the “[Enter Food Category]” without the quotation marks. This will cause a pop up box to come up when the query is run that asks you to enter the food category that you want. We will use this to generate a report.
- Save the query as “Q100 – Recipes by Food Category.”
- Close the query box.
Copying a Query
Create a Query to Pull an Individual Recipe
- On the left hand side of the screen, right-click on “Q100 – Recipes by Food Category” and select “Copy”. Right-click again and select paste. This will bring up a pop up box asking you what to paste the name of the query as. Enter “Q101 – Select Specific Recipe”.
- Right-click on “Q101 – Select Specific Recipe” and select “Design View” from the menu. Delete “[Enter Food Category].”
- Under “Recipe Name”, enter “[Enter Recipe Name to Pull Up].”
- Save the query and exit out of it.
We will create a report template and then use copy/paste to create two more with a few tweaks. Report writing is a lot like form design. It will take some time to organize everything the right way.
Create a Report Listing all Recipes – Sorted by Food Category
The first report that we will create will be a report that lists all recipes by name.
- On Access’ “Create” menu, click on the “Report Design” button. This will open a blank report. Save the report as “R002 – Recipes Grouped by Food Category”.
- Click on the “Add Existing Fields” button and then open up the fields for table “T002 – Recipes”. Drag “Recipe Name” into the detail section of the report. Now click on “Recipe Description”, hold shift down, and click on “Instructions19” at the bottom and drag all of those fields into the detail section.
- Click on the “Group & Sort” button (“Design” menu), which will open up a box on the bottom of the screen. Click “Add a Group” at the bottom. Select “Food Category” from the list. To best set up the report, please refer to the pictures below:
R002 - List all Recipes by Food Category
The biggest thing to remember is that you want to make sure each recipe prints on only one page.
Changing the Record Source on a Report
Create a Report Listing a Selected Food Category
- Right-click on the “R002 – Recipes Grouped by Food Category” and copy it. Now right-click in the same area, paste the report as “R003 – Recipe Category”, and hit enter.
- Right-click on the new report and open it in design view. Click on the “Property Sheet” button on the far right side of the “Design” menu. Change the “Record Source” to “Q100 – Recipes by Food Category” and save the report.
Create a Report Listing a Selected Recipe
- Repeat the first step in the last section, except paste the file as “R004 – Create a Recipe Card”.
- Repeat step two above, but change the “Record Source” to “Q101 – Select Specific Recipe” and save the report.
To finish this recipe database off, open the recipe form (F002 – Recipes) into design view. Now we will add a few buttons that will automate this database. Arrange them however you wish.
- New Record Button – On the design menu, click on the “Button” button and draw it out in the form header section. A popup box will appear. Select “Record Operations” as a category and “Add a New Record” as an action. Click finish. Resize the button to a small square.
- Delete Record Button – Same as the instructions above, except select “Delete Record” as the action instead.
- Find Button – Start out the same way, but select “Record Navigation” as the category, and “Find Record” as the action. Click Finish.
- Print All Button – Click on the “Button” button and select “Report Operations” as the category. You can either chose “preview the report” or “print report” as the action and click next. Select R002 as your report and click next. Click on text and type “Print All” and then click on the finish button.
- Print Category Button – Same as above, except select R003 as your report instead. Name the button “Print Food Category”.
- Print Individual Recipe Button – Same as above, except select R004 as your report instead. Name the button “Print Recipe”.
- Define Categories Button – After drawing out the button, select “Form Operations” as your category, and “Open Form” as your action. Select “F001 – Food Categories” as the form and click next twice. Change the text to be “Define Food Categories” and click finish.
Your recipe database is ready for use. Enter all of your favorite recipe cards into it. Remember that Access is fully customizable and that you can makes changes later. As a word of caution, be sure that if you add or delete any fields that you update all of the items down the line (queries, forms, reports, etc).