Using Excel and Access Together
Chapter Extension 6
CE6-2
Study Questions
C o p y r i g h t © 2 0 1 4 P e a r s o n E d u c a t i o n , I n c . P u b l i s h i n g a s P r e n t i c e H a l l
Q1: Why use Excel and Access together?
Q2: What is import/export?
Q3: How can you create charts with Excel?
Q4: How can you create group totals in Access?
Q5: How can you use Excel to graph Access data?
Q6: How can you use Access to report Excel data?
Q7: How can you combine Excel and Access to analyze data?
CE6-3
Q1: Why Use Excel and Access Together?
C o p y r i g h t © 2 0 1 4 P e a r s o n E d u c a t i o n , I n c . P u b l i s h i n g a s P r e n t i c e H a l l
• Access is a DBMS for keeping track of things and creating reports
• Excel is good for creating sophisticated graphs and analyzing data
• Eliminates re-keying data, reduces labor and errors
CE6-4
Q2: What Is Import/Export?
C o p y r i g h t © 2 0 1 4 P e a r s o n E d u c a t i o n , I n c . P u b l i s h i n g a s P r e n t i c e H a l l
• Process of transferring data from one system to another– Creates connection to source data– Connection closed after data transferred
CE6-5
Import/Export of Text Data
C o p y r i g h t © 2 0 1 4 P e a r s o n E d u c a t i o n , I n c . P u b l i s h i n g a s P r e n t i c e H a l l
• Comma-delimited text fileTab used to create a tab-delimited text
file
CE6-6
Open a database, click on External Data tab
Select file that contains data to importClick Import and OK
Multiple-panel wizard opensSpecify data file is delimited
Name fields and data type
Creating a Text File in Access
C o p y r i g h t © 2 0 1 4 P e a r s o n E d u c a t i o n , I n c . P u b l i s h i n g a s P r e n t i c e H a l l
CE6-7
External Data Menu Choice
C o p y r i g h t © 2 0 1 4 P e a r s o n E d u c a t i o n , I n c . P u b l i s h i n g a s P r e n t i c e H a l l
CE6-8
Importing Text Data into Access - Step 1
C o p y r i g h t © 2 0 1 4 P e a r s o n E d u c a t i o n , I n c . P u b l i s h i n g a s P r e n t i c e H a l l
CE6-9
Importing Text Data into Access: Specifying a Delimited File - Step 2
C o p y r i g h t © 2 0 1 4 P e a r s o n E d u c a t i o n , I n c . P u b l i s h i n g a s P r e n t i c e H a l l
CE6-10
Importing Text Data into Access: Specifying a Comma Delimited File - Step 3
C o p y r i g h t © 2 0 1 4 P e a r s o n E d u c a t i o n , I n c . P u b l i s h i n g a s P r e n t i c e H a l l
CE6-11
Importing Text Data into Access: Naming & Describing Columns During Import - Step 4
C o p y r i g h t © 2 0 1 4 P e a r s o n E d u c a t i o n , I n c . P u b l i s h i n g a s P r e n t i c e H a l l
CE6-12
Data After Import
C o p y r i g h t © 2 0 1 4 P e a r s o n E d u c a t i o n , I n c . P u b l i s h i n g a s P r e n t i c e H a l l
CE6-13
Q3: How Can You Create Graphs with Excel?
C o p y r i g h t © 2 0 1 4 P e a r s o n E d u c a t i o n , I n c . P u b l i s h i n g a s P r e n t i c e H a l l
• Data from Computer Budget workbook used for following examples
– Pie chart– Column chart
CE6-14
Sample Pie Chart
C o p y r i g h t © 2 0 1 4 P e a r s o n E d u c a t i o n , I n c . P u b l i s h i n g a s P r e n t i c e H a l l
CE6-15
Creating the Pie Chart
C o p y r i g h t © 2 0 1 4 P e a r s o n E d u c a t i o n , I n c . P u b l i s h i n g a s P r e n t i c e H a l l
CE6-16
Selecting the Chart Tools
C o p y r i g h t © 2 0 1 4 P e a r s o n E d u c a t i o n , I n c . P u b l i s h i n g a s P r e n t i c e H a l l
CE6-17
Sample Column Chart
C o p y r i g h t © 2 0 1 4 P e a r s o n E d u c a t i o n , I n c . P u b l i s h i n g a s P r e n t i c e H a l l
CE6-18
Creating a Column Chart
C o p y r i g h t © 2 0 1 4 P e a r s o n E d u c a t i o n , I n c . P u b l i s h i n g a s P r e n t i c e H a l l
CE6-19
Creating the Chart Title
C o p y r i g h t © 2 0 1 4 P e a r s o n E d u c a t i o n , I n c . P u b l i s h i n g a s P r e n t i c e H a l l
CE6-20
Volunteer database from CE 5 used in following examples
• TV station manager wants to know TotalDonations for each date of fundraising effort. Also wants to know if some dates are better than others.
• Using WORK table, create a query to group all donations by date and sum TotalDonations for each group.
Scenario:
Q4: How Can You Create Group Totals in Access?
C o p y r i g h t © 2 0 1 4 P e a r s o n E d u c a t i o n , I n c . P u b l i s h i n g a s P r e n t i c e H a l l
CE6-21
Open Volunteer database
Click Create
tab
Click Query Design
Select WORK table
Click Add
Click Close
How Can You Create Group Totals in Access? (cont’d)
C o p y r i g h t © 2 0 1 4 P e a r s o n E d u c a t i o n , I n c . P u b l i s h i n g a s P r e n t i c e H a l l
CE6-22
Selecting WORK Table for the Query
C o p y r i g h t © 2 0 1 4 P e a r s o n E d u c a t i o n , I n c . P u b l i s h i n g a s P r e n t i c e H a l l
CE6-23
Adding Date and Totaldonations to the Query
C o p y r i g h t © 2 0 1 4 P e a r s o n E d u c a t i o n , I n c . P u b l i s h i n g a s P r e n t i c e H a l l
CE6-24
Selecting Sum in Total Row for TotalDonations
C o p y r i g h t © 2 0 1 4 P e a r s o n E d u c a t i o n , I n c . P u b l i s h i n g a s P r e n t i c e H a l l
CE6-25
Results of Query with Group by Date
C o p y r i g h t © 2 0 1 4 P e a r s o n E d u c a t i o n , I n c . P u b l i s h i n g a s P r e n t i c e H a l l
CE6-26
Double-click Name to insert it into query
table
Double-click ProspectID and TotalDonations
Click Totals icon to insert Total row in query table
In Total row under
ProspectID, select Count
In Total row under
TotalDonations, select Sum
Create a column heading
ProspectID column by keying “Hours
Worked:”
Create a column heading for
TotalDonations as “Total
Obtained:”
Steps for Creating a Query to Compute Total Hours and Donations for Each Prospect
C o p y r i g h t © 2 0 1 4 P e a r s o n E d u c a t i o n , I n c . P u b l i s h i n g a s P r e n t i c e H a l l
1 2 3 4 5 6 7
CE6-27
Process for Creating a Query to Compute Total Hours and Donations for Each Prospect
C o p y r i g h t © 2 0 1 4 P e a r s o n E d u c a t i o n , I n c . P u b l i s h i n g a s P r e n t i c e H a l l
CE6-28
Results of the Query in Previous Slide
C o p y r i g h t © 2 0 1 4 P e a r s o n E d u c a t i o n , I n c . P u b l i s h i n g a s P r e n t i c e H a l l
CE6-29
Adding Average Donations Per Hour
C o p y r i g h t © 2 0 1 4 P e a r s o n E d u c a t i o n , I n c . P u b l i s h i n g a s P r e n t i c e H a l l
CE6-30
Results of the Query with Average
C o p y r i g h t © 2 0 1 4 P e a r s o n E d u c a t i o n , I n c . P u b l i s h i n g a s P r e n t i c e H a l l
CE6-31
Q5: How Can You Use Excel to Graph Access Data?
C o p y r i g h t © 2 0 1 4 P e a r s o n E d u c a t i o n , I n c . P u b l i s h i n g a s P r e n t i c e H a l l
Import Access query into Excel and use Excel’s graphing capability to display results
To import data into Excel:•Click Data tab •Click Get External Data section, select From Access•Select Volunteer database
CE6-32
Menu to Import Data from Access into Excel
C o p y r i g h t © 2 0 1 4 P e a r s o n E d u c a t i o n , I n c . P u b l i s h i n g a s P r e n t i c e H a l l
1.Click Data tab, then, 2.Get External Data3.Select From Access
CE6-33
Selecting the Query to Import
C o p y r i g h t © 2 0 1 4 P e a r s o n E d u c a t i o n , I n c . P u b l i s h i n g a s P r e n t i c e H a l l
CE6-34
Placing Imported Data into Spreadsheet
C o p y r i g h t © 2 0 1 4 P e a r s o n E d u c a t i o n , I n c . P u b l i s h i n g a s P r e n t i c e H a l l
CE6-35
Spreadsheet with Imported Data
C o p y r i g h t © 2 0 1 4 P e a r s o n E d u c a t i o n , I n c . P u b l i s h i n g a s P r e n t i c e H a l l
CE6-36
Formatted Imported Data
C o p y r i g h t © 2 0 1 4 P e a r s o n E d u c a t i o n , I n c . P u b l i s h i n g a s P r e n t i c e H a l l
CE6-37
Bar Chart of the Imported Data
C o p y r i g h t © 2 0 1 4 P e a r s o n E d u c a t i o n , I n c . P u b l i s h i n g a s P r e n t i c e H a l l
CE6-38
1. Used Access to keep track of volunteers and their received donations, to query and group data—all tasks for which Access is ideally suited.
2. Then, imported that data into Excel and used Excel’s easy graphing capability to create charts.
Reflect on What We Have Done
C o p y r i g h t © 2 0 1 4 P e a r s o n E d u c a t i o n , I n c . P u b l i s h i n g a s P r e n t i c e H a l l
CE6-39
Q6: How Can You Use Access to Report
Excel Data?
C o p y r i g h t © 2 0 1 4 P e a r s o n E d u c a t i o n , I n c . P u b l i s h i n g a s P r e n t i c e H a l l
Suppose you want to produce two different reports from this data
1. Group all expenses for a given expense category to produce an expense total.
2. Group all expenses for particular dates to produce an expense total for each date
Do both by importing Excel data into Access and using Access report generator
CE6-40
Sample Expense Data
C o p y r i g h t © 2 0 1 4 P e a r s o n E d u c a t i o n , I n c . P u b l i s h i n g a s P r e n t i c e H a l l
CE6-41
Creating a Named Range in Excel
C o p y r i g h t © 2 0 1 4 P e a r s o n E d u c a t i o n , I n c . P u b l i s h i n g a s P r e n t i c e H a l l
• Highlight data (including column headings) and click Formulas tab
• In Defined Names section, click Define Name and enter a suitable name
Note: Range names cannot have any spaces, so use underscores.
CE6-42
Creating a Named Range
C o p y r i g h t © 2 0 1 4 P e a r s o n E d u c a t i o n , I n c . P u b l i s h i n g a s P r e n t i c e H a l l
CE6-43
Close Excel
workbook that has
data
Open Access
database for
importing
Click External Data tab, then click Excel in Import section
Click Import source data
into new table in current database
Click OK
Click Show Named Ranges,
select Event_Expenses
Check box for First Row
Contains Column Names
Importing Data in Named Range into a New Access Table
C o p y r i g h t © 2 0 1 4 P e a r s o n E d u c a t i o n , I n c . P u b l i s h i n g a s P r e n t i c e H a l l
CE6-44
Importing Excel Data into Access
C o p y r i g h t © 2 0 1 4 P e a r s o n E d u c a t i o n , I n c . P u b l i s h i n g a s P r e n t i c e H a l l
CE6-45
Importing Excel Data into Access: Importing the Data in the Named Range
C o p y r i g h t © 2 0 1 4 P e a r s o n E d u c a t i o n , I n c . P u b l i s h i n g a s P r e n t i c e H a l l
CE6-46
Importing Excel Data into Access:Access Has Metadata to Guide Import
C o p y r i g h t © 2 0 1 4 P e a r s o n E d u c a t i o n , I n c . P u b l i s h i n g a s P r e n t i c e H a l l
CE6-47
After clicking Finish, Access creates table with metadata
descriptions and places data into Event_Expenses
table
Open Event_Expenses table
Click Create,
click Report in Reports section
Access generates a report to
be modified
to desired format
Creating Expense Reports
C o p y r i g h t © 2 0 1 4 P e a r s o n E d u c a t i o n , I n c . P u b l i s h i n g a s P r e n t i c e H a l l
CE6-48
Grouping Report Data by Expense Category
C o p y r i g h t © 2 0 1 4 P e a r s o n E d u c a t i o n , I n c . P u b l i s h i n g a s P r e n t i c e H a l l
CE6-49
Creating Group Totals
C o p y r i g h t © 2 0 1 4 P e a r s o n E d u c a t i o n , I n c . P u b l i s h i n g a s P r e n t i c e H a l l
CE6-50
Resulting Report
C o p y r i g h t © 2 0 1 4 P e a r s o n E d u c a t i o n , I n c . P u b l i s h i n g a s P r e n t i c e H a l l
CE6-51
In Grouping & Totals section of Design ribbon, click Group &
Sort. Click Add
Click Expense Category, as shown in next
slide
In Design mode, click More and
click Expense totaled
Select Expense for Total On, Show Grand
Total and Show in group footer
Report finished
Q7: How Can You Combine Excel andAccess to Analyze Data?
C o p y r i g h t © 2 0 1 4 P e a r s o n E d u c a t i o n , I n c . P u b l i s h i n g a s P r e n t i c e H a l l
CE6-52
Creating a Query to Sum Expenses by Given Date
C o p y r i g h t © 2 0 1 4 P e a r s o n E d u c a t i o n , I n c . P u b l i s h i n g a s P r e n t i c e H a l l
CE6-53
Creating a Query to Combine Results of Two Other Queries
C o p y r i g h t © 2 0 1 4 P e a r s o n E d u c a t i o n , I n c . P u b l i s h i n g a s P r e n t i c e H a l l
• Merge EventDateTotals query with EventExpenseTotals query– Click Create/Query Design, then Queries tab in Show
Table window, as shown in next slide. – Add both EventDateTotals and EventExpenseTotals to
query– Drag Date field in EventDateTotals and drop on top of
Event Date in EventExpenseTotals query– Add Date, SumOfTotalDonation, and Total Event Expense
to query
– Run (!) query– Save query as Event Results and Expenses
CE6-54
Combining the Results of Two Queries
C o p y r i g h t © 2 0 1 4 P e a r s o n E d u c a t i o n , I n c . P u b l i s h i n g a s P r e n t i c e H a l l
CE6-55
Matching Date Values in Two Queries
C o p y r i g h t © 2 0 1 4 P e a r s o n E d u c a t i o n , I n c . P u b l i s h i n g a s P r e n t i c e H a l l
CE6-56
Query with Columns Added
C o p y r i g h t © 2 0 1 4 P e a r s o n E d u c a t i o n , I n c . P u b l i s h i n g a s P r e n t i c e H a l l
CE6-57
Result of Query
C o p y r i g h t © 2 0 1 4 P e a r s o n E d u c a t i o n , I n c . P u b l i s h i n g a s P r e n t i c e H a l l
CE6-58
Click Data From Access in Get External Data
section of ribbon
Select Access database with
query, and select Event Results and
Expenses
Open Excel workbook and import Event Results and
Expenses query
Import Events Results and Expensesinto Excel
C o p y r i g h t © 2 0 1 4 P e a r s o n E d u c a t i o n , I n c . P u b l i s h i n g a s P r e n t i c e H a l l
CE6-59
Query Imported into Excel
C o p y r i g h t © 2 0 1 4 P e a r s o n E d u c a t i o n , I n c . P u b l i s h i n g a s P r e n t i c e H a l l
CE6-60
Imported into Excel
C o p y r i g h t © 2 0 1 4 P e a r s o n E d u c a t i o n , I n c . P u b l i s h i n g a s P r e n t i c e H a l l
CE6-61
Reflect on What Has Been Done with This Data
C o p y r i g h t © 2 0 1 4 P e a r s o n E d u c a t i o n , I n c . P u b l i s h i n g a s P r e n t i c e H a l l
• Total Donation data originated in Access and summed using an Access query
• Expense data from Excel worksheet Fund Raising Expense imported into Access, and summed in a query
• Results of Total Event Expense query imported back to Excel, and analyzed.
CE6-62
Active Review
C o p y r i g h t © 2 0 1 4 P e a r s o n E d u c a t i o n , I n c . P u b l i s h i n g a s P r e n t i c e H a l l
Q1: Why use Excel and Access together?
Q2: What is import/export?
Q3: How can you create charts with Excel?
Q4: How can you create group totals in Access?
Q5: How can you use Excel to graph Access data?
Q6: How can you use Access to report Excel data?
Q7: How can you combine Excel and Access to analyze data?
CE6-63