ACCTG 333 Microsoft Access Forms Reports and Queries Assignment

timer Asked: Apr 11th, 2019
account_balance_wallet $19.99

Question Description

Please complete this Access assignment, I would like for it to be finished by Monday, April 15.

Unformatted Attachment Preview

ACCTG 333 Spring 2019 Dr. Chang ACCESS Assignment – Forms, Reports, and Queries (50 points) Due on April 16 by 11:00PM REQUIREMENTS:  Please use the database Traggs for Access (on Blackboard) to complete this assignment.  After you download the database, do NOT open it. Change its name to be Traggs by your initials such as Traggs by JC.  Follow the tutorial to complete all the forms, reports, and queries.  You must include your initials when creating forms, reports and queries. For example, Sales Form by JC.  To receive credit for an item, include both the design view and the output view of your work in a Word document by using “print screen” function on the key board. Each item (both the design view and the output) should be printed on one page. Just if you use more than one query to get your result, include all the queries in your submission.  You have to use your full name as the header for your Word document.  Required printouts in Word must be in the order as presented in the tutorial (forms, reports, and then queries). Points will be deducted if pages are out of order. DELIVERABLE: Email your Word file to your section GA by the deadline. 1. Relationship report/window (one page) 2. Sales main form 3. Sales subform (show properties) 4. Sales subform (combo box - must open it to show the data values) 4. Sales invoice report (one page) 5. Sales Invoice Query 6. Inventory Query 7. Monthly Cash Receipts Query 1 SALES/COLLECTION BUSINESS PROCESS OVERVIEW Basic Concepts and Definitions In general, the revenue cycle interfaces with the conversion cycle, the financing cycle, and the financial reporting system. The financial reporting system interfaces with all of the cycles. Extending this to the framework of business processes, we can see that goods and services are made available to a company’s Sales/Collection process as a result of the Conversion, Acquisition/Payment and Payroll processes. The Sales/Collection process turns those goods and services into cash, which is made available to the Financing process. For this to happen, the Sales/Collection process must include at least one economic event that transfers out the goods and services (i.e., a decrement event) and at least one economic event that transfers in the cash (i.e., an increment event). The Sales/Collection process for all firms is similar regardless of whether the firm is engaged in manufacturing, in service, or in merchandising. Labor is typically not tracked and matched to the revenue-generating activity since labor costs are normally aggregated into a selling, general, and administrative expense and accumulated in the payroll cycle. One exception to this is in the case of service providers, where labor takes the place of inventory. Other exceptions include companies that use delivery services. Tragg’s Custom Surfboards Background Tragg’s Custom Surfboards is located in La Selva Beach, California. Tragg’s Custom Surfboards both manufactures and sells surfboards and surfing equipment. Jerry Tragg began shaping, designing, and manufacturing his own surfboards 25 years ago. He soon discovered that there was a demand for them among his friends and acquaintances. He started selling them casually and ultimately founded the business in 2008. Initially, his boards were sold through a few select dealers. As demand grew, however, he set up a website and opened his own store. The store now carries a stock of surfboards and Jerry continues to fill custom orders. Model the Revenue Cycle using REA Sales begin with the customer coming into the store or can commence with an e-mail order or a telephone order from the customer. Tragg’s requires a 50% deposit before beginning a custom order and this deposit is non-refundable. Custom orders usually take 4-5 weeks to complete. Whether a custom order or a sale from stock, the customer has a choice between four basic board styles: Egotist, Imposer, Nice Devil, or King Creator. The customer can also choose between two fins: single fin or glassed-on fin. Coloring is offered in opaque or transparent. The customer can choose his or her own pigment for the board by sending in a color chip obtained from the paint section of their local hardware store. Pricing is largely dependent upon the length of the board plus any extra features that are requested. For example, a board up to 6’11” begins at $475. Anything longer than that goes up $25 in price for every six inches requested. All boards come with certain standard features, depending upon the board 2 style chosen. In addition, the customer can add only one of the following custom stripes to his or her surfboard: a pinline stripe for $20, a colored competition stripe for $40, color lamination (tint or opaque) for $40, cloth design for $50, airbrush finish for $25, a gloss and polish for $45, or an extra 10-oz. volan glass layer for $25. (We realize that, in reality, customers might well order more than one of these custom extras. However, we have limited it to one custom extra per order for simplicity’s sake.) Tragg’s stores the information on extra features and details on other inventories in a separate table. When orders come in (custom orders or otherwise), a Sales Invoice form is completed. All sales are final; there are no returns allowed. As a result, the sales order is the sales invoice. (Again, to simplify this example, we will assume that no sales tax is charged. In reality, sales tax would be charged on all in-store sales and internet sales made to California residents). Customers must arrange to pick up the surfboards in the store or have them shipped. Final payment is due when the surfboard is picked up or before it is shipped. In addition, some customers may pay for several transactions at once (i.e., write one check for several invoices). Although, Tragg’s maintains several bank accounts (one general checking account, one payroll account, and one savings account), most of the cash receipts and cash disbursements are handled through the general checking account. Most cash receipts come from sales; however, Tragg’s does have a few investments that result in dividend income and are deposited in the general checking account. 3 According to the descriptions of Tragg’s business processes in the revenue cycle, Figure 1 is an UML Class diagram created to model the business activities for Tragg’s. Please note this is a partial model which only includes the customer Agent and its relevant links. Resource Event Agent FIGURE 1 UML Class Diagram for Tragg’s Custom Surfboards Revenue Cycle Create a Relational Database for Tragg’s Following the UML Diagram To start with a relatively simple design of a database, we will not create tables for those entities not shown in Figure 1. In addition, as noted in the model, the relationship between the entities Sales and Inventory is manyto-many. This is because it is possible that we will have several of one particular kind, size, and color surfboard in stock at one time. Therefore, as you learned in class, we cannot use a foreign key link to join these entities. You must create a separate table to represent each many-to-many relationship. Keep in mind that we have limited customers to one Extra on their surfboard. Therefore, the relationship between Extras and Sales is a one-to-many relationship and no separate table is necessary. Figure 1 includes both the table structure and the attributes in each table. Creating Tragg’s Relational Database using Access Open the database for Tragg’s Custom Surfboards (on Blackboard). You must download this database and save it to your hard drive in order to use it for your assignment. Note that all the tables, some relationships, and some forms have already been created for the Sales/Collection Business Process. Take some time to examine the tables, forms, and the relationships that have been created thus far. 4 The Sales Form: Creating a Form with a Subform The Sales Form is the Sales Order for Tragg’s. Therefore, it must be able to obtain information about customers, inventory, and extras. The database that we have provided for you has partial data in it so that you can now complete a sale. If you have not yet done so, download and open the Tragg’s Custom Surfboard database and take some time to acquaint yourself with the Customer Table, Inventory Table, and the Extras Table. In addition, since the creation of the Sales Form will result in entering data into both the Sales Table and the Sales Inventory Table (i.e., the relationship table), we will need to establish the necessary relationships between these tables, before creating the Sales Form. 1. Click on the Relationships icon in the Relationships section of the Database Tools tab to open the Relationships window. Figure 2 shows the existing partial database. FIGURE 2 2. Click on Show Table in the Relationships section of the Design tab. 3. Hold down the Ctrl key and click on the Customer Table, the Extras Table, the Inventory Table, the Sales Inventory Table, and the Sales Table. 5 FIGURE 3 4. Click Add. Then click Close. 5. Look at the arrangement of the tables as they have been added to the Relationship window. Access randomly places them in the window. However, for the sake of an orderly AIS design, it is useful for us to think about Resources, Events, and Agents (i.e., the REA diagram). Drag the tables so that they are generally placed in the same locations that they would appear if you had created a REA diagram. This will be helpful to you in the event that you need to locate a table in the Relationship window later. FIGURE 4 6 6. Create the necessary relationships by linking the primary keys to their foreign keys in the appropriate tables. For example, drag CustomerID from the Customer Table to CustomerID in the Sales Table. When you do this, the Edit Relationships window appears, check the Enforce Referential Integrity and the Cascade Update Related Fields buttons and click Create. FIGURE 5 7. Create all the remaining relationships so your relationship window looks like Figure 1. 8. Close the Relationships window. Now that the Relationships have been created, we are ready to begin creating the Sales Form. 9. Click on the Form Wizard icon in the Forms section of the Create tab. 10. From the Tables/Queries pull-down menu, select the Sales Table and select all three fields for inclusion in the form. 7 FIGURE 6 11. Select the Customer Table from the drop-down list under Tables/ Queries and select all the fields for inclusion in the form. NOTE: Before you select fields for inclusion from this table and from tables in the future, it is helpful to make sure that the last field is highlighted in the Selected Fields window. If it is not, the fields may be out of order when the form is created and you might have to rearrange them! 12. Remember that in some cases a field represents a foreign key (i.e., it is a primary key in another table) and, therefore, provides the relationship between tables. Therefore, it is important that we select the field from the correct table for inclusion in the form. The CustomerID is one of these fields. It is represented in both the Sales Table and the Customer Table. We do not want to include both fields in the form so the question arises as to which field should be included in the Sales Form. Since this is the Sales Form, the CustomerID field included should be the one from the Sales Table. In addition, there is no need to include the CustomerPhone field in the Sales Form. Return the CustomerTable.CustomerID and the CustomerPhone fields back in the Available Fields window by selecting each field individually and clicking on the Left Arrow 8 icon. FIGURE 7 13. Select the Sales Inventory Table and, as before, select all fields for inclusion. Return the Sales Inventory Table.InvoiceNo field to the Available Fields window. We have included the InvoiceNo field from the Sales Table. Therefore, we do not include it again from the Sales Inventory Table. FIGURE 8 9 14. Select Inventory Table and then select InventoryDescription and InventoryPrice for inclusion in the form. Note that you do not need the InventoryID field included in the form because you have already included this field when you added it from the Sales Inventory Table. FIGURE 9 15. Select the Extras Table and select ExtrasDescription and ExtrasPrice for inclusion in the form. You do not need the ExtrasID field included in the form because you included the ExtrasID field when you added the Sales Inventory Table. FIGURE 10 10 16. Click the Next button. The Form Wizard automatically suggests a layout for the Sales Form (containing fields from the Sales Table and the Customer Table) with a subform (containing the fields from the Sales Inventory Table, the Inventory Table and the Extras Table). Note that, if the Form Wizard does not suggest this layout, one likely explanation is that you have not created the appropriate relationships between the tables required for this form. If this occurs, you should go back and examine your relationships before continuing on with this step. FIGURE 11 Accept the layout by clicking the Next button. FIGURE 12 11 17. Select the Tabular layout for the subform in the next window and click Next. 18. Change the name of the form to Sales Form (leave the name of the subform alone). Click on Modify the form’s design. Click on Finish. FIGURE 13 19. We will now make substantial adjustments to the form’s design. FIGURE 14 12 20. Shrink the Navigation Pane containing the database objects on the left-hand side of the database window by clicking on the left double arrow at the top of the Navigation Pane so that we can work with the Sales Form in the full window. Note that you will be able to retrieve the Navigation Pane at any time by clicking on the right double arrow . Switch to the Form View by clicking on icon in the Views section of the Design tab. Notice that the city, state, and zip code the View portion of the address are on separate lines. FIGURE 15 Since this information will be returned automatically by the database once the CustomerID has been entered, we can consolidate the information and make the form more user-friendly. 21. Return to the Design View and double left click on the Form Selector (the square in the upper left corner of the form where the two rulers meet). 13 FIGURE 16 22. This will pull up the Property Sheet window on the right-hand side of the screen. FIGURE 17 23. Click on the Data tab in the Property Sheet. Then double-click on the Build button next to the Record Source property. This opens the Query Builder window. Notice that the Sales Table and the Customer Table appear at the top pane of the window linked by CustomerID. Also notice that all of the fields that we added to the form appear in the columns in the lower pane of the window, called the Design Grid. Close the Property Sheet. 14 FIGURE 18 24. Click on the Sort property under the InvoiceNo field. Using the pull-down menu, select Ascending for this property so that the Sales Invoices will sort according to Invoice No. FIGURE 19 15 25. Scroll to the right of the Design Grid until you reach an open column (the column after CustomerZip). Right click in the Field cell of this column and click on the Builder icon. This opens the Expression Builder window, which facilitates the creation of new fields in a query. FIGURE 20 16 26. In the Expression Elements portion of the window, select the database (Traggs by Initials), then select Tables finally select Customer Table. FIGURE 21 27. We are going to create a composite address field for the customer’s city, state, and zip code so that all three fields are combined into one field, in a format similar to that you would see in a mailing label. Type CustomerCompAddress: in the upper portion of the Expression Builder window. Double-click on CustomerCity; type &, type “, “ (typed as an open quotation, comma, space, close quotation). type & and double-click on CustomerState. Type & and type “ “ (typed as an open quotation, three spaces, close quotation). Type & and double-click on CustomerZip. Remove the <> that was automatically inserted just after the CustomerCompAddress field name from the expression. (Note that you may have more than one appearance of <>. You will need to remove every instance for your expression to run properly.) 17 FIGURE 22 28. Click OK and click the Run icon in the Results section of the Design tab. FIGURE 23 29. Return to the Design View for the Query Builder. Make another composite field for the customer’s name using the same process you followed in Step 25. Put the FirstName field first and the LastName field second with a space in between (“ “). Name this field CustomerCompName. 30. Close the Query Builder (by clicking the X in the upper right-hand corner) and save the query. 31. Highlight the LastName label and field in the detail section of the Sales Form; right-click on them and click Delete. Repeat the process and delete the FirstName, CustomerCity, CustomerState, and CustomerZip fields and their labels. 18 FIGURE 24 32. Click on the Add Existing Fields icon in the Tools section of the Design tab. A Field List window appears with all the fields that are available for this form. Notice that the two new fields that you created are in this list. 19 FIGURE 25 33. In the Field List click on the CustomerCompName field and drag it to the right of the InvoiceNo field. From the Sales Form drag the CustomerAddress1 field below the CustomerCompName field and drop it and then drag the CustomerAddress2 field below the CustomerAddress1 field. In the Field List click on new CustomerCompAddress field and drag it below the CustomerAddress2 field. 34. Delete the labels on the customer name and address fields by clicking on them and hitting the delete key (or right click on the label and select Cut). Be careful not to delete the fields themselves. Adjust the size of the new fields. You can expand the field windows so the entire field name is showing. 20 FIGURE 26 You can close the Field List now. Since the Customer Table is one of the tables that data is read from in this form, we need to ensure that the user cannot write any data to the Customer Table in this particular application. Therefore, we now want to format the customer fields so that the user cannot change the data that is entered in these fields. 21 35. Place the cursor below the lower right-hand corner of the CustomerCompAddress field. Hold down the left mouse button and drag the cursor across all of the customer fields until you have drawn a rectangle across all of the fields. When you release the cursor, all of the customer fields should be highlighted. FIGURE 27 36. We want the Customer information to automatically appear when the Customer ID is entered. This is called Closed-Loop Verification, where one item of data is used to retrieve other fields from a record so the user can ensure that the proper record has been accessed. While it is not necessarily a very strong internal control, it is a good preventive control to ensure that the wrong customer does not get billed. To achieve closed-loop verification, click on the Property Sheet icon in the Tools section of the Design tab. The Property Sheet window is now accessible on the right-hand side of the screen. If it does not open to the Data tab, click on the Data tab now. Change the Enabled property to No. This results in deactivating the property and it will appear dimmer than the other properties. We don’t want the values in these fields to be dimmer than the other fields, though. Therefore, we will also change the Locked property to Yes. When the Locked property is set to Yes, the field value can’t be edited. Using a combination of these two properties results in leaving the data rea ...
Purchase answer to see full attachment

Tutor Answer

School: Cornell University

These files are database .mdb and Word file with the required screenshots.Please tell me if you have some questions or want me to make some changes...

flag Report DMCA

Good stuff. Would use again.

Similar Questions
Related Tags

Brown University

1271 Tutors

California Institute of Technology

2131 Tutors

Carnegie Mellon University

982 Tutors

Columbia University

1256 Tutors

Dartmouth University

2113 Tutors

Emory University

2279 Tutors

Harvard University

599 Tutors

Massachusetts Institute of Technology

2319 Tutors

New York University

1645 Tutors

Notre Dam University

1911 Tutors

Oklahoma University

2122 Tutors

Pennsylvania State University

932 Tutors

Princeton University

1211 Tutors

Stanford University

983 Tutors

University of California

1282 Tutors

Oxford University

123 Tutors

Yale University

2325 Tutors