SECOND DELIVERABLE INSTRUCTIONS

Please make note of the following items:

1) Use the Access database posted with the other second deliverable items to complete this deliverable. Do not use the Access database you created and submitted as part of the first deliverable. Do not use the example Access database with queries posted for Chapter 4 under Course Materials. Download and save the Access database posted with the other second deliverable items as LastNameFirstInitial_AccessAssignment_SecondDel.accdb.

2) This set of instructions builds upon the activities performed in the first deliverable instructions. Because of this, table and figure numbers used in this second deliverable are numbered in continuation of those used in the first deliverable instructions.

3) On average, the second deliverable takes about twice as long to complete as the first deliverable.

4) A Word document cover sheet is posted along with these instructions. Consistent with the first deliverable instructions, use the cover sheet document to capture screenshots and then answers to questions. Also consistent with the first deliverable instructions, take, place and title screenshots on separate pages located after the cover sheet and place answers to questions on a separate page located after the last screenshot. The cover sheet is two pages in length and there are four screenshots to be taken and questions to answer, so your output will be seven pages in length. No grade will be earned if the cover sheet document is not used for gathering your output.

5) Name your Access database as instructed in this deliverable. Name your Word file for this deliverable as LastNameFirstInitial_AccessAssignment_SecondDel.docx.

6) Upload items (Word document and completed database) by the due date and time shown on the schedule.

7) Consistent with the first deliverable instructions, the deadline for sending emails with questions about the second deliverable is 12pm the day the assignment is due .

8) See the schedule for class date in which class time has been set aside for working on the second deliverable.

Please proceed to the next page for Part 4 of the assignment.

Page | 26

PART 4

CREATING ENTRY FORMS

This section of the assignment illustrates how to create an entry form for ease and efficient entry of data into a database. You will first populate the master files with data, and then create a form for entering transaction data. Open the database posted with the other second deliverable items under Assignments.

4.1 Populate Master File Tables

In this section, we will use the information in Table 2 to populate ONLY the following three tables (all of which are master files, with the first and third being agent files and the second being a resource file):

· Employee

· Inventory

· Customer

The invoice and invoice line tables (both of which are transaction or event files) will NOT be populated until Section 4.3 of these instructions.

You can populate a table by either entering the data directly into the table or by using a user-friendly data-entry form. Using a form is preferable because, if designed properly, it can reduce the number of data entry errors (i.e., make data more accurate) and make data entry a timelier process.

Access gives you three ways to create a form:

1. Use the default form provided by Access.

2. Use the form wizard to design a form. This gives you control over what the form will look like.

3. Use the blank form tool to design a form. This lets you build a form from scratch.

We will populate the three master file tables by entering the data directly into the tables. We will design a form later using the form wizard for entering sales transactions into the invoice and invoice line tables.

From the Tables menu on the left, double click on the employee table to open. You should be in the Datasheet View. If any field captions are truncated in the table, you can widen these columns the same way you do in both Excel and Word (see how all column headings are fully visible in Figure 11).

Enter the data from Table 2 into the table, tabbing from field to field. Table 2, which was first referred to in the first deliverable Word document of tables and figures, is also included in the second deliverable Word document of tables and figures so you would not have to go back to the first deliverable Word document. When entering the commission rate, note that the field is formatted for percentages. Therefore, you do not enter decimal values for any of the three employees, but just number values.

Access automatically opens a new record when you tab out of the last field of a record. If you touch the tab key too many times and end up with a blank record or make a mistake and want to just delete the record and start over, do the following: the record must have some contents to be deleted – even if you just make up something. Then you can highlight the record by right clicking and select delete record.

Save after entering the data and close the employee table.

Populate the inventory and customer tables just as you populated the employee table, using the data in Table 2. Save and close the respective tables after populating them.

4.2 Create Form to Enter Invoice Transaction Data

In this section, you will create an invoice form. Consider the following purposes of the form:

1. The form is a data entry screen of sales made to customers.

2. The form can also be used to generate sales invoices to provide to customers.

QUESTION #1 TO BE FORMALLY ANSWERED IN OUTPUT TURNED IN FOR DELIVERABLE #2: In your Word document, type “Response to Question #1:” and formally answer the following question:

Which step in the data processing cycle (see Chapter 2 of your course textbook) is emphasized by the first purpose of creating a form in this assignment? Which step in the data processing cycle is emphasized by the second purpose of creating a form in this assignment? Explain why for both answers.

In this assignment, you will create the invoice in rough form. Further study of Access can provide you with skills to improve the appearance and functionality of the form. The goal here is to demonstrate that you have the skills to integrate database concepts and create an invoice form similar to that used in many common accounting software packages.

4.2.1 Multi-table form – main form and subform

Many database forms, including those used in an AIS, interact with more than one table. The invoice form you will create is an example of a multi-table form because some of the data will involve the invoice table while other data will involve three tables related to the invoice table: employee, customer, and invoice line. Moreover, additional data comes from the inventory table that is indirectly related to the invoice table through the invoice line table (see Figure 12 for these relationships).

The main form is usually the one side of a one-to-many relationship. In this case, the invoice table is the one side of the relationship between the invoice and the invoice line tables (see Figure 12).

Some field names appear in more than one table, e.g., INVNO appears in both the invoice and invoice line tables. Therefore, it is important that the main portion of the invoice form derives fields from the correct tables. For each invoice form to contain unique information, the main portion of the invoice form must derive the invoice number from the invoice table, and NOT from the invoice line table. INVNO is the invoice table’s primary key; therefore, if the main form derives the invoice number from the invoice table, each invoice form will contain data about each unique invoice.

In addition, the main portion of the form gets EMPCODE and CUSTCODE data from foreign key fields in the Invoice table and NOT from the primary key fields in the employee table or customer table. If your main form tries to derive the CUSTCODE from the customer table, Access will not know which customer you are referring to when entering an invoice.

Because the main form will derive its data from the one side of the one-to-many relationship, the main form information is nonrepeating, e.g., there is only ONE invoice number, date, employee code, and customer code for EACH invoice form.

Because the subform will derive its data from the many side of the one-to-many relationship (the invoice line table), the subform may have multiple lines, with each line representing a specific product sold on a particular invoice. The subform will derive product number and quantity sold from the invoice line table and product description and unit price from the inventory table. Figure 12 shows what tables provide data to the main form, and what tables provide data to the subform.

To begin creating your form, perform the following:

1. Select the Create ribbon → Form Wizard. You should see a form similar to Figure 13.

2. In the Tables/Queries text box, select Table:Invoice.

3. click the double chevron (>>) to add all four fields to the Selected Fields box.

4. Do not select Next yet. (If you already have, just select the Back button.)

Procedural Comment

You have now established Table:Invoice as THE source for the following data: INVNO, InvDate, EmpCode, and CustCode. If you derive these fields from any other table, the form will not work properly.

The Access form wizard places fields on the form in the order that they are selected, so a little forethought about how you want the form to look would be useful. While you can change the appearance of a form after it has been created (this is done later in these instructions for showing how changes can be made), planning can reduce the need to make edits later.

5. Within this same form wizard screen, select Table:Employee from the top-left pull-down menu, and add EmpLastName and CommRate to the form.

Practical Business Comment

In a real business situation, both the first and last name of the employee/sales agent would likely appear on the invoice, as a reference point to the customer. We are including only the last name in order to simplify the creation of the invoice form. In addition, commission rates are not likely applicable to show on an invoice that will be given to a customer because that information is likely of no use to a customer (or the seller would not want the customer to know the commission rate because the sales price is set to allow commissions and a profit). We are adding the commission rate strictly for instructional purposes so that students (and instructor) can review the form to see that the proper sales representative data is pulled from the underlying table.

6. Within the same screen, select Table:Customer, and add CustOrgName and CustCity.

7. Within the same screen, select Table:InvoiceLine. Add PRODNO and QtySold.

8. Within the same screen, select Table:Inventory, and add ProdDesc and UnitPrice.

9. Click Next. You should see a screen that looks similar to Figure 14.

QUESTION #2 TO BE FORMALLY ANSWERED IN OUTPUT TURNED IN FOR DELIVERABLE #2: In your Word document, type “Response to Question #2:” and formally answer the following question:

Of the characteristics of information in Table 1-1 of your textbook, identify the one that is most applicable to the discussion under “Practical Business Comment” on the previous page regarding whether it is applicable to include the commission rate on the invoice. Explain why.

SCREENSHOT #1 TO BE PROVIDED IN OUTPUT TURNED IN FOR DELIVERABLE #2: Make a screenshot of the Form Wizard at this point in the assignment and place in your Word document. Before the screenshot, provide the title “Screenshot #1 – Form Wizard in Progress”.

Before you proceed, give some thought as to how you want the data to appear. By default, “by Invoice” is selected because Access knows that the invoice table is the one side of a one-to-many relationship with the invoice line table. But you can look at the alternative views by clicking on the tables in the left pane and viewing how the information will appear in the right pane of the Form Wizard. Go ahead and do so to see what alternative views can be chosen.

What format makes the most sense, i.e., is most understandable (one of the information characteristics from Table 1-1)? You would probably want the invoice number, invoice date, and other invoice table information at the top of the form. Moreover, the invoice table is the one side of the one-to-many relationship, so it is logical that the invoice form is organized with the invoice table data at the top of the form. Therefore, you would want to view your data “by Invoice.” If not already on this view, select it. Doing so will instruct Access to define the invoice table as the main section of the form and invoice line table as a nested component of the main form. This is consistent with prior discussion and reference to Figure 12.

10. Click Form with subform(s) (if not already selected) → Next.

11. Select Tabular for the subform layout, then Next.

12. Change the title of the form to InvoiceMainForm .

13. Change the title of the subform to InvoiceLine Subform (if not already entered by default).

14. Select Open the form to view or enter information → Finish. Your form should look similar to Figure 15. You may have to use the scroll bar in the InvoiceLine subform to see the unit price field. Later in this tutorial, we will modify the form and subform so that all fields are visible on-screen.

Note that you are automatically in Form View, as opposed to the Design View. All fields are empty at this time because you have not yet entered the sales transaction data shown in Table 3.

Note that the main invoice form is located in the upper half, and the invoice line subform is located in the lower half of the screen. Navigating buttons are at the bottom of both the subform and the main form (see Figure 15). The upper set of buttons moves you through the subform of an invoice from one invoice line to another. The lower set moves you from one invoice to another.

4.2.2 Viewing the Underlying Query of the Form

Using the Form Wizard, you have performed join and project operations on the five tables to create your form. The join operation connects two or more tables based on primary and foreign key relationships. The project operation displays selected information from tables. These join and project operations are performed by an underlying query that was created by the Access wizard. To view the query:

1) Open the InvoiceMainForm in Design View

2) In the Form Design ribbon, select Property Sheet from the Tools group.

3) In the Selection Type box of the Property Sheet, select Form

4) Select Data tab. If need be, modify the size of your property sheet so that it is not taking up too much space on your screen (see the size shown in Figure 16).

5) click the build button  to the right of the Record Source.

Your underlying query should agree with that shown in Figure 17.

SCREENSHOT #2 TO BE PROVIDED IN OUTPUT TURNED IN FOR DELIVERABLE #2: Make a screenshot of the query at this point in the assignment and place in your Word document. Before the screenshot, provide the title “Screenshot #2 – Query behind Invoice Form”.

Close screens until you get back to the Design View for the InvoiceMainForm (See Figure 18). If you are not in Design View, you can easily switch views by right clicking on the InvoiceMainForm tab. As reference to Figure 18, note that the main form has a Form Header section, Detail section, and Footer section. The subform that is nested in the main form also has Header, Detail, and Footer sections. If you cannot see the entire form in Form View, use Layout or Design View to reshape objects within the form.

This is another good place at which to make a backup copy of your work. Close the database, saving your work and make a backup copy with a modified file name based on where you are in the tutorial.

4.3 Enter Sales Transaction Data

Open your database file and then open InvoiceMainForm. From the Home or Datasheet ribbon (at the top of the navigation pane), use the View button to select Form View.

Enter the sales transaction data listed in Table 3. See the highlighted comment above the table about entering the current year. Use the TAB key to enter data in the main form, and then Click on the field for Product No. to begin entering data in the subform. Use the TAB key to move to each field and new invoice line, when applicable.

Warning: Because invoice number is the primary key for this form, Access requires that you start entering information with the invoice number. In other words, make sure your cursor is in the Invoice Number data field when beginning to enter invoice data. The escape key will allow you to exit the invoice if you make a mistake and wish to start over. If that does not work, you may have to select File → Close. A warning message may appear that says the data changes will be lost. Answer Yes (you will only lose data for the current invoice being entered). double click on InvoiceMainForm to begin re-entering data.

As you enter data, notice that:

· When you enter employee code, both the employee last name and commission rate fields automatically fill.

· When you enter the customer code, both customer business or organization name and city fields automatically fill.

· When you enter the product number, both the product description and unit price fields automatically fill.

Your first invoice should be similar to Figure 19. It does not matter at this time if your fields are in a different order than what is shown, or that you may have to scroll in the subform to see all data values in all fields of invoice lines. You will modify form appearance in a later section of these instructions. Make sure at this time that all data for the first invoice is entered.

To move to a blank invoice, press the Next Record navigation button () at the bottom of the screen for the main form. Remember that there are two sets of navigation buttons: 1) the buttons that navigate between line items within the subform and 2) those that navigate between invoices. See Figure 15 for identification of each set of buttons. Enter all remaining data from Table 3.

QUESTION #3 TO BE FORMALLY ANSWERED IN OUTPUT TURNED IN FOR DELIVERABLE #2: In your Word document, type “Response to Question #3:” and formally answer the following question.

Read the PDF file of pages 398-399 from your course textbook that is posted with the first deliverable documents. Identify the control that is in place when using data entered to automatically retrieve and display related information in other fields in the form and the information characteristic enhanced by the control. Explain why for both the control and information characteristic.

QUESTION #4 TO BE FORMALLY ANSWERED IN OUTPUT TURNED IN FOR DELIVERABLE #2: In your Word document, type “Response to Question #4:” and formally answer the following question.

Coding schemes are discussed in Chapter 2 of your course textbook. Which coding scheme is being used for invoice number? Which coding scheme is being used for product number? Explain why for both answers.

QUESTION #5 TO BE FORMALLY ANSWERED IN OUTPUT TURNED IN FOR DELIVERABLE #2: In your Word document, type “Response to Question #5:” and formally answer the following question.

When defining the invoice number field in the invoice table, the instructions stated that it be a data type of text. Please go back and see 2.4.4 in the first deliverable instructions. Another option would have been to autonumber. You can open the invoice table in design view and see that option under Data Type for INVNO.

Autonumbering is preferable because it reduces data entry errors. It is also preferable because it eliminates any omission or failure to enter the invoice number.

Of the characteristics of information in Table 1-1 of your textbook, identify the two information characteristics that are most applicable to the discussion above as to why autonumbering is preferable. Explain why.

4.4 Review Results and Create another Backup

After entering all the data, save and close the form. When you enter data in the form, Access populates both the invoice and the invoice line tables. Open those tables and see that they are populated correctly.

After reviewing your results, exit Access and make another backup of your database.

QUESTION #6 TO BE FORMALLY ANSWERED IN OUTPUT TURNED IN FOR DELIVERABLE #2: In your Word document, type “Response to Question #6:” and formally answer the following question.

You have been asked to make a number of database backups in both deliverables of this assignment.

For ease with helping answer this question, a PDF file of pages 404-405 from Chapter 13 of the course textbook is posted along with these instructions. Please read these two pages beginning with the topic of Recovery and Resumption of Normal Operations. Of the characteristics of information in Table 1-1 of your course textbook, identify the one that is most applicable to the discussion of data backup procedures in this excerpt from your textbook. Explain why.

Please proceed to the next page for Part 5 of the assignment.

PART 5

ADDING CALCULATED CONTROLS AND FORMATTING CONTROLS

This section of the assignment illustrates how to add calculated values for invoice line totals and an invoice grand total to the invoice form.

5.1 Access Controls

Part 2.1 in the first deliverable instructions provides an explanation of the Access term object, which includes tables and forms. The term control is used in this section to describe labels, boxes, and images that are contained within forms. Please note that the usage of the term control here in Access is DIFFERENT from what we refer to in this course as “internal control.”

You can place three types of Access controls on a form:

· Bound controls : contains data stored in tables and brought into the form for display. For example, CustCode in the InvoiceMainForm is a bound control because the underlying customer code number is an attribute (or field) of the Invoice table.

· Unbound controls : design elements that are not a feature of the underlying table. For example, form headers and footers are unbound controls.

· Calculated controls : arithmetic or logical manipulations of bound controls, i.e., a calculated control, combines or performs logical operations on the data in the underlying table. For example, in this part of the instructions you will create a calculated control of Extended Amount that will be equal to the quantity of units sold (derived from the underlying invoice line table) multiplied by the unit price (derived from the underlying inventory table).

5.2 Inserting Calculated Controls

Since one purpose of the invoice form is to send to customers on which they will pay, you need to add calculated totals for each invoice line and a calculated grand total for each invoice. To accomplish this, open InvoiceLine Subform in Design View.

Arrange and resize the subform so that it is at least 7.5 inches wide with approximately one inch of space to the right of “UnitPrice” (see Figure 20). Grab the handle on the bottom of the “Form Footer” bar and drag the form footer so that it is at least ½ inch long. Reduce the size of any boxes that take up too much space in the subform by selecting the box and adjusting with your mouse.

5.2.1 Line Item Total

Add a line item total field to the detail section of the subform as follows:

1. In Design View, select the Form Design ribbon and then the text box button ( ab|) from the Controls group. Draw a text box to the right of unit price in the detail section. Note that you get not only a text box (with the word “Unbound” inside) but also a label box to the left of the text box (with the word “Text xx” where xx is a number automatically assigned).

2. Cut and paste the label box (the LE