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

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