In: Computer Science
Exp19_Access_Ch02_Capstone - International Foodies 1.0
Project Description:
International Foodies is an importer of exotic foods from all over the world. You landed a summer internship with the company and discovered that their product lists and the suppliers they buy from are stored in Excel workbooks. You offer to help by using your newly gained knowledge of Access to create a relational database for them. You will begin by importing the workbooks from Excel into a new Access database. Your manager mentions that she would also like a table that specifies food categories so that you can relate the products you sell to specific categories in the database. You will create a table from scratch to track categories, create relationships between the tables, and create some baseline queries.
Steps to Perform:
Step |
Instructions |
Points Possible |
1 |
Start Access. Open the downloaded Access file named Exp19_Access_Ch2_Cap_Foodies. Grader has automatically added your last name to the beginning of the filename. Save the file to the location where you are storing your files. |
0 |
2 |
You will examine the data in the downloaded Excel worksheets to
determine which fields will become the primary keys in each table
and which fields will become the foreign keys so that you can join
them in the database. |
10 |
3 |
Import the Products.xlsx workbook, set the ProductID Indexed option to Yes (No Duplicates), and select ProductID as the primary key. Accept the table name Products. |
10 |
4 |
Change the Field Size of the QuantityPerUnit field to 25 in Design view of the Products table. Set the Field Size of ProductID and CategoryID to Long Integer. Save the changes and open the table in Datasheet view. Open the Suppliers table in Datasheet view to examine the data. Close the tables. |
4 |
5 |
You will create a new table that will enable International
Foodies to associate each product with a food category in the
database. |
6 |
6 |
|
6 |
7 |
Add CategoryDescription with the Long Text Data Type. Set the Caption property to Category Description. Switch to Datasheet view and save the table when prompted. You will enter Category data into the table in the next step. |
4 |
8 |
Category ID Category Name Category Description 1 BEVERAGES SOFT DRINKS, COFFEES, TEAS 2 CONDIMENTS SAUCES, RELISHES, SEASONINGS 3 CONFECTIONS DESSERTS, CANDIES, SWEET BREADS 4 DAIRY PRODUCTS CHEESES 5 GRAINS/CEREALS BREADS, PASTA, CEREAL 6 MEAT/POULTRY PREPARED MEATS 7 PRODUCE DRIED FRUIT, BEAN CURD
8 SEAFOOD SEAWEED
AND FISH |
6 |
9 |
You will create the relationships between the tables using the
Relationships window. |
12 |
10 |
You will use the Simple Query Wizard to create a query of all
products that you import in the seafood category. |
10 |
11 |
Add a criterion in Design view, to include only products with 8 as the CategoryID. |
2 |
12 |
Sort the query results in ascending order by ProductName. Run, save, and close the query. |
2 |
13 |
You want to create a query that displays actual category names
rather than the CategoryIDs. You are interested to know which meat
and poultry products are imported. You will copy the Seafood
Products query and modify it to delete a field, then add an
additional table and field. |
2 |
14 |
Open the Seafood Or Meat/Poultry query in Design view and delete the CategoryIDcolumn. |
2 |
15 |
Add the Categories table to the top pane of the query design window. Add the CategoryName field to the last column of the design grid and set the criterion as "Seafood" Or "Meat/Poultry". Run, save, and close the query. |
4 |
Suppliers.xlsx and Products.xlsx in this two workbook did not provide in the question in this cause it is not to posible to implement the Access workbook file.
So this cause Asumping this two excel table file and implimented in the Access file
Asumption Suppliers.xlsx file =
Suplier Id | Suplier Name | Suply Items | Suply Form |
1023 |
D_ Das | Grossory | Local Market |
1025 | M_Moriaty | Medicins |
Local Pathology |
1026 | S_Shvastav | Dry Food |
Loacal Bazar |
Asumption Products.xlsx file =
Product Id | Quantity Per Item | Catagory Id | Supliers Id |
205 | 10 Packets | 1 | 1023 |
208 | 100 Packets | 3 | 1025 |
209 | 50 Packets | 5 | 1026 |
1)
2) Importing Two Excel files
i)Goto External Data -> Then Click Import to Excel Spred Sheet option -> Brows the file -> Enable the option Import the source data into the new table and corrent Database -> click OK -> Click Next -> Enable the option First row is colum Heading.
iii) Set Field Suplier Id No duplicates -> Click Next
iv) Choose the Suplier Id As a Primery Key -> click Next
v) Set the Name of Table and Finish
In This same way to import the Products Table.
4) Goto Design view tab And change the Catagory Id Data type ia long integer
Datsheet View of Suplier
Datasheet View of Products
5) Creating New Table
Goto Insert Tab -> Click Table Design -> Fill the Design View Tab ->
Set Primary Key Catagory ID and Click Save with the table name Categories.
8) Fill the Data then table look like
9) Goto Database Tools tab and Click Relationship option
Add three table
10) Go to Query Wizerd in create tab Click Simple querry wizard and click Ok
Add table filds and click Ok then the Query is created
Use Sort and filter option to Sort the value of query results in ascending order by ProductName.
Next query is the same method using to Run