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
LEFTMOST of the two boxes with the word “Text
xx”) into the subform’s header and above the new ‘Unbound” text box as follows:

a. Point to the label box and click to select it. click right mouse button. Select
cut. Click in the subform’s header. Click right mouse button and select
paste. The box will drop in the top left corner of the header. Move the label so it is above the new Unbound text box in the detail section as follows:

Point to the top left corner of the label box until the pointer turns into a four-direction arrow. Then hold the left mouse button and drag to move the label box above the text box.

b. Make sure the label box is highlighted. Change the wording to

Extended Amount
. If both words are not on one line or you cannot see all the letters, resize the box.

3. Right click on the “Unbound” text box in the detail section and select
Properties. The
Property Sheet dialog box will appear on the right side of the screen. Select the
Format tab and set the format to
Currency. Next, select the
Data tab from the Property Sheet and enter the following formula into the
Control Source field:

=[QtySold]*[UnitPrice]
. Type carefully! See CAUTION note provided below. Select the
Other tab and change the name of the field to

ExtAmt
. Close the text box properties menu.

CAUTION: Be very careful when typing the control source code because the system requires spelling

EXACTLY
conforming to your table field definitions (entered in Section 2.4 of the first deliverable instructions). For example, we used the field name
UnitPrice in defining the field in Section 2.4, but if “SalePrice” is typed in the Control Source field, the invoice will not properly extend the amount. The same is true for other spelling errors, such as adding a space between Qty and Sold when there should not be one, and spelling “QtySold” as “Qtysold.” Be very careful.

4. Save InvoiceLine Subform, but do not close.

The Form Header and Detail section of your subform should look more similar to Figure 20 now that this calculated control has been added.

5.2.2 Total Amount Due

Add a total amount due field to the footer of the subform as follows:

1. Make sure the footer depth (length) is about ½ inch (see Figure 20).

2. In the
Form Design ribbon, select
ab| and draw a text box in the bottom right corner of the footer.

3. click one time on the label box (leftmost box) to change the wording to

Total Amount Due:

4. Right click on the unbound control box and select
Properties. The
Property Sheet dialog box will appear on the right side of the screen. Select the
Format tab and change the format to
Currency. Select
Data tab and enter the following Control Source:

=Sum([QtySold]*[UnitPrice])
. BE CAREFUL WITH SPELLING! Your Design View should now look even more similar to Figure 20 with the addition of this second calculated control.

5. Close Properties, save the design view, and close InvoiceLine Subform.

SCREENSHOT #3 TO BE PROVIDED IN OUTPUT TURNED IN FOR DELIVERABLE #2:
Reopen your InvoiceLine Subform. Make a screenshot of the InvoiceLine Subform in Design View at this point in the assignment and place in your Word document. Before the screenshot, provide the title “Screenshot #3 – Design View of InvoiceLine Subform”.

5.2.3 View the completed form

From the Home ribbon, open InvoiceMainForm. Select
Form View. Your InvoiceMainForm should look more similar to Figure 21 (you may note that certain field names are labeled differently in the figure – this is because of a few more edits that will be made in these instructions).

To change the appearance of the form, edit through either the Layout View or Design View. For example, sometimes the individual fields on the subform cannot be viewed except by using the scroll bar on the right side of the subform. This is not good, because when the invoice is generated for the customer, information will be cutoff.

Modify your form until all data for any particular invoice you have entered is shown on screen in Form View without having to scroll. This may require you to modify the spacing in one, two or three areas of the subform (Header, Detail and Form Footer).

Besides resizing your form, you can change the order of data fields on the form. For example, it is probably better that all customer data is grouped together and all employee data is grouped together in the top half of the form. Click on a field of interest and move to where you desire to show on your form. Figure 21 shows a resulting invoice with the fields reordered. Change the appearance of your form in this manner, as well as any other manner as long as it is logical in presentation. For example, resize label and text boxes to nice sizes based on the amount of data contained within each. As another example, line up boxes so that your invoice looks neat. As a further example, center the text in label boxes for enhancing the presentation of the invoice and/or justifying the values in text boxes (these can be done through the Format tab in the Property Sheet for a box).

After making modifications that improve the form’s appearance

(including the modification of your form so that all data for any particular invoice you have entered is clearly shown on screen in Form View without having to scroll or with data cutoff from view because a box is too small)
, the revised form can now be used for recording, viewing sales transactions
AND generating invoices for customers.

5.3 Formatting Controls

You have already done some minor formatting by moving and resizing some controls, e.g., label and text boxes.

Open InvoiceMainForm in Design View. Editing the form header is similar to editing a Word file. Note that a label box is located in the header. Add another label box in the middle of the header by going to
Form Design ribbon
Controls group

Aa
and dragging the label tool. Change the text so that it reads
[your last name] Company Invoice.

click outside the box. right click inside the box and select
Properties. Select
Format tab and change the font size to 18 and the Font Weight to extra bold. Close the Property Sheet. Resize and center the heading within the box and on the form. Delete the InvoiceMainForm text box in the main form header. Switch between Design and Form View to see your changes.

Perform the following edits:

· Change the label for
Employee Last Name to
Sales Agent

· Change the label for
Employee Code to
Sales Agent Number

· Change the label for
Customer’s Business or Organization Name to
Customer Name

A final step in designing your screen form is to change the background color. Although the light blue is fine, it uses a lot of ink if printing the invoices. In Design View, place the pointer in the header bar and left click to highlight the header bar. Then right click on the header bar, and select the
Fill/Back Color button, and change the back color to white. Check to make sure this background formatting is also reflected in the detail section. After all format changes have been made, save the form.

Many more enhancements could be made but are not required for this assignment. For example, you could add color and graphics images, a customer list box to display a list of possible customers or use a combo box that combines a text box with a list box. You could also create command buttons to streamline functions such as a command button (for example, one that states .

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

PART 6

CREATING QUERIES AND REPORTS

This section of the assignment illustrates how to create a query underlying a report. Besides using forms based on queries, database software such as Access provides the capability of “interrogating” data based on queries and generating reports for decision makers.

6.1 Create a New Query

Perform the following:

1. Select the
Create ribbon →
Query Design. At the resulting screen, click the
Add Table button from the ribbon to show the list. Your screen should be similar to Figure 22.

2. In the Add Tables area, double click on both
Customer and
Invoice. Close the Add Tables area after adding these two tables.

3. double click
CUSTCODE from the
Customer table box to add to the first data column of the query.

4. Input “142” (include the quotes) in the criteria box in the first data column containing
CUSTCODE.

5. Uncheck the
Show checkbox in the
CUSTCODE column. Since we already know which customer that we are choosing by specifying the
CUSTCODE criteria, we do not need Access to show us the repeated customer number associated for every invoice identified by the query.

6. double click
INVNO from the
Invoice table box to add the field to the second data column of the query. Your screen should be similar to Figure 23.

SCREENSHOT #4 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 #4 – Design View of Query for Sales to Customer 142”.

7. Save the query by selecting
X. Select Yes to save changes and save as

CustomerSalesQuery
.

8. To run the query, double click on
CustomerSalesQuery in the left panel. Six invoice numbers should be listed on screen. While this query provides the result desired, it is common to produce formal reports based on queries. You will now create a formal report using the query.

6.2
Create a New Report

1. Close the query. Select the
Create ribbon →
Report Wizard located in the
Reports group to create a formal report for the query you just created.

2. On the resulting
Report Wizard box, make sure that
Query:CustomerSalesQuery is selected under Tables/Queries.

3. click the chevron button (>) to add INVNO.

4. Select
Table:Customer under Tables/Queries. Add
CustOrgName.

5. Select
Table:Invoice under Tables/Queries. Add
InvDate.

6. Select
Table:Inventory under Tables/Queries. Add all fields by clicking the double chevron button (>>).

7. Select
Table:InvoiceLine under Tables/Queries. Add the
QtySold field. Your screen should be similar to Figure 24.

8. click
Next to move to the next screen of the wizard.

9. The wizard asks if you want to add any grouping levels for organizing your report data. Highlight
CustOrgName in the left selection area and click the chevron button (>). The report you are creating will group data in the report under the first heading of
CustOrgName.

10. Add a second level group by highlighting
INVNO and clicking the chevron button (>). Your screen should be similar to Figure 25.

11. These are the only two groupings that your report will have. click Next to proceed in the wizard and click Next on the following screen as well.

12. Choose
Outline for the Layout and
Landscape as the Orientation. click Next to proceed.

13. Change the title to

Customer Order Report – your last name
. Make sure the
Preview the report button is selected. click Finish. Your report should look like that in Figure 26.

14. Scroll through the pages of your report to make sure it is nice and presentable to a user. For example, if the report includes unnecessary pages such as from formatting issues resulting in the page number not properly fitting on the page, open the report in design view and modify as you did your invoice form. Do this for any issues so your report is nice and presentable to a user.

Please leave your report as is, but consider this – is there any way you would like to change this report? For example, note the repetition of the invoice date under each invoice. It may make more sense to use invoice date as a grouping instead of it being a detailed item within. Ultimately, you should ask the user how they would like information presented in the report. Actually, you should first ask the user what information is to be presented in the report to help the user make decisions. You also want to make sure the user can obtain the report. This can be done by giving the user the ability to retrieve the report themselves from the database.

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

Of the characteristics of information in Table 1-1 of your course textbook, identify the three that are emphasized in the last paragraph above. Explain why.

Congratulations! You have completed the Access assignment! Upload both your final database (I am not requiring a backup because the database is no longer a work in progress but is complete) and Word file of output (cover sheet, screen captures and answers to questions) at Blackboard.

9.1

Page | 15