1  Creating the Wooden Crafts Database

Problem: Jan Merchant is an enterprising business person who has a small kiosk in a shopping mall that sells handcrafted wooden items for children, such as trains, tractors, and puzzles. Jan purchases products from individuals that make wooden products by hand. The database consists of two tables. The Product table contains information on products available for sale. The Supplier table contains information on the individuals that supply the products.

Instructions: Perform the following tasks

1. Create a new database in which to store all the objects related to the merchandise data. Call the database Wooden Crafts.

2. Create the Product table using the structure shown in Figure 1-82. Use the name Product for the table.

Structure of Product Table
FIELD NAME / DATA TYPE / FIELD SIZE / PRIMARY KEY / DESCRIPTION
Prod ID / Text / 4 / Yes / Product Id Number (Primary Key)
Description / Text / 20 / Description of Product
On Hand / Number / Number of Units On Hand
Cost / Currency / Cost of Product
Market Price / Currency / Selling Price of Product
Supplier Code / Text / 2 / Code of Product Supplier

3.Create a form for the Product table. Use the name Product for the form

4. Add the data shown in Figure 1-82 to the Product table.

Data for Product table
PRODUCT ID / DESCRIPTION / ON HAND / COST / SELLING PRICE / SUPPLIER CODE
BF01 / Barnyard Friends / 3 / 54.00 / 60.00 / PL
BL23 / Blocks in Box / 5 / 29.00 / 32.00 / AP
CC14 / Coal Car / 8 / 14.00 / 18.00 / BH
FT05 / Fire Truck / 7 / 9.00 / 12.00 / AP
LB34 / Lacing Bear / 4 / 12.00 / 16.00 / AP
MR06 / Midget Railroad / 3 / 31.00 / 34.00 / BH
PJ12 / Pets Jigsaw / 10 / 8.00 / 12.00 / PL
RB02 / Railway Bridge / 1 / 17.00 / 20.00 / BH
SK10 / Skyscraper / 6 / 25.00 / 30.00 / PL
UM09 / USA Map / 12 / 14.00 / 18.00 / AP

FIGURE 1-82

5. Create the Supplier table using the structure shown in Figure 1-83. Use the name Supplier for the table.

Structure for Supplier Table
FIELD NAME / DATA TYPE / FIELD SIZE / PRIMARY KEY / DESCRIPTION
Supplier Code ID / Text / 2 / Yes / Supplier Code (Primary Key)
F Name / Text / 10 / First Name of Supplier
L Name / Text / 15 / Last Name of Supplier
Address / Text / 20 / Street Address
City / Text / 20 / City
State / Text / 2 / State (Two-Character Abbreviation)
Zip Code / Text / 5 / Zip Code (Five-Character Version)
Telephone Number / Text / 12 / Telephone Number(999-999-9999 Version)

6. Add the data shown in Figure 1-83 to the Supplier table.

Data for Supplier Table
SUPPLIER CODE / FIRST NAME / LAST NAME / ADDRESS / CITY / STATE / ZIP / PHONE NUMBER
AP / Antonio / Patino / 34 Fourth / Bastrop / NM / 75123 / 505-555-1111
BH / Bert / Huntington / 67 Beafort / Richford / CA / 95418 / 707-555-3334
PL / Ping / Luang / 12 Crestview / Mackington / AZ / 85165 / 602-555-9990

Figure 1-83

7. Create the query shown below from the Product Table, Name it Inventory Report

Inventory Report
Product ID / Description / On Hand / Cost
BF01 / Barnyard Friends / 3 / 54.00
BL23 / Blocks in Box / 5 / 29.00
CC14 / Coal Car / 8 / 14.00
FT05 / Fire Truck / 7 / 9.00
LB34 / Lacing Bear / 4 / 12.00
MR06 / Midget Railroad / 3 / 31.00
PJ12 / Pets Jigsaw / 10 / 8.00
RB02 / Railway Bridge / 1 / 17.00
SK10 / Skyscraper / 6 / 25.00
UM09 / USA Map / 12 / 14.00

8. Create the query below called Product and Supplier below.

Product and Supplier /
PRODUCT ID / DESCRIPTION / COST / SUPPLIER CODE / FIRST NAME / LAST NAME /
BL23 / Blocks in Box / $29.00 / AP / Antonio / Patino
FT05 / Fire Truck / $9.00 / AP / Antonio / Patino
LB34 / Lacing Bear / $12.00 / AP / Antonio / Patino
UM09 / USA Map / $14.00 / AP / Antonio / Patino
CC14 / Coal Car / $14.00 / BH / Bert / Huntington
MR06 / Midget Railroad / $31.00 / BH / Bert / Huntington
RB02 / Railway Bridge / $17.00 / BH / Bert / Huntington
BF01 / Barnyard Friends / $54.00 / PL / Ping / Luang
PJ12 / Pets Jigsaw / $8.00 / PL / Ping / Luang
SK10 / Skyscraper / $25.00 / PL / Ping / Luang

9. Create the report below from the Query Product and Supplier

and modify it to look exactly like the report below.

.

10. Print the Product table FIGURE 1-82

11. Print the Products Form, for ONE product.

12. Print the Supplier table. Figure 1-83

13. Print the Query Report called Inventory Report

14. Print the Query Report called Product and Supplier

15. Print the Report called Report 2.

Staple the printouts with your name and Section Number in the top right of the first page.

Thank you

2