Microsoft Office Tutorials and References
In Depth Information
Case 1: Preliminary Case: Veggie Box Delivery
VEGGIE BOX DELIVERY
Setting up a Relational Database to Create Tables,
Forms, Queries, and Reports
In this case, you will create a relational database for a local farm that delivers boxes of fresh fruits and
vegetables to local customers on a weekly basis. First, you will create three tables and populate them with data.
Next, you will create a form and subform for recording new customers and their orders. You will create four
queries: a select query, a parameter query, a totals query, and a query used as the basis for a report. Finally,
you will create the report from the fourth query.
Before attempting this case, you should have some experience using Microsoft Access.
Complete any part of Access Tutorial B that your instructor assigns, or refer to the tutorial as
More and more people are buying
to be more environmentally friendly. In addition, many people feel
that they can taste the difference between locally grown produce and produce that is grown far away and
shipped to their grocery store. In response, a farm just outside of Atlanta, Georgia is selling boxes of its
in-season fruits and vegetables to Atlanta residents. Customers sign up for the so-called
pay on a monthly basis. The veggie boxes are delivered to customers
homes every Thursday afternoon. The
contents of the box are a surprise; for example, customers do not know if they will receive fresh asparagus,
peaches, blueberries, or other fruits and vegetables that were picked that morning.
The veggie box is growing in popularity, and the owners of the farm need your help. The owners have
heard that you are proficient in Microsoft Access, and they have asked you to computerize their ordering
system. They had hired a summer intern last year, so the database design is already created.
Your first job is to create the tables and populate them with data. Until now, customer orders have been
tracked manually. The database design includes three tables, as shown in Figures 1-1, 1-2, and 1-3: Customers,
which keeps track of each customer’s ID number, name, address, e-mail address, and credit card number for
billing purposes; Boxes, which keeps track of the three box types, their description, and monthly price; and
Orders, which keeps track of each order number, customer ID, box type, and start date of the service.
The owners have a few requirements for information output in the database beyond simply recording the
data. First, they would like to have an easier way to record a new customer
s information and the type of box
the customer ordered. You can accomplish this task by creating a form and subform.
In addition, the owners would like the database to answer some questions. To enable efficient delivery of
the veggie boxes, the drivers want to be able to print the delivery addresses on their routes and order the list
by zip code. In addition, the owners want to know how many orders they receive for each type of box so they
can plan for next year’s crop and subsequent harvest.