Fluent Essays

you have to follow the steps and make a database in Access. Everything should be as per the requirements of the question ACCT 3303 Access Assignment Using Microsoft Access 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 2019/365 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 (with the exception of the service table) should not look like the examples or the data of any other student. Create an Access Database: After starting MS Access 2019/365, click the Blank Database icon near the top of the right frame. Change the filename in the pop-up window textbox. Name your database as follows: xxx_Billing.accdb (where xxx represents your first initial and last name. For example, I would name my database SHenderson_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. Important: Do not forget to enter captions. Service table: Field Name Data Type Field Size Caption Comments ServID Short Text 3 Service ID Primary Key Description Short Text 25 Description Rate Currency Rate Validation Rule: · Rate – limit rates to between $100 and $500 inclusive. (Between 100 And 500)*. · 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: Field Name Data Type Field Size Caption Comments/Other ClientTypeID Short Text 3 Client Type ID Primary Key ClientTypeDesc Short Text 25 Client Type Description Billing table: Field Name Data Type Field Size Caption Comments/Other TransNo AutoNumber Transaction Number Primary Key/ (Transaction Number) BillingDate Date/Time Billing Date Format = Short Date ClientID Short Text 5 Client ID Foreign Key ServID Short Text 3 Service ID Foreign Key HoursBilled Number Double Hours Billed Client table: Field Name Data Type Field Size Caption Comments/Other ClientID Short Text 5 Client ID Primary Key, 5 digit number ClientTypeID Short Text 3 Client Type ID Foreign Key ClientName Short Text 40 Client Name Address Short Text 50 Address City Short Text 25 City State Short Text 2 State Default Value = "TX" Zip Short Text 10 Zip Code Zip Code Input Mask (Click on at end of text box) Phone Short Text 15 Phone Number Phone Number Input Mask Validation Rules *: · ClientID – (Like “#####”)*. Must be a 5-digit number. - Don’t forget the Validation Text! *Note: the validation rule is inside the parentheses. Be sure you don’t include the parentheses ( ) in your validation rule. Your import will not work if this is done incorrectly. 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 40 customers in 4 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. . If the process is not successful, the most common issue is with the validation rules you set up at the end of Page 2. If not set up correctly, the validation rule will cause the import to fail. 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 ServID 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. 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 ServID (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. 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. 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) 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 ServID 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 ServID 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. Service data: You should have three ServIDs: ACO, COS, and TAS. The corresponding Descriptions should be as follows: ACO – Accounting Services COS – Consulting Services TAS – 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 six client types. Suggested clients types follow: ClientTypeID ClientTypeDesc IDI Individual SPO Sole Proprietor PAT Partnership COR Corporation LLC Limited Liability Company EST Estates and Trusts OTH Other 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 four (4) client types. Add three (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 6 work weeks (30 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 twice once during the 6 weeks and use all 3 of your services. At least half of the clients (20) should be billed at least three times. At a minimum, that will require you to make at least 100 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 ServID. 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). 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. 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): 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) 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): ADDITIONAL REQUIREMENTS: 1. Create queries to answer the following questions: Query will be named by requirement (ex – 1a) a. What was the average amount billed per transaction? (This query should return a single number.) b. What was the average amount billed for each service type? (Sort descending by Description) c. What was the average amount billed per client type? (This query should return a single number per client type.) 2. Create the following query and report: Query will be named the same as the report name a. Amount Billed by Client . Include the following in your report: Client Name, Transaction Number, Billing Date, Hours Billed, 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. *** 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. *** DELIVERABLE: 1. Upload your database file (xxx_Billing.accdb) to Canvas. Any assignments turned in after the due date/time are subject to a 20% penalty for each day it is late. 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. If you need help with this project, you should seek the help of the instructor rather than other students (including tutors). 1 Sheet1 ClientID ClientTypeID ClientName Address City State Zip Phone 10000 Muna Baral 1260 Morgan Dr Irving TX 76038 9849596397 10001 Nabin Baral 1261 Morgan Dr Irving TX 76038 9842057647 10002 Nikita Baral 1262 Morgan Dr Irving TX 76038 9851034748 10003 Nischit Baral 1263 Morgan Dr Irving TX 76038 9841317623 10004 Jivan Baral 1060 N Main st Euless TX 76039 9842057641 10005 Sabita Baral 1061 N Main St Euless TX 76039 9842057641 10006 Samip Baral 1405 Tennis Drive Bedford TX 76022 9842057642 10007 Shailu Baral 1406 Tennis Dr Bedford TX 76022 9842057643 10008 Sunoj Dawadi 1407 Tennis dr Bedford TX 76022 9842057644 10009 Mina Pyakurel 1408 Tennis Dr Bedford TX 76022 9842057645 10010 saurav KC 1409 Tennis Dr Bedford TX 76022 9842624272 10011 Sushant DB 1060 Walnut Hill Irving TX 76032 9826243625 10012 Prakash Aryal 1061 Walnut Hill Irving TX 76032 9825242526 10013 Geeta Aryal 1062 Walnut Hill Irving TX 76032 3174411784 10014 Prakriti Kharel 1063 Walnut Hill Irving TX 76032 3174411384 10015 Bazinga Kharel 1064 Walnut Hill Irving TX 76032 9922552273 10016 Sammy Kharel 1065 Walnut Hill Irving TX 76032 9027343211 10017 Sanjib Dada 1066 Walnut Hill Irving TX 76032 4174419852 10018 Sushmita Kafle 1067 Walnut Hill Irving TX 76032 9843152432 10019 Saman Baral 1068 Walnut Hill Irving TX 76032 3177625378 10020 Sajal Rai 1069 Walnut Hill Irving TX 76032 1843524622 10021 Kumar Kafle 1321 Walnut Hill Irving TX 76032 9724352627 10022 Binnya Kafle 1322 Walnut Hill Irving TX 76032 9722222222 10023 Puru Koirala 1323 Walnut Hill Irving TX 76032 8174411784 10024 Paola Koirala 1324 Walnut Hill Irving TX 76032 9176725374 10025 Munny BC 1311 Plum Vista Arlington TX 76055 3426243829 10026 Tara Niraula 1312 Plum Vista Arlington TX 76055 1800652332 10027 Lila Bhattarai 1312 Plum Vista Arlington TX 76055 9852041150 10028 Dipti KC 1314 Plum Vista Arlington TX 76055 9810116467 10029 Prisu Kc 1315 Plum Vista Arlington TX 76055 9803639266 10030 Nia Sharma 1316 Plum Vista Arlington TX 76055 5424266529 10031 Rubal Sharma 1317 Plum Vista Arlington TX 76055 9810110101 10032 Riya Shah 1318 Plum Vista Arlington TX 76055 4218761234 10033 Sonia bc 1319 Plum Vista Arlington TX 76055 6189872534 10034 Sabina BC 1211 Plum Vista Arlington TX 76055 4413562718 10035 Amrit Thapa 1212 Plum Vista Arlington TX 76055 2178276354 10036 Aayush Pyakurel 1213 Plum Vista Arlington TX 76055 2177777777 10037 Shahil Thapa 1214 Plum Vista Arlington TX 76055 2173334256 10038 Bibek Aryal 1215 Plum Vista Arlington TX 76055 2716253844 10039 Pina Raimajhi 1216 Plum Vista Arlington TX 76055 9183645252