Microsoft Office Tutorials and References
In Depth Information
Case 4: T-Shirts etc. Order Database
T-SHIRTS ETC. ORDER DATABASE
Designing a Relational Database to Create Tables, Forms,
Queries, and Reports
In this case, you will design a relational database for a business that prints custom t-shirts and assorted clothing
and sells them to customers who attend the local university. After your database design is completed and correct,
you will create database tables and populate them with data. Then you will produce one form with a subform, six
queries, and two reports. The queries will address the following questions: What items sell for less than $20? What
are the best-selling products? What are the most popular colors in the best-selling products? Who are the best
customers? Another query will help the owner update prices on items. One report will list customer orders,
including specific discounts based on a query. Another report will display the output from a query more professionally.
Before attempting this case, you should have some experience in database design and in using
Complete any part of Database Design Tutorial A that your instructor assigns.
Complete any part of Access Tutorial B that your instructor assigns, or refer to the tutorial as
Refer to Tutorial F as necessary.
Susan is a local artist who has years of experience in the textile industry. Her children attend the local
university, and they sometimes ask her to design and print custom t-shirts for their university organizations,
such as clubs or sports teams. Friends of Susan
s children have always complimented them on the custom
shirts, and have requested some of their own. Susan has decided to go into business printing custom apparel
and selling it to organizations at the local university. Her forecasts indicate that her first-year sales will be
quite high, so she will be very busy generating all the products for the orders. As a consequence, she wants to
hire a part-time worker to handle the orders that come into the shop. One of Susan’s children is a friend of
yours, and knows you have mastered database design and implementation. Your friend asks you to apply for
the job. Before you begin, you interview Susan about how the company works.
YOU: Tell me about how you take orders.
SUSAN: When a potential customer requests a custom job, I first take down all the customer
information, such as the person
s name, street address, city, state, and zip code. I also request their
phone number in case I have questions about the order.
YOU: Sorry to interrupt, but can some customers have the same name?
which is my dining room. There I show them t-shirts, both long and short sleeved; fleece jackets,
either pullover or zipped; hoodies, caps, and fleece hats. Each has a different price, which is marked
on the item, and each can be ordered in a variety of colors and sizes, except headwear. When the
customer is ready to order, I fill out a form with the customer information repeated on the top of the
order. I would like not to have to rewrite all this information because I sometimes make copying