using excel and access together chapter extension 6
TRANSCRIPT
![Page 1: Using Excel and Access Together Chapter Extension 6](https://reader036.vdocuments.us/reader036/viewer/2022062404/551c58d0550346a5458b50e6/html5/thumbnails/1.jpg)
Using Excel and Access Together
Chapter Extension 6
![Page 2: Using Excel and Access Together Chapter Extension 6](https://reader036.vdocuments.us/reader036/viewer/2022062404/551c58d0550346a5458b50e6/html5/thumbnails/2.jpg)
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?
![Page 3: Using Excel and Access Together Chapter Extension 6](https://reader036.vdocuments.us/reader036/viewer/2022062404/551c58d0550346a5458b50e6/html5/thumbnails/3.jpg)
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
![Page 4: Using Excel and Access Together Chapter Extension 6](https://reader036.vdocuments.us/reader036/viewer/2022062404/551c58d0550346a5458b50e6/html5/thumbnails/4.jpg)
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
![Page 5: Using Excel and Access Together Chapter Extension 6](https://reader036.vdocuments.us/reader036/viewer/2022062404/551c58d0550346a5458b50e6/html5/thumbnails/5.jpg)
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
![Page 6: Using Excel and Access Together Chapter Extension 6](https://reader036.vdocuments.us/reader036/viewer/2022062404/551c58d0550346a5458b50e6/html5/thumbnails/6.jpg)
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
![Page 7: Using Excel and Access Together Chapter Extension 6](https://reader036.vdocuments.us/reader036/viewer/2022062404/551c58d0550346a5458b50e6/html5/thumbnails/7.jpg)
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
![Page 8: Using Excel and Access Together Chapter Extension 6](https://reader036.vdocuments.us/reader036/viewer/2022062404/551c58d0550346a5458b50e6/html5/thumbnails/8.jpg)
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
![Page 9: Using Excel and Access Together Chapter Extension 6](https://reader036.vdocuments.us/reader036/viewer/2022062404/551c58d0550346a5458b50e6/html5/thumbnails/9.jpg)
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
![Page 10: Using Excel and Access Together Chapter Extension 6](https://reader036.vdocuments.us/reader036/viewer/2022062404/551c58d0550346a5458b50e6/html5/thumbnails/10.jpg)
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
![Page 11: Using Excel and Access Together Chapter Extension 6](https://reader036.vdocuments.us/reader036/viewer/2022062404/551c58d0550346a5458b50e6/html5/thumbnails/11.jpg)
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
![Page 12: Using Excel and Access Together Chapter Extension 6](https://reader036.vdocuments.us/reader036/viewer/2022062404/551c58d0550346a5458b50e6/html5/thumbnails/12.jpg)
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
![Page 13: Using Excel and Access Together Chapter Extension 6](https://reader036.vdocuments.us/reader036/viewer/2022062404/551c58d0550346a5458b50e6/html5/thumbnails/13.jpg)
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
![Page 14: Using Excel and Access Together Chapter Extension 6](https://reader036.vdocuments.us/reader036/viewer/2022062404/551c58d0550346a5458b50e6/html5/thumbnails/14.jpg)
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
![Page 15: Using Excel and Access Together Chapter Extension 6](https://reader036.vdocuments.us/reader036/viewer/2022062404/551c58d0550346a5458b50e6/html5/thumbnails/15.jpg)
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
![Page 16: Using Excel and Access Together Chapter Extension 6](https://reader036.vdocuments.us/reader036/viewer/2022062404/551c58d0550346a5458b50e6/html5/thumbnails/16.jpg)
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
![Page 17: Using Excel and Access Together Chapter Extension 6](https://reader036.vdocuments.us/reader036/viewer/2022062404/551c58d0550346a5458b50e6/html5/thumbnails/17.jpg)
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
![Page 18: Using Excel and Access Together Chapter Extension 6](https://reader036.vdocuments.us/reader036/viewer/2022062404/551c58d0550346a5458b50e6/html5/thumbnails/18.jpg)
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
![Page 19: Using Excel and Access Together Chapter Extension 6](https://reader036.vdocuments.us/reader036/viewer/2022062404/551c58d0550346a5458b50e6/html5/thumbnails/19.jpg)
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
![Page 20: Using Excel and Access Together Chapter Extension 6](https://reader036.vdocuments.us/reader036/viewer/2022062404/551c58d0550346a5458b50e6/html5/thumbnails/20.jpg)
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
![Page 21: Using Excel and Access Together Chapter Extension 6](https://reader036.vdocuments.us/reader036/viewer/2022062404/551c58d0550346a5458b50e6/html5/thumbnails/21.jpg)
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
![Page 22: Using Excel and Access Together Chapter Extension 6](https://reader036.vdocuments.us/reader036/viewer/2022062404/551c58d0550346a5458b50e6/html5/thumbnails/22.jpg)
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
![Page 23: Using Excel and Access Together Chapter Extension 6](https://reader036.vdocuments.us/reader036/viewer/2022062404/551c58d0550346a5458b50e6/html5/thumbnails/23.jpg)
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
![Page 24: Using Excel and Access Together Chapter Extension 6](https://reader036.vdocuments.us/reader036/viewer/2022062404/551c58d0550346a5458b50e6/html5/thumbnails/24.jpg)
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
![Page 25: Using Excel and Access Together Chapter Extension 6](https://reader036.vdocuments.us/reader036/viewer/2022062404/551c58d0550346a5458b50e6/html5/thumbnails/25.jpg)
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
![Page 26: Using Excel and Access Together Chapter Extension 6](https://reader036.vdocuments.us/reader036/viewer/2022062404/551c58d0550346a5458b50e6/html5/thumbnails/26.jpg)
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
![Page 27: Using Excel and Access Together Chapter Extension 6](https://reader036.vdocuments.us/reader036/viewer/2022062404/551c58d0550346a5458b50e6/html5/thumbnails/27.jpg)
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
![Page 28: Using Excel and Access Together Chapter Extension 6](https://reader036.vdocuments.us/reader036/viewer/2022062404/551c58d0550346a5458b50e6/html5/thumbnails/28.jpg)
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
![Page 29: Using Excel and Access Together Chapter Extension 6](https://reader036.vdocuments.us/reader036/viewer/2022062404/551c58d0550346a5458b50e6/html5/thumbnails/29.jpg)
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
![Page 30: Using Excel and Access Together Chapter Extension 6](https://reader036.vdocuments.us/reader036/viewer/2022062404/551c58d0550346a5458b50e6/html5/thumbnails/30.jpg)
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
![Page 31: Using Excel and Access Together Chapter Extension 6](https://reader036.vdocuments.us/reader036/viewer/2022062404/551c58d0550346a5458b50e6/html5/thumbnails/31.jpg)
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
![Page 32: Using Excel and Access Together Chapter Extension 6](https://reader036.vdocuments.us/reader036/viewer/2022062404/551c58d0550346a5458b50e6/html5/thumbnails/32.jpg)
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
![Page 33: Using Excel and Access Together Chapter Extension 6](https://reader036.vdocuments.us/reader036/viewer/2022062404/551c58d0550346a5458b50e6/html5/thumbnails/33.jpg)
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
![Page 34: Using Excel and Access Together Chapter Extension 6](https://reader036.vdocuments.us/reader036/viewer/2022062404/551c58d0550346a5458b50e6/html5/thumbnails/34.jpg)
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
![Page 35: Using Excel and Access Together Chapter Extension 6](https://reader036.vdocuments.us/reader036/viewer/2022062404/551c58d0550346a5458b50e6/html5/thumbnails/35.jpg)
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
![Page 36: Using Excel and Access Together Chapter Extension 6](https://reader036.vdocuments.us/reader036/viewer/2022062404/551c58d0550346a5458b50e6/html5/thumbnails/36.jpg)
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
![Page 37: Using Excel and Access Together Chapter Extension 6](https://reader036.vdocuments.us/reader036/viewer/2022062404/551c58d0550346a5458b50e6/html5/thumbnails/37.jpg)
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
![Page 38: Using Excel and Access Together Chapter Extension 6](https://reader036.vdocuments.us/reader036/viewer/2022062404/551c58d0550346a5458b50e6/html5/thumbnails/38.jpg)
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
![Page 39: Using Excel and Access Together Chapter Extension 6](https://reader036.vdocuments.us/reader036/viewer/2022062404/551c58d0550346a5458b50e6/html5/thumbnails/39.jpg)
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
![Page 40: Using Excel and Access Together Chapter Extension 6](https://reader036.vdocuments.us/reader036/viewer/2022062404/551c58d0550346a5458b50e6/html5/thumbnails/40.jpg)
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
![Page 41: Using Excel and Access Together Chapter Extension 6](https://reader036.vdocuments.us/reader036/viewer/2022062404/551c58d0550346a5458b50e6/html5/thumbnails/41.jpg)
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.
![Page 42: Using Excel and Access Together Chapter Extension 6](https://reader036.vdocuments.us/reader036/viewer/2022062404/551c58d0550346a5458b50e6/html5/thumbnails/42.jpg)
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
![Page 43: Using Excel and Access Together Chapter Extension 6](https://reader036.vdocuments.us/reader036/viewer/2022062404/551c58d0550346a5458b50e6/html5/thumbnails/43.jpg)
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
![Page 44: Using Excel and Access Together Chapter Extension 6](https://reader036.vdocuments.us/reader036/viewer/2022062404/551c58d0550346a5458b50e6/html5/thumbnails/44.jpg)
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
![Page 45: Using Excel and Access Together Chapter Extension 6](https://reader036.vdocuments.us/reader036/viewer/2022062404/551c58d0550346a5458b50e6/html5/thumbnails/45.jpg)
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
![Page 46: Using Excel and Access Together Chapter Extension 6](https://reader036.vdocuments.us/reader036/viewer/2022062404/551c58d0550346a5458b50e6/html5/thumbnails/46.jpg)
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
![Page 47: Using Excel and Access Together Chapter Extension 6](https://reader036.vdocuments.us/reader036/viewer/2022062404/551c58d0550346a5458b50e6/html5/thumbnails/47.jpg)
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
![Page 48: Using Excel and Access Together Chapter Extension 6](https://reader036.vdocuments.us/reader036/viewer/2022062404/551c58d0550346a5458b50e6/html5/thumbnails/48.jpg)
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
![Page 49: Using Excel and Access Together Chapter Extension 6](https://reader036.vdocuments.us/reader036/viewer/2022062404/551c58d0550346a5458b50e6/html5/thumbnails/49.jpg)
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
![Page 50: Using Excel and Access Together Chapter Extension 6](https://reader036.vdocuments.us/reader036/viewer/2022062404/551c58d0550346a5458b50e6/html5/thumbnails/50.jpg)
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
![Page 51: Using Excel and Access Together Chapter Extension 6](https://reader036.vdocuments.us/reader036/viewer/2022062404/551c58d0550346a5458b50e6/html5/thumbnails/51.jpg)
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
![Page 52: Using Excel and Access Together Chapter Extension 6](https://reader036.vdocuments.us/reader036/viewer/2022062404/551c58d0550346a5458b50e6/html5/thumbnails/52.jpg)
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
![Page 53: Using Excel and Access Together Chapter Extension 6](https://reader036.vdocuments.us/reader036/viewer/2022062404/551c58d0550346a5458b50e6/html5/thumbnails/53.jpg)
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
![Page 54: Using Excel and Access Together Chapter Extension 6](https://reader036.vdocuments.us/reader036/viewer/2022062404/551c58d0550346a5458b50e6/html5/thumbnails/54.jpg)
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
![Page 55: Using Excel and Access Together Chapter Extension 6](https://reader036.vdocuments.us/reader036/viewer/2022062404/551c58d0550346a5458b50e6/html5/thumbnails/55.jpg)
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
![Page 56: Using Excel and Access Together Chapter Extension 6](https://reader036.vdocuments.us/reader036/viewer/2022062404/551c58d0550346a5458b50e6/html5/thumbnails/56.jpg)
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
![Page 57: Using Excel and Access Together Chapter Extension 6](https://reader036.vdocuments.us/reader036/viewer/2022062404/551c58d0550346a5458b50e6/html5/thumbnails/57.jpg)
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
![Page 58: Using Excel and Access Together Chapter Extension 6](https://reader036.vdocuments.us/reader036/viewer/2022062404/551c58d0550346a5458b50e6/html5/thumbnails/58.jpg)
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
![Page 59: Using Excel and Access Together Chapter Extension 6](https://reader036.vdocuments.us/reader036/viewer/2022062404/551c58d0550346a5458b50e6/html5/thumbnails/59.jpg)
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
![Page 60: Using Excel and Access Together Chapter Extension 6](https://reader036.vdocuments.us/reader036/viewer/2022062404/551c58d0550346a5458b50e6/html5/thumbnails/60.jpg)
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
![Page 61: Using Excel and Access Together Chapter Extension 6](https://reader036.vdocuments.us/reader036/viewer/2022062404/551c58d0550346a5458b50e6/html5/thumbnails/61.jpg)
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.
![Page 62: Using Excel and Access Together Chapter Extension 6](https://reader036.vdocuments.us/reader036/viewer/2022062404/551c58d0550346a5458b50e6/html5/thumbnails/62.jpg)
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?
![Page 63: Using Excel and Access Together Chapter Extension 6](https://reader036.vdocuments.us/reader036/viewer/2022062404/551c58d0550346a5458b50e6/html5/thumbnails/63.jpg)
CE6-63