How To Create A Report In Database

Recipe Database (Main Screen)

Eric Cramer

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

Eric Cramer

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

Eric Cramer

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".

Screenshot of T001 - Food Categories

Eric Cramer

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:

• Appetizers
• Breads
• Fish
• Beef
• Poultry
• Pork
• Soup
• Pastas
• Sides
• Desserts

Save and close the table when finished.

Fields for T002 - Recipes

Lookup Wizard (Table - Design view)

Eric Cramer

Eric Cramer

Eric Cramer

Recipes Table

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

Form Wizard Screenshots

Eric Cramer

Read More From Owlcation

Eric Cramer

Food Categories Form

1. 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.
2. Click on the Tables/Queries drop down and select "T001 – Food Categories".
3. Click the right arrow to select "Food Category" as a selected field.
4. Click next and change the selection to "Tabular."
5. Pick a style that you like. I prefer the "Windows Vista" myself.
6. Click next again and name the form "F001 – Food Categories." Change the bottom selection to "Modify the form's design" and click Finish.
7. 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.
8. 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.

Recipe Form

1. 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.
2. 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.
3. 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.
4. 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

Screenshot of my form layout

Eric Cramer

Tab Order Screenshot

Eric Cramer

Macro Setup Screenshot - Autoexec

Eric Cramer

Recipe Form - Additional Steps

1. 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.
2. 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.
3. Change the form title to whatever you want to call it.
4. 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.
5. 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.
6. 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

Query to isolate 1 Food Category based on user input

Eric Cramer

Create Queries to Drive Report Generation

Create a Query to Pull an Individual Category of Recipes

1. 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.
2. 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.
3. 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.
4. Save the query as "Q100 – Recipes by Food Category."
5. Close the query box.

Copying a Query

Copy Q100 and Paste it as Q101

Eric Cramer

Create a Query to Pull an Individual Recipe

1. 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".
2. Right-click on "Q101 – Select Specific Recipe" and select "Design View" from the menu. Delete "[Enter Food Category]."
3. Under "Recipe Name", enter "[Enter Recipe Name to Pull Up]."
4. Save the query and exit out of it.

Create Reports

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.

1. 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".
2. 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.
3. 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

Eric Cramer

Eric Cramer

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

Property Sheet Screenshot

Eric Cramer

Create a Report Listing a Selected Food Category

1. 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.
2. 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

1. Repeat the first step in the last section, except paste the file as "R004 – Create a Recipe Card".
2. Repeat step two above, but change the "Record Source" to "Q101 – Select Specific Recipe" and save the report.

Finishing Touches

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).

Stella on May 19, 2020:

This was awesome!

I never used access before, but this step by step guide made me feel a pro! :)

Quick (hopefully) question: If I want to search by ingredient, how should I go about it?

Onur Gulcat on January 30, 2020:

Thank you for this article. I want to calculate (for example) the total amount of the ingredient 1 that is used in several recipes, so I can know how much ingredient 1 is left. How can we do this?

John on January 11, 2020:

I have too many recipes and find it difficult to see all & compare a particular ingredient, for example

Looking forward to getting my head around Access

Saketa from Washington on January 02, 2020:

Thank for Useful the informative.

Pam on May 09, 2018:

Thanks for this! I have used Access before but not much. The steps were easy to follow even though I am in 2016 so they weren't quite exactly the same.

Adam on April 03, 2018:

Thank you for this tutorial. Considering I have never used Access, this was a good one to start with. I am currently trying to build a database for work (assisted living facility) due to their tedious and ineffective manner they use keep up with their inventory, standardizing recipes so that everyone is on the same page, as well as digitizing their ordering process and having "real" reports on waste. If anyone has an idea where I can go from here to do that, I would deeply appreciate it.

Andrew on September 28, 2017:

Thank you very much for this article. I found this very useful for my database class. Have a good weekend friend.

Eleanor on April 21, 2017:

I followed steps under recipe table and when I selected the Lookup wizard for the food categories I got an error message that No valid fields in T001 - Food Categories table.

Please advise.

Regards

Russ on December 04, 2016:

This was really nice of you to share. Great job!

Sarah on September 29, 2016:

I am trying to make my Food Categories form table but it is not displaying like yours

DW Green Company from Tempe Arizona on May 19, 2015:

Great info! I've built recipe databases for websites in MySql and PHP before, but this is really handy for personal use, especially if you love cooking!

Kristen Howe from Northeast Ohio on March 03, 2015:

Clever idea, Eric. You can post your own recipes or ones from the Internet or other sources. Who would need cookbooks anymore? Voted up.

Jen Gregory from Harcourt, Victoria on February 05, 2015:

Hi Eric, thanks for your instructions on the recipe database. What I'm looking to do is be able to make a shopping list from a weekly menu plan. How would I take 6 recipes, for example, merge their ingredients and then print this out as a report for a shopping checklist? thanks, Jen G (another tricky thing to consider is then discounting ingredients I already have and finding out what the 'revised' or actual shopping list would be...)

Eric Cramer (author) from Chicagoland on January 26, 2015:

Getting started in Access is pretty easy. Creating tables to house data is straightforward and simple. Queries are probably the most challenging part depending on what you are trying to do.

TTGReviews on January 26, 2015:

I have never used Access before. This seems like a good idea. How hard would it be to get started with Access? I have had some programming experience in the past.

tmercks@juno.com on September 10, 2014:

Been a while since I used Access. I'm going to give it a try - but I feel I will be making a "pdf organizer" instead of a full blown recipe index. I have used word to create a one-page notebook sheet for each recipe I have. My Database only needs to be a front end (ingredient, category, etc.) searchable organizer to bring up the specific pdf sheet for viewing and printing. Should be quite a snap with your instructions.

Eric Cramer (author) from Chicagoland on May 03, 2014:

Thanks Dataload!

DATALOAD from Michigan on May 03, 2014:

Thanks for the tips. I like to keep a lot of my recipes in Google Drive so I can access them with the tablet in the kitchen. They can also be downloaded easily enough if I have to go somewhere.

Alyson on May 27, 2013:

I'm very new to Access, but your tutorial was great! I was wondering how to set up a query to search by ingredient?

mgentry on May 16, 2013:

Thank you so much this is great!!

Jasnav on August 23, 2012:

Nice hub!

Access has amazing tools for those wanting to try it out, and it's not limited just to recipes. I've myself used the tools to effect in several places, and the results have always been impressive.

Riverfish24 from United States on July 30, 2012:

More MS Access, another hub for keeps. Thanks for all the guidance and tips!

Eric Cramer (author) from Chicagoland on July 30, 2012:

Thanks! Learning Access took a lot of trial and error before I figured much of it out.

Kristi Sharp from Born in Missouri. Raised in Minnesota. on July 30, 2012:

You are so good with Access. I have a ton of recipes that seem to float around needing a home. This will help a lot! Thanks -K

How To Create A Report In Database

Source: https://owlcation.com/stem/How-to-Create-a-Recipe-Database

Posted by: levinespinat.blogspot.com

Share this post