Unformatted Attachment Preview
Lab Exercise 9: Integration of Access and Excel
As we have learned in previous exercises, Excel and Access each have their own strengths
and ways to use:
• Access – Relational database that can be used to manage and maintain data in
multiple data tables with appropriate relationships set up between the tables.
Capabilities include forms to enter data, use of queries as a mechanism for locating
all records that match a user-defined set of criteria, and reports to create structured
output from the database.
• Excel – Spreadsheet software that allows us to easily manipulate data including
simple mathematical operations, more complex functions, and pivot tables. It also
provides graphing and charting capabilities that are not possible with Access.
To effectively use the data in our databases, it would be extremely useful to be able to utilize
the strengths of both. The purpose of this lab is to show some of the ways the usage of Access
and Excel can be integrated.
Let’s examine some of the ways this integration could be useful working with the database
that was being used in the Access exercises.
• Including all zip code/city name pairs in Cook county into the Access zipCode table
from a list that is available in an Excel spreadsheet from some other source
• Using a query to find what quantities of each item has been purchased by each
• Exporting the results of that query to Excel for further analysis
• Being able to graph various combinations of that data.
There are three starting files for this lab exercise that should be downloaded from your lab
section blackboard site and stored on your documents folder (or elsewhere if you prefer –
just remember where):
• Lab9.Simple.Graphing.xls – This Excel file will be used to demonstrate the simple
graphing capabilities of Excel.
• Lab9.database.accdb – this database is similar to the database used in Lab Exercise 8.
• Lab9.cook.county.zipcodes.xls – this Excel worksheet contains a list of the zip codes
and cities in Cook County that was found on the internet
Part 1 – Simple Graphing in Excel
There are some relatively straightforward graphing/charting capabilities within Excel. The
first part of this exercise will demonstrate some of those capabilities. Visual representation
of data may often show information that might not be otherwise be recognized. Excel has
many different kinds of charts – bar charts, pie charts, etc. We are going to demonstrate a
basic line chart based on some simple data you might be analyzing.
In the starting Excel file for this part of the exercise, there is a worksheet that contains
quarterly sales of an item for 2017 and 2018. We might be interested in seeing if there is
some pattern in this data and a line chart may make this much more obvious than just looking
at the numbers.
Action 1 – Open the starting file – Lab9.Simple.Graphing – In Excel
You should see a worksheet with a column for the quarters and another for the sales.
Action 2-Select the data in cells A1-B9
When you select the data, it should be highlighted and look like the following:
Now that the data is selected, Excel can easily generate different kinds of charts. The column
headings should be included in data selection so that Excel can know how to label the charts
Action 3 – Generate a Line Chart
• Click on Insert Ribbon
• Use Line Chart Pull down menu
• Select the first option which is a simple line chart
This will generate a line chart with the Sales on the Y axis and the Quarter on the X axis as
Looking at the graph, you can see that there is an upwards trend in the numbers. There are
lots of options and different kinds of charts that can be generated from data such as this.
Explore some of these options on your own time.
Part 2 – Importing data from Excel into Access
Often, there is data in an Excel spreadsheet that would be useful to bring into Access either
as a new table or appended to an existing table. This would be much more efficient that
entering the data into Access one record at a time.
For our database with the customer, purchase, item, and zipcode tables, the database owner
has determined that they want to be prepared for making sales anywhere in Cook County, IL
and wish to have all of the zip code/city pairs entered into the database appended to the
existing zipCode table.. A spreadsheet has been located containing this information. Rather
than entering each of those pairs in manually one record at a time, it would be more efficient
to import this information from the spreadsheet.
Action 4 – Open “Lab9.database” in Access.
This resin the database with the tables, relationships, and data. This is what will be used to
demonstrate the integration capabilities between Access and Excel. Now, we will import the
zip code information contained in the spreadsheet you should have downloaded.
Action 5 – Click on the External Data item and select “New Data Source”. Select From
File, and Click on Excel
Once you select Excel, you will begin a wizard that will lead you through importing the data.
It will first ask you for the file name and what kind of an import. We are going to be importing
zip code/city pairs and appending to the existing zip code table.
Action 6 - Tell Access where the spreadsheet is and how to import the spreadsheet file.
• Browse to the location of where you stored the zip codes file
• Select the button to append a copy of the records
• Using the pulldown menu, select the name of the ZipCode Table
• Click on OK
This will start up the wizard and lead you through the necessary steps. Since you are
appending to an existing table, the first row of the excel file being imported should have the
field names matching the field names of the table where the data is being imported. In this
case, the two field names are zipCode and city and are already in the downloaded
The screen shown next shows you how the data will be imported. Since the data being
imported has columns that exactly match the table columns, you do not have any options to
select. If you were creating a new table from a spreadsheet, you would be given additional
options. While this is not part of the lab exercise, this is something you may wish to explore
on your own time.
Action 7 – Click “Next” indicating you agree with the way the data is mapped.
Once you click next, another screen will confirm what table the imported data is going to be
appended. You do not need to enter any data on this screen, just click on “Finish”.
Action 8 – Click on finish to complete the import
The wizard will then display the next screen and if you wish to save the import steps. This
would be useful if you had a consistent set of data that you wished to import on some regular
basis. For the lab exercise, we will leave this unchecked. We can then close the wizard.
Action 9 – Click on Close to finish the wizard.
If you open up the ZipCode table in Access you will see that the data from the spreadsheet is
now contained in the table.
It is also possible to import data to Access from a delimited text file, another Access DB, other
databases, or various other sources. Check these out on your own time.
Part 3 – Exporting results from an Access Query to an Excel Spreadsheet
As you learned in Lab Exercise 6, Access provides the ability to generate data based on some
user defined criteria. This data can be pulled from a single table or multiple tables. Obtaining
data from multiple tables requires that the relationships between tables have been
appropriately established in Access.
If we can create a useful set of data from the tables in Access, then it would be useful to be
able to export that data to Excel since Excel has a lot more flexibility to do different kinds of
analysis. For instance, with the Access file we are working with for this lab, it would be useful
to be able to analyze all of the sales information including the customer’s last name, city, date
of purchase, quantity, item name, item price and sales amount.
In the Access file, a query has been provided that pulls information on those items from the
appropriate tables and creates a dataset for all sales.
The screen below shows the design of the provided query. This has been provided to you,
you do not have to recreate it. You will notice that there are no criteria specified. That is
because we want to produce a dataset of all the sales information.
Action 10 – Generate a dataset using the provided query
Use the “PurchaseQuery” in the left hand panel to generate the dataset by double clicking it.
The screen below shows where that is located and the resulting dataset.
LastName, city, date, quantity, itemname, and itemprice are pulled directly from the
appropriate tables. Sales Amount is a calculated field obtained by multiplying Quantity and
Now we have a dataset that includes all of the information about Sales in the database. It
would be useful to be able to export this dataset to Excel for further analysis on product sales,
sales to different customers, sales on given dates, etc. Generally this kind of analysis is easier
to perform in Excel than in Access.
Action 11 – Begin Export Wizard
• Click on the External Data Ribbon
• In the Export area of the ribbon, Click on Export to Excel
This will start up the export wizard and display the following screen:
Action 12 – Setup Export Wizard
• Ensure that the file name is the file name you want created by the export
• Ensure the File Format is set up as Excel Workbook
• Check the box to export data with formatting and layout. This will make sure that the
data in Excel is formatted the same as it was in Excel.
• Check the box to open the destination file when export complete. This will bring you
up in Excel after completion of the operation.
• Click OK
When you click OK, it will execute the export and since you had the “open destination file”
box checked, it will bring up excel with the dataset you exported. This is what you should
Now we can do various analysis on this data using the capabilities of Excel. As an example,
calculate the average sales amount from column G.
Action 13 – Find average sales amount in Excel.
In cell F10, enter the text “Average”, in cell “G10”, enter the function “=Average(G2:G9)” and
you should get the following result.
On your own time, you can try other kinds of analysis on this data.
Now that we have this information in Excel, we can use some of the powerful analysis and
display capabilities in pivot tables and pivot charts. Pivot charts provide a graphical display
of the information in the pivot table. Charts are often a very useful way to display data.
Action 14 – Create a Pivot Table and Pivot Chart
• Select the Insert Ribbon
• Click on Pivot Chart towards the middle
• Use the pull down menu under Pivot Chart to select the option of creating both a pivot
table and a pivot chart
Action 15 – Select Pivot Table Options for Creation
• Select the appropriate range for your pivot table source. In the case of the lab exercise
this should be A1:G9 which includes the header row.
• Indicate you want a new worksheet created
• Click OK to create the new worksheet with the pivot table and the pivot chart
This will create both a pivot table and a pivot chart which can be used to do some of the same
type of analysis you did in Lab Exercises 3 and 4. The new worksheet in your workbook file
should look as follows:
Now we can many of the same kind of analysis that we did with Pivot Tables and also
generate various graphical displays. For purposes of this lab exercise we will do one.
Action 16 – Determine the amount of sales of each item in each city.
• Drag Sales Amount to the “∑ Values” field
• Drag Cities to the “Axis” field
• Drag ItemName to the “Legend” field
This is what you should see (note, you may have to adjust the position of the pivot chart in
case it is overlapping with the pivot table.
This would give you information about which items had the most sales in each city.
As time permits, try some other fields in the different categories of the Pivot Chart to see
what each looks like and consider how that data might be useful.
Being able to combine use of Access and Excel brings together the strengths of both tools.
Access in managing and storing the data and Excel in having powerful analysis tools that help
you understand, visualize and communicate the information.
Elk Grove Village
Elk Grove Village
Country Club Hills
La Grange Park