MIS 300- Access Assignment 1 Instructions Step One: Create a table Open Access Click Blank Database Type "Walf Company your last name) in the File name box Click the Browse button to change the file location, if appropriate Click the Create Button I 2 Step Two: Create fields Click the down arrow beneath the View button Click Design view Type "Business Analyst" as a table name Type and set up the following fields: Field name Business Analyst Number Last Name First Name Street City State Zip Code Salary YTD Incentive YTD Step Three: Insert records Last Name Business Analyst Number Click View arrow Click Datasheet view Smith Franklin Short Text Short Text Short Text Short Text Short Text Short Text Short Text Currency Currency Data Type First Name Francis Joseph Street City 123 Painter St 452 Third Ave Cary 2 15 15 3.0 30 2 5 Bryson City Field Size State NO NC Notes Primary key Caption: BAB Required: Yes Allow zero-length: No Salary YTD 27513 $2900 Zip Code 28713 $3500 Incentive YTD $2500 $2300 3 4 Client Name Street CLA Client Number City State Stag Zip Code GA74 GF56 Jones KD21 KD04 Martinez Step Four: Create a table Click Create-table Click the down arrow beneath the View button Click Design view Type "Client" as a table name Type and set up the following fields Field name Kerry Grant Antiques Granger Foundation Nancy KAL Design Kyle Grocery Short Text Short Text Short Text Short Text Short Text Short Text Currency Currency Number Data Type Street 78 Catawba 65 Simpson 116 Pine 421 First City 5 Pine Cone Lane 1035 Sunset Olive Lane Cary Granger Cary Kyle NC Asheville Amount Paid Current Due Contract Hours YTD Single Create an Excel spreadsheet containing data for Import into your Access Database. Create a new workbook named Clients and type in the following data. Cilent Name State: Zip Code NC NC Mount SC 4 50 30 2 5 Field Size 27513 NC 27036 NC 27513 28798 28801 $4200 28365 $2500 Amount Paid $5,500.00 $0.00 $5,000.00 $3,200.00 Primary key Caption: CL# Notes Required Yes Allow zero-length: No Current Due $3,200.00 50 $6,500.00 50 $3,200.00 $0.00 Contract Hours YTD 34.5 0 30.5 5 817 The . . Bikeshop NS01 Woody 578 Central Sporting Save your Excel workbook Step five: Import an Excel Spreadsheet Return to your Access Database Click the External Data Ribbon Click the Excel button in import & Link group Click Browse button 346 Austin . Buda Step Six: Add a field to the Client table . Click Design View Walburg 0 O Check Table name ("Client") in Import to Table Click Finish button Click Close button Double-click on "Client" table to open it Click Home at Ribbon Add Business Analyst Number Data type: Short Text Field size: 2 Caption: BA O Navigate to the proper folder Click Client file Click Open button Select Import the source data into a new table in the current database Click OK button Make sure "First Row Contains Column Headings" is checked on Click Next button SC NC Step Seven: Resize columns in the table Step Eight: Create a relationship 28796 23819 $1,200.00 $2,750.00 $2.250.00 Close the Business Analyst table & Client table Click Database Tools in the Ribbon Click the Relationships button Click Show Table Select the Business Analyst table to Add to Show Table Double-click the right boundary of the field selectors of each of the fields $1,600.00 14 185 . . . Select the client table to Add in Show Table Click Close in Show Table Click the Edit Relationships button in the Tools group Click Create New button in the Edit Relationship window Click the arrow in the Left table Name box Select Business Analyst Click the arrow in the Left Column Name box Select Business Analyst Number. Click the arrow in the Right table Name box Select Client Click the arrow in the Right Column Name box. Select the Business Analyst Number Click OK button Check Enforce Referential Integrity Click Create button Click the Close button Click Yes to save the Relationship Tools Tab. p Nine: Create queries show names of Business Analyst and their Salaries in a single table: Click Create tab at the Ribbon Click Query Wizard Select Simple Query Wizard Click the Ok button Select the Business Analyst table in the Table/Queries box Select Business Analyst Number Pers Click > button Same for First name, Last Name, Salary YTD A Click the Next button Check the Detail button Click Next Change the Title to Business Analyst Query Check Open the query to view information Click the Finish button ort records: Open Business Analyst-Client Query . Click the Home tab in Ribbon Click View button Click Design View Click on the arrow button next to BAN Select Ascending (or A to 2) Click on the arrow button next to CLA Select Ascending (or A to Z) Click View button Step Ten: Calculating Statistics . Click Create tab at the Ribbon Click the Query Design button in the Ribbon Select Client table at Show Table Click Add button Close Show Table Click the Totals button in Show/Hide group Select the Business Analyst Number in the 1st Field Select Group By in Total Calen Select Amount Paid in the 2nd Field Frierie Select Avg in Total Click Save Click Save button Name it Business Analyst-AvgAmountPaid Click OK Click View arrow . Click Datasheet View Step Eleven: Create a Report . . . . Click the Report Wizard button in the Report group of Create tab Select Table: Business Analyst in the Tables/Queries Select Business Analyst Number, click > button Select Last Name, click > button Select First Name, click > button . Select Salary YTD, click > button Select Incentive YTD, click > button Click Next Select None In Grouping Level Click Next Select Sort by Salary YTD Click to change to Descending Select Incentive YTD Click to change to Descending Select Sort by Last Name