MS Access Assignment 2

ACCT 3303
Access Project Using Microsoft Access 2013/2016 Fall 2017

The purpose of this assignment is to help you gain a basic understanding of both MS Access and relational database concepts. You are to create tables, forms, queries, and reports for a database that deals with accounting service billing. Instructions for Microsoft Access 2013/2016 are given in these instructions. You may use another version; however, you will need to be able to adjust the instructions.

Special Note: All examples contained in these project notes are for illustrative purposes only. Your data should not look like the examples or the data of any other student.

Create an Access Database: After starting MS Access 2013/2016, click the Blank Database icon near the top of the right frame. Change the filename in the pop up window textbox.

. For example, I would name my database Mark_Adams_Billing.accdb). Click on the folder icon to the

right of the textbox to select the folder location of your database. Then click the Create button.

Create Tables: When your database first opens, you should see Table1 by default. Click the View icon (top left of the icon ribbon on the Home tab) and name your table as specified on the next page (i.e. the first table should be named Service). If you do not see Table1 or if you are ready to create another table, click the Create tab, then the Table icon followed by the View icon.

The Field Name should be input as described below and the Data Type should be selected from the drop-down list in the top section of the Table window. The Field Size, the Caption, Input Masks, Validation Rules and Validation Text, and any other required information (see comment column for additional information) should be input in the Field Properties section of the Table window. See the figure below and the information on the next page.

Name your

database as follows: xxx_yyy_Billing.accdb (where xxx_yyy represents your first name and last name

1

Service table:

Validation Rule:

•   Rate – limit rates to between $50 and $250

  inclusive. (Between 50 And 250)*.

•   Don’t forget the Validation Text!
  To create the remaining tables, on the Create ribbon select the Table icon and then click the View icon.

  ClientType table:

  Billing table:

  Client table:

Validation Rules:

•   ClientID – must be a 5 digit number. (Like “#####”)*.

•   Don’t forget the Validation Text!

  * Note: validation rules are in the parentheses. Be sure you don’t include the parentheses ( ) in your validation rule.

2

Import a table: Create an Excel worksheet similar to one below. Be sure your column headings are exactly like the ones you created in your table. ClientTypeIDs will be assigned later.

Client data: You should have at least 12 customers in 3 different Client Types such as individuals, partnerships, corporations, etc. Zip codes and phone numbers should only include numbers.

Note: Your data must be unique to your project. Do not copy my client data nor that of a classmate.

Excel worksheet.

Close your Excel worksheet before continuing.

Click on the External Data tab. On the ribbon, in the Import & Link group, click on the Excel icon. Browse to find the Excel file you created. Select the Append a copy... option and select the Client table from the drop-down list. Click OK to continue.

The next window will show your data as shown below. Make sure it looks right before proceeding. If there are problems cancel the import and correct your headings and data in your Excel worksheet before starting the import process again. If everything looks correct, click Next to continue. Make sure Client is showing in the Import to Table: textbox then click Finish. Click Close on the Save Import Steps window.

Open the Client table and make sure the data imported correctly.

Close all open tables before continuing with the next step or you will not be able to set up the

relationships properly.

3

Create Relationships and Relationships Report: Set up relationships to link your foreign keys to the appropriate primary keys. Click the Database Tools tab and then click the Relationships icon. A popup box should appear with a list of all tables in your database. Select each one and then close the box. You can rearrange the tables by clicking and dragging them to where you want them. Set up relationships by clicking and holding the primary key in the Service table. Drag and drop on ServiceID in the Billing table. Verify that the specified tables and field names are as desired. Click on the Enforce Referential Integrity check box and then on the Create button. Do the same for the Primary Key in the Client table. Finally, link the Client Type table to the Client table using the primary key of the Client Type table. If a relationship does not appear to be correct, delete it and start over. The resulting relationships should look similar to the following:

Click on the Relationships Report icon on the Tools Ribbon (Design tab). Click the Close Print Preview icon on the right side of the Ribbon. Now your screen should be showing the report in the Design View. Change the report title to include your first and last name similar to below:

Save the report. The default name (Relationships for xxx_Billing) is fine. Close any open Tables or Reports.

Create Forms: Create 4 data entry forms for the 4 tables previously created. To create a form for the Service table, click the Service table, click the Create tab and then click the Form icon. A form like the one below should appear.

If at any point you get the following warning, click the Enable Content button.

4

Notice that the form includes a subform. Click on this subform and delete it.

Change the name of the form to Service form. Choose the Layout View if you are not already in that view from the View drop-down view list. Click in the title box on your form and type in the new name. Also check the remaining labels on the form to make sure they appear correctly. If not, make any needed changes. You should not be showing any abbreviated field names such as ServiceID (or words that run together such as ClientName) in the forms. You may also make, if you wish, your field sizes appear smaller by clicking and dragging the edge of the text boxes to the left. Your form should appear as below. Save your form as Service Maintenance Form.

Create the Client Type form as you did above making sure you delete the subform. Change the title to Client Type Maintenance For and save the form as ClientType.

Create the Client form making sure you delete the subform. Save the form as Client.

5

Now replace the Client Type text box with a combo box. Click on the Client Type ID text box and press the delete key. Click the Combo-Box (Controls) icon (see figure below).

Click on the space left by the text box you deleted previously. Once you do this, a Combo Box Wizard will open. Choose the default “I want the combo box to get the values from another table or query” option and click Next.

On the next screen select the ClientType table and click Next.

On the next screen, from the Available Fields

click the button to move both fields to the Selected Fields, then click Next.

On the next screen concerning sort order, select ClientTypeDesc from the first drop down list, make sure Ascending is showing and click Next and Next again on the next screen.

6

On the next screen (see figure below), select the “Store that value in this field:” option. In the drop-down box, select ClientTypeID and click Next.

On the next screen, Type Client Type ID in the text box and click the Finish button.

Your form should appear with a combo-box as show below. (your data will be different)

7

Save and close your Client form.

Create a Billing form as outlined in the steps above. The form title should be Hours Billed. Create two (2) combo boxes on the Hours Billed form for the Client ID and the Service ID.

Client ID: Choose the default “I want the combo box to look up the values in a table or query” option and Click the Next button. Choose the Client table from the Tables list. You should choose the Client table for the Client ID combo box and click the Next button. Select the ClientID and ClientName from

the Available Fields and click the button to move the fields to the Selected Fields then click Next.

On the next screens, you can sort your data by ClientName and change the size of the column if you wish, then click the Next button on each screen. On the final screen make sure you select the “Store that value in this field:” and choose ClientID from the drop-down list before clicking the Next button. Name your combo box so the appropriate label will appear on your form.

Service ID: Choose the default “I want the combo box to look up the values in a table or query” option and Click the Next button. Choose the Service table from the Tables list. You should choose the Client table for the Client ID combo box and click the Next button. Select the ServiceID and Description from

the Available Fields and click the button to move the fields to the Selected Fields then click Next.

On the next screens, you can sort your data by Description and change the size of the column if you wish, then click the Next button on each screen. On the final screen make sure you select the “Store that value in this field:” and choose ServiceID from the drop-down list before clicking the Next button. Name your combo box so the appropriate label will appear on your form.

Your form should appear similar to the one below. You will have no data showing in the combo boxes as you have not entered any data in your tables.

Save and close your Hours Billed form.
Enter Data: Use the forms you have created to enter data. Note: Make up your data. Your data

should not be like the examples used in this tutorial or the same as another classmate’s data.

*** Important Note: To prevent problems with data entry, you should populate the tables with data in the following order: Service, ClientType, Client, and Billing.

8

Service data: You should have three ServiceIDs: A, C, and T. The corresponding Descriptions should be as follows:

A – Accounting Services C – Consulting Services T – Tax Services.

Enter your own rates for each service type following the validation rules set up in the table. ClientType data: You should have at least five client types. Choose any five from the following list:

Client data: As you imported your data from an Excel worksheet without Client Type IDs, you will need to navigate to each record in your Client form and select the appropriate type of Client Type. Make sure you have at least three (3) client types. Add two (2) more clients using your Client form. To quickly get to a blank record, click the New (blank) record icon at the bottom of your form.

Billing data: Enter billing data for 2 work weeks (10 days; Monday – Friday). Keep in mind the number of work hours in a day should be reasonable. You should enter billing hours for all of your clients at least once during the 2 weeks and use all 3 of your services. At least 3 of the clients should be billed more than once. At a minimum, that will require you to make at least 17 billing entries.

Create Queries: Create the following queries. Click the Create tab and then click the Query Design icon. Select the appropriate tables needed for your query, click the Add button and close the Show Table window. The tables you select depend on what results the query needs to show.

Move the fields from the top panel where your tables are showing to the Field row of bottom panel where you build your query by dragging the field or by double-clicking on the field name. Alternatively, you can select the field name from the drop-down box in the Field row in the bottom panel.

You can also create new fields as shown in the first query. Add any sorting, filtering, or other selection criteria to your query. Next you want to run your query to see if it gives you the correct results. The run button can be found in the Results section of the Design ribbon. If the results are unexpected, you can go back and refine your query until you have the data you need.

Amount Billed by Date: Create a query showing the Billing Date, Client Name, Service ID, Hours Billed, Rate, and Total Billed. The results should be sorted in ascending order by BillingDate and ServiceID. Total Billed should be calculated as follows: Total Billed: [HoursBilled] * [Rate]. With the Total Billed column in the Query Design View selected, click the Property Sheet icon (Design Tab) at the top of the window and choose the Currency format from the drop-down box (see below).

9

Save the query as Amount Billed by Date. Your results should be similar to the following when you run the query.

Amount Billed by Client Type: Create a query showing the Client Type Description, Client Name, Service ID, Hours Billed, Rate, and Total Billed. The results should be sorted in ascending order by ClientTypeDesc and ClientName. Total Billed should be calculated as follows: Total Billed: [HoursBilled] * [Rate]. With the Total Billed column in the Query Design View selected, click the Property Sheet icon (Design Tab) at the top of the window and choose the Currency format from the drop-down box (see previous query).

Your results should be similar to the following when you run the query. Save the query as Amount Billed by Client Type.

10

Create Reports: Prepare the following reports by selecting the appropriate table or query and clicking the Create Tab followed by the Report icon.

Important Notes: The appearance of your reports may be different from the examples provided in this tutorial. If you want to get rid of the default lines, select all of the data by holding the control key down and clicking the top field in each column. Click on the Property Sheet icon and select the Format tab, find the Border Style property and select Transparent. You can also get rid of alternating row colors by clicking on the Detail bar in the Design View and selecting No Color in the Alternate Back Color property.

Also, make sure you look at all of your data in the report. If any data is not showing fully, just click on the cell and resize the orange box until all of your data is showing.

Amount Billed by Client Type: This Report is based on the Amount Billed by Client Type Query and should be Grouped on ClientType and totaled on Total Billed and also Grouped on ClientName and totaled on TotalBilled. Be sure to include Grand Totals for both groupings. See grouping information below:

See the example report below (shown in Report View):

11

12

Amount Billed by Date: This Report should be based on the Amount Billed by Date Query and should be Grouped on BillingDate and totaled on HoursBilled and Total Billed.

HoursBilled and TotalBilled should look similar to the above pop up window screenshots.

As shown above, the grouping will result in the dates showing for each individual row under each date. My screenshot does not show this. It is acceptable to have the date for each line as well as the header date, but you can hide the dates. There are likely multiple methods to do this, but I just changed the font color and removed the alternate row color. To remove the alternate row color, go to the Design View and click on the BillDate Header bar and open the Property Sheet. For the Alternate Back Color property on the Format tab, select No Color from the drop-down list. Do the same for the BillingDate Footer. In the Detail section click on the BillingDate field and on the Property Sheet Fore Color property and click the ellipsis button and select the white color option.

(Continued on next page)

13

Client List: Create a client list report based on a client list query (Hint: you will need to create a new query). The Report should be grouped by Client Type and sorted by Client Name (Group & Sort icon on the Design tab). You will need to change the field sizes and make sure your report prints in the landscape layout (Page Setup Tab). Change the field sizes in the Design View and the layout to landscape in the Print Preview view. See the example figures below:

See the example below (shown in Print Preview):

14

ADDITIONAL REQUIREMENTS:

1. Create queries to answer the following questions:

   1. What was the average amount billed per transaction? (This query should return a single

      number.)

   2. What was the average amount billed for each service type? (Sort descending by

      Description)

2. Create the following report:

a. Amount Billed by Client. Include the following in your report: Client Name, Transaction Number, Billing Date, Due Date (assuming due date is 15 days after the billing date), and the Amount Billed. Total on the Amount Billed for each client and include a grand total. Group by Client and sort by Transaction Number.

DELIVERABLE:

1. Upload your database file (xxx_Billing.accdb) to Blackboard. Note: you will need to click on the assignment itself to upload the file. Browse for your file, select it and click the Submit button.

IMPORTANT REMINDER: This is an individual project. Identical or close to identical submissions constitute academic dishonesty and can have serious consequences in addition to a zero on the project. It is acceptable to get assistance from other students or the instructor

*** Before turning in your file, be sure to compact the database. From the menu bar, select File. In the

Info section, click on Compact & Repair. ***

when you have difficulty completing part of the project, but each student must complete and submit their own work.

 for fast delivery please contact us at