Follow these steps this shouldn't take long Open the a02c1Suppliers.xlsx Excel workbook, examine the data, and then close the workbook. Open the a02c1Products.xlsx Excel workbook, examine the data, and then close the workbook. Create a new, blank database named a02c1Foodies_LastFirst. Close the new blank table created automatically by Access without saving it. Import Data from Excel You will import two Excel workbooks that contain supplier and product information into the database. Click the External Data tab, click New Data Source, point to From File in the Import & Link group, and then select Excel. Navigate to and select the a02c1Suppliers.xlsx workbook to be imported as a new table in the current database. Select First Row Contains Column Headings option. Set the SupplierID field Indexed option to Yes (No Duplicates). Select SupplierID as the primary key when prompted and accept the table name Suppliers. Do not save the import steps. Import the a02c1Products.xlsx workbook, set the ProductID Indexed option to Yes (No Duplicates), and then select ProductID as the primary key. Accept the table name Products. 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. Create a New Table You will create a new table that will enable International Foodies to associate each product with a food category in the database. Create a new table in Design view and save the table as Categories. Add the following fields in Design view and set the properties as specified: Add the primary key field as CategoryID with the Number Data Type and Number assigned to a new category. (type the period) as the Description. Set the Caption property to Category ID. Add CategoryName with the Short Text Data Type and Name of food category. (type the period) as the Description. Change the field size to 15. Set the Caption property to Category Name and the Required property to Yes. 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 a later step. Close the table. Create Relationships You will create the relationships between the tables using the Relationships window. Add all three tables to the Relationships window. Identify the primary key fields in the Categories table and the Suppliers table and join them with their foreign key counterparts in the related Products table. Select the Enforce Referential Integrity and Cascade Update Related Fields check boxes. Save and close the Relationships window. Add Data to the Categories Table You will add 8 records to the Categories table so that you have some sample data to test in the database. Add the following records to the Categories table: 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 Close the table. Use the Query Wizard You will use the Simple Query Wizard to create a query of all products that you import in the seafood category. Add the ProductName, SupplierID, and CategoryID fields from Products (in that order). Save the query as Seafood Products. Add a criterion in Design view, to include only products with 8 as the CategoryID. Sort the query results in ascending order by ProductName. Run, save, and close the query. Copy and Modify a Query in Design View 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. Copy the Seafood Products query and paste it using Seafood Or Meat/Poultry as the query name. Open the Seafood Or Meat/Poultry query in Design view and delete the CategoryID column. 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. Create a Multitable Query You will create a query that identifies suppliers and their associated products. Because there is a relationship between the two tables, you can now pull data from each of them together as usable information. Create a query in Design view that includes the Suppliers and Products tables. The query should list the company name, contact name, phone number (in that order), then the product name and the product cost (in that order). Sort the query by company name in ascending order, then by product cost in descending order. Run, close, and save the query as Company by Product List. Use a Total Row to Summarize Data in a Query You determine that the data in the Company by Product List query could be summarized with a Total row. You will group the records by company name and count the number of products you buy from each of them. Copy the Company by Product List query and paste it using Summary of Company by Product as the query name. Open the Summary of Company by Product query in Design view and delete the ContactName, Phone, and ProductCost columns. Click Totals in the Show/Hide group on the Query Tools Design tab. Click in the Total row of the ProductName field, click the arrow, and then select Count. The records will be grouped by the company’s name and the products for each company will be summarized. Modify the field name of the Title column as Product Count: ProductName to make the field name more identifiable. Click Run in the Results group (20 records display in the Datasheet). The results display the product count for each company that supplies your organization. Save and close the query. Close the database and exit Access. Based on your instructor’s directions, submit a02c1Foodies_LastFirst.
CS 340 Milestone One Guidelines and Rubric Overview: For this assignment, you will implement the fundamental operations of create, read, update,
Retail Transaction Programming Project Project Requirements: Develop a program to emulate a purchase transaction at a retail store. This
7COM1028 Secure Systems Programming Referral Coursework: Secure
Create a GUI program that:Accepts the following from a user:Item NameItem QuantityItem PriceAllows the user to create a file to store the sales receip
CS 340 Final Project Guidelines and Rubric Overview The final project will encompass developing a web service using a software stack and impleme
