D. Nixon: CS 12 > Week 6

Home (CS 12) | Assignments | Tutorials | Examples | Quizzes & Exam | Other Info | Chat

Computer Science 12


Week 6




Sunday, March 8, 2020



Assignment 6 (due next week)

  1. Access assignment (due next week, March 15, 2020)
  2. Scratch assignment (due next week, March 15, 2020)
  3. Paper on problem-solving, Draft 2 (due March 22, 2020)

  1. Access assignment (due next week):
  2. Following the detailed instructions below, create a single-table database which will also have a form, a query, and a report. The table will list employees. The query and the report will list "senior employees," a subset of the employees in the original table.

    1. Creating a table in Access:
    2. Open Microsoft Access 2013 or 2016. Double-click "Blank desktop database," which will be one of the large icons on the right. (These icons represent commonly-used types of databases.) You will be shown a blank new table.

      First let's give the table an appropriate name. Click the "File" tab, then click "Save." In the "Save As" dialog box that pops up, change "Table1" to "Employees."

      Let's now create ("design") appropriate fields (columns) for this table. At the top of the main panel is a tab marked "Employees". Right click that tab and select "Design View." You'll now see a new table whose purpose is to define fields for the original table. To go back to the original table, click the "Employees" tab again and select "Datasheet view." Then go back to "Design View" again, and toggle back and forth between the two views until you see how they relate to each other.

      Notice that one field has already been defined for us: A field named "ID," whose data type is "AutoNumber." This field will automatically generate unique ID's for all the employees in our table.

      In "Design View," let's now define the following additional fields, as explained below:

      • A field named "Last name," of data type "Short Text"
      • A field named "First name," also of data type "Short Text"
      • A field named "Date of birth," of data type "Date/Time"
      • A field named "Salary," of data type "Currency."

      To define each of the above four new fields, type its name in the "Field Name" column of the "Design View" table, and select its data type from a drop down list.

      Then right-click the "Employees" tab at the top of the table and select "Save." Then right-click the "Employees" tab again and select "Datasheet View."

      Now enter data for 10 employees. Make up their names, birth dates, and salaries. Note that the ID is automatically filled in for you. Choose birth dates such that about half the employees are over 30 years old and the other half are under 30. For both age ranges, choose salaries such that some are earning more than $50,000 (per year) and others are earning less than $50,000.

      Then right-click the "Employees" tab and click "Save."

    3. Creating a form:
    4. Let's now create a form which will enable more data to be entered into the table in a more user-friendly fashion, one new employee at a time.

      At the top of the window, select the "Create" tab, Then click "form." At first, the form will display the data for your first employee. So that you can use it to enter data for new employees, hover the mouse over the little arrow icons at the bottom of the form and click the one that says "New (blank) record" when you hover over it You'll then see the fields all go blank except for "(New)" in the ID field..

      The form has the name "Employees," same as the table. Let's change its name to "Employee" (singular), to distinguish it from the table. To do this, right click the "Employees" tab for the form (not the table) and select "Save" and then, in the "Save As" dialog box that pops up, delete the final 's' from the word "Employees."

      The form still has a heading at the top, just below the tabs, that says "Employees." To change that, simply double-click on that heading and delete the final 's' from the word "Employees," and then press the [Enter] key.

      Right-click on the "Employee" tab again and observe the available views: "Form view," "Layout view," and "Design view." The "Form view" is intended for entering data, whereas the other two views are intended for changing the appearance of the form. By default, when you create a new form, you are initially in "Layout view."

      Select "Form view" so you can enter data. Then, if you have not done so already, hover the mouse over the little arrow icons at the bottom of the form and click the one that says "New (blank) record" when you hover over it.

      The cursor will now be on the "(New)" in the ID field. Since that field is auto-generated, you can't put a value there yourself. So, just press Enter to move your cursor to "Last Name".

      Enter data for a few new employees.

      Then right-click the "Employee" tab and select "Save".

      Then click the "Employees" tab to go back to the "Employees" table. At first, you won't see the new employees. One way to make them appear is to close the "Employees" table (by right-clicking the "Employees" table and selecting "close") and then re-open the table (by double-clicking "Employees" in the left panel).

    5. Creating a query:
    6. Let's now use our table to look up some information. Suppose we want a listing of all the employees over 30 years old who are earning at least $50,000. Given how short our table is, it's not too difficult to do this by hand. But, if we had a lot more employees, looking up this information by hand could be quite tedious. So, let's use a query to automate our search.

      At the top, click the "Create" tab, then select "Query Wizard." Select "simple query wizard" and click "OK."

      First, we are asked to select which fields will appear in our query result. Select all the fields. Then click "Next." Then select "Detail" and click "Next."

      In the next dialog bax, you are asked what name you want for the query, with default name "Employees query." Change the name to "Senior employees." Then select "Modify the Query Design" and click "Finish.".

      At the bottom, in the "Criteria" row, in the cell in the "Date of birth" column, enter "<" followed by a date 30 years ago. Also in the "Criteria" row, in the cell in the "Salary column, enter ">=" followed by the number 50000. (Do not use a dollar sign, and do not use a comma.)

      Then right-click the "Senior employees" tab and select "Datasheet view." The quary results should then be generated and displayed. You should see only those employees who fit your criterial If you get an error message, see Examples of query criteria on the Microsoft site.

      Now save the query. Right click the "Senior employees" tab and click "Save."

    7. Creating a report:
    8. Let's now create a report to display our query results in a customizable fashion.

      At the top, click the "Create" tab, then select "Report." Then play around with various features to see how they change the appearance of your report.

      Then save your report.

    9. Saving your database:
    10. After you've saved and then closed all the individual objects in your database, save your database itself. Click the "File" tab, then select "Save As," then select "Save Database As" and give your database a filename like the following:

      last-first-employees.accdb

      replacing "last" and "first" with your own last and first name.

    Submit the above file via email with a subject line of

    HW 6 Access, LastName FirstName

    where "LastName" and "FirstName" should be replaced by your own last name and and first name, respectively.

    Warning:  You must follow the homework submission instructions EXACTLY in order for your homework to be graded at all. If you do not use the correct filenames, or if you do not use the correct subject line, or if you submit the files attached to separate email messages rather than as three attachments to just one email message, then your homework will not be graded at all. These rules are necessary in order for me to keep track of your homework emails.

  3. Scratch assignment (due next week):
  4. Review the Scratch tutorial titled "Create A Pong Game". See Create A Pong Game for clarifications on some issues.

    Then do the following additional tutorials:

    Save your work in files with appropriate filenames, as follows:

    where "last" and "first" must be replaced by your own actual last name and first name. Each file must contain the results of working through the named tutorial.

    Then send both files attached to ONE email with the following subject line:

    HW 6 Scratch, LastName FirstName

    where "LastName" and "FirstName" must be replaced by your own last name and and first name, respectively. Be sure to attach all three files to ONE email message. Do NOT submit the three files in three separate emails.

    Warning:  You must follow the homework submission instructions EXACTLY in order for your homework to be graded at all. If you do not use the correct filenames, or if you do not use the correct subject line, or if you submit the files attached to separate email messages rather than as three attachments to just one email message, then your homework will not be graded at all. These rules are necessary in order for me to keep track of your homework emails.

  5. Paper on problem-solving, Draft 2 (due March 22, 2020)
  6. Write parts 2 and 3 of your paper, describing how to think through the process of solving various kinds of Excel problems, using last week's Excel problems as examples. The example for Part 2 should be the invoice with discount, and the example for Part 3 should be the sales force ranking problem. Among other things, show possible ways to break each problem down into simpler parts and gradually work your way up to the complete solution.

    Draft 2 of the paper will have three parts, each devoted to a distinct problem. Note that parts 2 and 3 do NOT need to be in any way related to the problem you solved in part 1. Rather they should be presented as separate, mutually independent examples of how to think through the process of solving problems in Excel.

    To get started thinking in-depth about problem-solving, review your notes on last week's Excel lectures. Also review the tutorials on solving math word problems and last week's tutorilas on Excel and programming. When reading each of the Excel tutorials, look first at the example problems and try to figure out the correct formulas yourself before reading the solution in the tutorial.

    You will be expected to use Microsoft Word to write your paper. Some tutorials on Microsoft Word are provided.

    Parts 2 and 3 of the problem-solving paper should each be between 1 and 2 pages long. (Together these two parts should be between 2 and 4 pages long.) They must be appended to Part 1, which should be edited in accordance with any feedback you've received on it. The three parts must each have section headings.

    Your entire paper so far (parts 1, 2, and 3) should be divided into 3 main sections, each with sub-sections. The three main sections should be devoted to (1) the problem you solved in part 1, (2) the invoice with discount, and (3) the sales force ranking problem. Each of the three main sections should be divided into a few sub-sections, each devoted to some aspect of the problem or a step in solving it.

    The paper, which will be due two weeks from now, must be a Microsoft Word document with a filename with the following format:

    where "last" and "first" should be replaced by your own last and first names. Include also the spreadsheet for part 1, which must have a filename with the following format:

    again replacing "last" and "first" with your own actual last and first names. Submit both files together in a single email message with a subject line like the following:

    HW 7 Paper draft 2, LastName FirstName

    where "LastName" and "FirstName" should be replaced by your own last name and and first name, respectively.


To all information for Week 6  |  To all homework assignments



Excel tutorials

On nested  IF  functions, and  AND  and  OR. 

Review the the Excel tutorials listed for Week 5. See especially the section titled "13. Limit IFs with AND and OR" in 19 tips for nested IF formulas on the ExcelJet site. You will be responsible for knowing, on quizzes and the final exam, when and how a nested  IF  can be simplified using  AND  or  OR.

Excel 2016's  IFS  function.

Excel 2016 has a new  IFS  function, a simpler substitute for nested  IF/ELSE.  See:

Note that the  IFS  function is new as of Excel 2016 and will not work on older versions of Excel. Hence it should not be used in spreadsheets you intend to share with people who may have older versions of Excel. Also, it might not be supported on your own computer if you have Excel 2013 or earlier. Since you are not required to have the latest version of Excel (as long as you have at least Excel 2010), there will be no homework assignments involving the  IFS  function. However, you ARE required to understand it and be able to write a correct  IFS  function on a quiz or the final exam. If you don't have Excel 2016 at home, it is recommended that you get some practice with it after class or in a public lab on campus.

Pay particular attention to the use of  TRUE  as the last condition in some  IFS  tutorial examples. What is the purpose of doing that?


To all information for Week 6  |  To all tutorials about Excel



New topic: Intro tutorials on databases, using Access

    Preliminary introductions to databases (in general, not just Access):

    Preliminary introductions to database concepts, using Access:


To all information for Week 6  |  To all tutorials about databases and Access



Tutorials on Scratch

Review the following, in Notes on some Scratch tutorials:

Then see Additional tutorials on Scratch, especially the following:


To all information for Week 6  |  To all tutorials about programming (very basic intro)



Tutorials on Microsoft Word

Below are some general tutorials on Microsoft Word:


To all information for Week 6  |  To all tutorials about Word



Study guide for next week's quiz

THIS QUIZ WAS CANCELED due to the CoViD-19 crisis. However, the material mentioned below is still fair game for subsequent quizzes, and for the final exam.

Quiz 6, to be given on Sunday, March 15, was going to include the following:

To prepare for the Excel part of the quiz, the main thing you will need is practice using all the above-named Excel functions -- not just using them in a spreadsheets, but also thinking through various problems involving them. To that end, it is recommended that you play with examples in the tutorials. Make sure, especially, that you are comfortable with with nested IF functions and logical expressions involving AND and OR. Among other things, be prepared to recognize what kinds of nested IF expressions can be converted to simpler IF expressions using AND and OR. At the same time, learn to recognize and avoid unnecessarily complicated, redundant logical tests within a nested IF or within an IFS).

To prepare for the database portion of the quiz, review your notes from last week's class and read the database tutorials. It is also recommended that you do Google searches on each of the database terms in conjuction with the word "database," i.e. do searches on "database record," "database key," etc.


To all information for Week 6  |  To all quiz and exam study guides

D. Nixon: CS 12 > Week 6