INF 110 In-Chapter Access Chapter 2

INF 110 In-Chapter Access Chapter 2
(Word Document named In-ChapterAccess2LastName.docx)
(Create a Word document containing the Access objects of each Your Turn projects)
(Note: This is required because security issues will not allow you to send an Access database as an email attachment) (Be sure to watch the videos in the Access 2 PowerPoint, they show you how to do all of these steps) (Download VLC Video Player) 

Your Turn! (see slide 23 in Access 2 PowerPoint instead of page 30 in book)
Creating Students and HomeWork_Results tables
(Create two tables named Students and HomeWork_Results)
             Use the following set of directions as a guideline: (See video) (Download video)

1.      Type in the fields and records for the student table on slide 23 by creating a database named (Access Essentials.accdb)

2.      See handout. to get the data for the field structure and the records for the tables

3.      After typing in all the data from the handout, Open each table in Datasheet view

5.      Copy the two tables into the Word document listed above by using the following steps:

Note: Office 2013 users, the software will not let you change the Check box to a Text box as indicated on the video for the Homework_Completed field in the HomeWork_Results table. Just leave that field as check box with Yes/No data type.

(Access 2003 users then click Tools, point to Office Links, point to Publish it with Microsoft Office Word.)

(Access 2007 users click External Data Tab, click the Word Icon, click the OK command Button after selecting a path for the file on the 1st dialog box, click the Close command Button on the 2nd dialog box.) Two new Word documents will open called Students.rtf  and HomeWork_Results.rtf .
Copy and paste these tables into your original Word document. (In-ChapterAccess2LastName.docx)

(Access 2010&2013 users click External Data Tab, click the More Button down arrow, click the Word Icon, click the OK command Button after selecting a path for the file on the 1st dialog box, click the Close command Button on the 2nd dialog box.) Two new Word documents will open called Students.rtf  and HomeWork_Results.rtf .
Copy and paste these tables into your original Word document. (In-ChapterAccess2LastName.docx)

Your Turn! (see slide 28 in Access 2 PowerPoint instead of using the tables on page 32 in book)
Creating a link between the Students and HomeWork_Results tables
 (Create a Relationship named Relationships) (Use pages 31 and 32 as guide)

  1. Close all tables from the previous step listed above.
  2. Click the Database Tools Tab on the Ribbon (see Figure 2.7 on page 31)
  3. Click the Relationships Icon. (see Figure 2.7 on page 31)
  4. The Show Table dialog box should now display. (see Figure 2.8 on page 32) (Access 2010&2013 users might need to Click the Design Tab under Relationship tools)
  5. Double-Click on each table to display the Students and HomeWork_Results tables to be linked. (see Figure 2.9 on page 32)
  6. Click the primary key from one table and drag it to the foreign key of the other table.
    (Drag the Student_Id field from the Students table to the Student_Id field in the HomeWork_Results table)
    The Edit Relationship dialog box will appear. (see Figure 2.10 on page 32)
    1. Enter a check mark in the Enforce Referential Integrity check box and the other two boxes (see slide 28)
    2. Click the Create command button to create the one-to-many link. (see slide 28)
  7. Use the Alt-PrintScreen keys to copy the relationship screen-shot into the Word document under the two tables.
  8. Open the Students table in Datasheet view, and click on the + icon to see the relationship of the homework to each student. (see slide 29)
Your Turn! (see pages 37-41 in book and slides 34 and 35 in Access 2 PowerPoint)
Creating a bound Manage Students form from the Students table (See video) (Download video )
 (Create a Form named Manage Students)
  1. Create the Manage Students form following the steps on pages 37-41
  2. Bound Manage Students form to the Students table using pages 37-38 (see page 39 of the book Figure 2.19) (See video on Slide 33 to see how to create form)
  3. Use the following link (Download Teacher Graphic) on the assignments web page to get the teacher sitting at the computer graphic
  4. See the animated text boxes on slide 35 to see how to use the Code Wizard to add the code for the Quit Application button.
    Also see page 41 of the book Figure 2.20     for the Command button Wizard dialog box.
  5. Run the Form
  6. Use Alt-PrintScreen keys to copy a screen-shot of the Form to the clipboard, paste this screen-shot into the Word document
  7. Use Alt-F11 keys to go to the code window for the form, copy and paste this code into the Word document
Your Turn! (see pages 43-44 in book and slide 45 in Access 2 PowerPoint)
Creating a query of all the student names in the Students table
 (Create a Query named Retrieve Student Names)
  1. Create the query using the steps on pages 43-44 (see Figures 2.22, 2.23)
  2. See slide 50 for the results of the query instead of Figure 2.24 in the book
  3. Open the query in data sheet view
  4. Copy the query into the Word document listed above by using the following steps:

(Access 2003 users then click Tools, point to Office Links, point to Publish it with Microsoft Office Word.)
(Access 2007 users click External Data Tab, click the Word Icon, click the OK command Button after selecting a path for the file on the 1st dialog box, click the Close command Button on the 2nd dialog box.) A new Word document will open called Retrieve Student Names.rtf . Copy and paste this query into your original Word document. (In-ChapterAccess2LastName.docx)
(Access 2010&2013 users click External Data Tab, click the More Button down arrow, click the Word Icon, click the OK command Button after selecting a path for the file on the 1st dialog box, click the Close command Button on the 2nd dialog box.) A new Word document will open called Retrieve Student Names.rtf . Copy and paste this query into your original Word document. (In-ChapterAccess2LastName.docx)

Your Turn! (see slide 54 in Access 2 PowerPoint instead of using the tables on page 45 in book)
Creating a query to find all student names and grades for homework #2
(Create a Query named Homework #2 Results)
  1. Create the query using the steps on pages 45 as a guideline
  2. Substitute the tables, fields, and conditions shown on slide 54 for those in Figure 2.25 on page 45 in the book
  3. See slide 54 for the results of the query instead of Figure 2.26 on page 46 in the book
  4. Open the query in data sheet view
  5. Copy the query into the Word document listed above by using the following steps:

(Access 2003 users then click Tools, point to Office Links, point to Publish it with Microsoft Office Word.)
(Access 2007 users click External Data Tab, click the Word Icon, click the OK command Button after selecting a path for the file on the 1st dialog box, click the Close command Button on the 2nd dialog box.) A new Word document will open called Homework #2 Results.rtf . Copy and paste this query into your original Word document. (In-ChapterAccess2LastName.docx)
(Access 2010&2013 users click External Data Tab, click the More Button down arrow, click the Word Icon, click the OK command Button after selecting a path for the file on the 1st dialog box, click the Close command Button on the 2nd dialog box.) A new Word document will open called Homework #2 Results.rtf . Copy and paste this query into your original Word document. (In-ChapterAccess2LastName.docx)

(Students should submit the electronic versions of the database ( (Access Essentials.accdb) and the Word File (In-ChapterAccess2LastName.docx) using the drop box by clicking on on the InChp7 link under the Assignments link. In-Chapter work from this chapter (Access Essentials.accdb), the objects in the In-Chapter database file should use the following name (Students and HomeWork_Results tables, Relationships link, Manage Students form, Retrieve Student Names and Homework #2 Results queries), substitute your last name for LastName listed in file names above)