Week 12 BB Exercise 12-3

Week 12 BB Exercise - BB Company Exercise OBJECTIVES To learn to use Microsoft Access to create tables from external data sources create your own

Info iconThis preview shows pages 1–4. Sign up to view the full content.

View Full Document Right Arrow Icon
BB Company Exercise OBJECTIVES: To learn to use Microsoft Access to create tables from external data sources, create your own tables, and establish relationships between databases. To prepare an Excel spreadsheet for import into an Access table Naming a range to import into a database Creating a calculation using an ‘IF’ expression To add data to an existing table To create Queries and build Reports on Tables and Queries Part 1 Prepare an Excel spreadsheet for import We will now open an Excel spreadsheet and name the range that contains data, then add a conditional formula to the spreadsheet in order to calculate the annual salary for active employees. 1. Open the file BBEmployeeInfo.xls in Excel 2. Highlight the selection A5 through N2527 3. From the menu, select I nsert then N ame , then D efine (you will notice at the bottom, the box "Refers to" contains the range $A$5 : $N$2527) 4. In the box, "Names in Workbook" type: EmployeeInformation , then click Add 5. Go to Column N, Row 6 (N6) where we will build a calculation based on an "IF" statement First, click the fx button and choose the “IF” formula, a wizard will pop-up. Objective: Calculate the Annual Salary for Active Payroll employees (Column K) IF the contents of Cell K6 is “A” , Multiply the Payrate X 2080, if not, 0 Refer to the image below to create this calculation…. . 6. Once you have created the IF statement in cell N6, copy the formula to all cells in Column N. 7. Save the file as EmployeeInformation.xls on the hard drive. (remember the path of the file ie: C:\Student )
Background image of page 1

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
PART 2 Create a New Access Database and Import Data Now we are ready to start in MS Access and import 3 tables into a new database. 1. Close Excel 2. Open Microsoft Access 3. Chose to start a NEW BLANK DATABASE 4. Name the database as requested on the next screen type “ EmployeeInfo.mdb 5. Click CREATE 6. Now we are ready to import data tables into Access 7. Click External Data then Excel , then Browse to find your data and find the file EmployeeInformation.xls 8. Press OK and the File Import Wizard will now pop-up to assist you in the process. 9. Click on the button for “ Show named ranges ” and select the range we created in Excel called Employee Information then click NEXT
Background image of page 2
Now, you will need to click the box which tells Access that the “ First Row Contains Column Headings Then click
Background image of page 3

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
Image of page 4
This is the end of the preview. Sign up to access the rest of the document.

This note was uploaded on 09/27/2010 for the course ACCT 425 taught by Professor Terawooten-tanner during the Summer '10 term at Franklin.

Page1 / 13

Week 12 BB Exercise - BB Company Exercise OBJECTIVES To learn to use Microsoft Access to create tables from external data sources create your own

This preview shows document pages 1 - 4. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online