{"id":4125,"date":"2023-02-22T05:28:17","date_gmt":"2023-02-22T05:28:17","guid":{"rendered":"https:\/\/www.goodacademic.com\/blog\/questions\/it163-database-concepts-using-microsoft\/"},"modified":"2023-02-22T05:28:17","modified_gmt":"2023-02-22T05:28:17","slug":"it163-database-concepts-using-microsoft","status":"publish","type":"questions","link":"https:\/\/www.goodacademic.com\/blog\/questions\/it163-database-concepts-using-microsoft\/","title":{"rendered":"it163 database concepts using microsoft"},"content":{"rendered":"
\n Learning Goal: <\/b>I’m working on a databases question and need an explanation and answer to help me learn.<\/p>\n

Part 1 – Assignment Instructions<\/strong><\/p>\n

Using the model car sample database provided, create queries to demonstrate a variety of simple, single table select query functionality to include selecting more than one field, using the where clause, using alias\u00e2\u20ac\u2122, working with dates, sorting and using relational and logical operators.<\/p>\n

    \n
  1. Open the sample database.<\/li>\n<\/ol>\n

    For this assignment you will use this sample database: IT163_ModelCarSampleDatabase.accdb<\/a><\/p>\n

      \n
    1. Create the five queries as detailed below. You may create these queries using the query design view or you may manually write the SQL code in the SQL view.<\/li>\n<\/ol>\n

      a.<\/strong> Select the Company field from the Customers table.<\/p>\n

      Save as QueryA. Run the query to see the subset of data produced. Verify that the results are correct.<\/p>\n

      Reference:<\/strong> Alexander, M., & Kusleika, D. (2019). Access 2019 bible<\/em>. Wiley.<\/p>\n

      Chapter 8: \u00e2\u20ac\u0153Selecting Data with Queries \/ Creating a Query \/ Working with Query Fields \/ Adding Criteria to Your Queries \/ Saving a Query\u00e2\u20ac\ufffd<\/p>\n

      b.<\/strong> Modify QueryA to sort the output in alphabetical order.<\/p>\n

      Save as QueryB. Run the query to see the subset of data produced. Verify that the results are correct.<\/p>\n

      Reference:<\/strong> Alexander, M., & Kusleika, D. (2019). Access 2019 bible<\/em>. Wiley.<\/p>\n

      c.<\/strong> Select the Description, Cost and QtyInStock fields from the Products table for all records with category \u00e2\u20ac\u0153Trucks.\u00e2\u20ac\ufffd Sort by cost from lowest to highest cost.<\/p>\n

      Save as QueryC. Run the query to see the subset of data produced. Verify that the results are correct.<\/p>\n

      Reference:<\/strong> Alexander, M., & Kusleika, D. (2019). Access 2019 bible<\/em>. Wiley.<\/p>\n

      Chapter 8: \u00e2\u20ac\u0153Selecting Data with Queries \/ Creating a Query \/ Working with Query Fields \/ Adding Criteria to Your Queries \/ Saving a Query\u00e2\u20ac\ufffd<\/p>\n

      d.<\/strong> Select the OrigCustDate, FirstName, and LastName from the Contacts table for all records with the state of Texas (\u00e2\u20ac\u0153TX\u00e2\u20ac\ufffd) and OrigCustDate of January 1, 2013 or later. Display first and last name together as Customer Name.<\/p>\n

      Save as QueryD. Run the query to see the subset of data produced. Verify that the results are correct.<\/p>\n

      Reference:<\/strong> Alexander, M., & Kusleika, D. (2019). Access 2019 bible<\/em>. Wiley.<\/p>\n

      Chapter 9: \u00e2\u20ac\u0153Using Operators and Expressions in Access \/ Introducing Operators \/ Using Operators and Expressions in Queries \/ Using Multiple Criteria in a Query \/ Entering Criteria in Multiple Fields\u00e2\u20ac\ufffd<\/p>\n

      e.<\/strong> Select the Company, CurrentBalance and State from the Customers table for all records with a current balance greater than zero and a state of New York or Connecticut. Sort by CurrentBalance from highest to smallest value.<\/p>\n

      Save as QueryE. Run the query to see the subset of data produced. Verify that the results are correct.<\/p>\n

        \n
      1. Document your results for submission.<\/li>\n<\/ol>\n

        In a Word document, copy each of the questions from above in BOLD. After each question, place the SQL code. After the SQL code, place a screenshot of the successful execution from Access showing the correct results were garnered when you ran each query. Start a new page for each question.<\/p>\n

        Hint: you can copy your SQL code as plain text from the SQL view. Right-click your query and choose SQL View or use the View option from the ribbon.<\/em><\/p>\n

        Example Query Output:<\/em><\/strong><\/p>\n

        IT163_Example_Output_1.docx<\/strong><\/a><\/p>\n

        Assignment Requirements<\/strong><\/p>\n

        Submit a single Word document with all of the documentation for each query as described in the assignment instructions.<\/p>\n

        Part 2 – Assignment Instructions<\/strong><\/p>\n

        You will create a form for the Customer table with a subform for the Customer Contacts table so that data can be entered into both tables at the same time. You will also add controls and apply data validation to several fields in the Customer form. You will also apply encryption to password protect your database.<\/p>\n

          \n
        1. Open the sample database.<\/li>\n<\/ol>\n

          For this assignment you will use this sample database:<\/p>\n

          IT163_ModelCarSampleDatabase.accdb<\/a><\/p>\n

            \n
          1. You will apply encryption to your database and use the password \u00e2\u20ac\u0153PurdueGlobal.\u00e2\u20ac\ufffd<\/li>\n<\/ol>\n

            For extra help see Optional Resources for Assignment under the Academic Tools, Library link:<\/strong><\/p>\n

            Reference:<\/strong><\/p>\n

            Encrypt a database by using a password<\/em><\/p>\n

            https:\/\/support.microsoft.com\/en-us\/office\/encrypt…<\/a><\/p>\n

              \n
            1. Create a form with subform for the Customer (main form) and Customer Contacts tables (subform).<\/li>\n<\/ol>\n

              Reference:<\/strong>
              Alexander, M., & Kusleika, D. (2019). Access 2019 bible<\/em>. Wiley.<\/p>\n

              Chapter 19: \u00e2\u20ac\u0153Working with Form Controls \/ Working with Subforms\u00e2\u20ac\ufffd<\/p>\n

                \n
              1. Name the new form with subform CustomerContactSubForm and save it as a new form object in your database.<\/li>\n
              2. You will add validation and controls for the Customer data as follows. Use the validation rule with validation text to ensure a custom error message is displayed when a rule is violated where appropriate:<\/li>\n<\/ol>\n