ITS Course Notes
Importing External Data and Action Queries
In this workshop we will import a dataset from Excel and then use Access Action Queries to
massage our data.
‘External Data’ is any dataset that does not currently
reside within the database you have open.
This data could be a part of another Access
database, an Excel workbook, Text file (Tab or
Comma delineated), XML file, MS Share Point List
(We won’t even talk about this!) or carious other
files that can be considered a data set.
Access allows all these datasets to be imported into
a traditional Access Database Table.
In this exercise, we will be importing an Excel dataset
into Access and then applying Action Queries to
massage that data.
I clicked the Excel button on the External Data
Tab/Import Ribbon and got the ‘Get External Data –
Excel Spreadsheet’ window.
Browse normally to find the file you want and then click
the 1st Radio Button labeled ‘Import the Source data…’
Concepts to understand…
The Import choice that we are choosing, basically copies the data into an Access table in raw form. We will have
the option to import the data into a new table or append to an existing table. This is usually the best choice.
The Append choice is a shortcut that automatically appends your imported data into an existing table, although you
have the option of creating a new table for it in the process. This is new in 2007 and seems odd to me, not a whole
lot different than the first choice above.
For both of these choices, you are copying the data into your database and severing the link to the original data
source. So, if changes are later made to the original source (our Excel Workbook) those changes do NOT propagate
into your Access table.
The Link choice is the answer to this. Sometimes a dataset already exists, and you want to interact with it in your
Access database, and the original source is continually updated, and you want those updates to update your data in
This last functionality (Link) is NOT part of this exercise.
Analyzing the Source dataset
At left is my source dataset in an Excel Worksheet.
For my Access table, I am only interested in the basic
demographic fields such as name and address fields. I
could delete the unwanted fields from the Excel file and
import, but there are usually reasons why that is not the
best choice, so we will import this data raw.
This file has a header row, row 1, with the field names.
This is a huge help.
So, it is always a good idea to, if possible, take a look at the original data source in its original application, like this
spreadsheet opened in Excel. Make whatever changes you want to before, or at least be aware of what is there
and what you will be importing. For example, if you will be eventually appending (adding) this data to an existing
table, do the field names match? Last Name in this file, LName in your Access table?
These are things to be aware of.
Back in Access, now we know what we want, and
have identified the file and that we want to import
it, let’s go!
Importing the Data
Continuing, the next step in the Import Wizard displays
the possible components of your original dataset.
The spreadsheet I am importing from has only one sheet,
which is not named, thus no choices are shown. I could
also choose to see any ‘Named Ranges’ in the workbook.
This one has none.
So, so far, I am just verifying that this is the right dataset.
The next screen shows the same data, but adds a
checkbox to let you tell Access that this dataset
has a header row. Ours does.
This tells Access that the first row is headers and
not an individual record.
I skip through this screen. It allows me to do
a number of things that are best done in
Access once our data is in an Access table.
I could set data types or chose to NOT import
a field, all shortcuts that could be handy, but
that also could cause problems.
This screen asks whether we want to set a ‘Primary Key’.
We said earlier that all tables that will ever be relational
with other tables MUST have a primary key.
Choice one is to let Access add it, which in this screen it
did, adding field ‘ID1’ and setting it to AutoNumber.
In class we discuss that there are no existing fields that
will serve properly as a Primary Key. That kills choice 2.
I opt for choice 3, no Primary Key, because this table will
never be relational (in our exercise) and thus, doesn’t
need one. We discuss why in class.
The Imported Dataset
The last step:
We need to name the table into which we
are importing this data.
I choose the name ‘Raw Imported Data’
to be clear about what this table is to be.
Clicking ‘Finish’ finalizes our import.
Done importing, we are asked if we want to save
the steps we just did.
This is so quick and easy to do I have never
Note my new table is listed in my table list in the left
pane. I opened it and it is at left.
Turns out that I left Access’ Primary Key added; no harm.
Note also that there is a new table called ‘4#25 Import
Errors’. If you look at this one, it shows that it reset a
data type that was incompatible with Access to
something Access can understand. The Field involved is
one we won’t use anyway so I am ignoring it.
Once you are finished importing, it is important to look at the data, and even check data types in design view to
see if everything is what you want.
This is raw data. It will rarely be consistent with what is already in your database. You could have made
changes, as stated earlier, to the source dataset but often that dataset needs to stay intact for other uses. So, I
believe it is best to import the data raw into Access as we’ve done and use Access Action Queries to clean the
Now we will start building some Action Queries to go through a few common scenarios.
The queries we have worked with so far have all been ‘Select’ queries. Select, as in, the query has selected data for
us based on our criteria.
Action Queries are essentially Select Queries that are given a more involved job to do. All Action Queries start life
as Select Queries. Once a Select Query is created, it can then, in Design View, be turned into an Action Query.
In most cases, Action Queries are not saved for re-use. Their duties are usually short term and the process they
need to perform would only be done once. In fact, in most cases, running an Action Query more than once would
There are 4 main types that we will work with here:
1) Make Table: Creates a table from the query results.
2) Append: Adds the query results to an existing table.
3) Update: Changes data in an existing table based on your specifications.
4) Delete: Deletes records in a table based on your specifications.
Everything that an Action Query can do can be done manually, of course, but in tables with many records, that can
be extremely tedious and time consuming. These Action Queries allow you to affect thousands, even hundreds of
thousands, of records instantaneously.
To get started, as in the Query class, click onto the
On the ‘Other’ ribbon, click the ‘Query Design’
Starting With a Select Query
We’ve done all this before (Course G04) so we’ll
breeze through this.
In the ‘Show Table’ screen, click on the ‘Raw
Imported Data’ table.
This is the time to get rid of those unwanted fields,
so for exercise and this Query, I choose to include:
Last Name, First Name, Address 1, Address 2, City,
State, and Zip.
Include the fields that have data you want to keep.
I have run my new query and see the results at right.
Note the unwanted fields are not included.
There are 2832 records, as there were in the original
Excel data Source and also in The Raw Imported
Remember, this query does NOT store data. If we were to delete the Raw Imported data table now, then re-run
this query, it would be empty.
Now, click back into design view to turn this into a Make Table query.
Make Table Query
Back in Design view, you will automatically be on the
‘Query Tools/Design’ tab. On the ‘Query Type’ ribbon,
you will see our choices.
Click the ‘Make table’ button.
In the ‘Make Table’ screen, type your new table a
name; I chose ‘Clean Imported Data’.
The default is to create this table in the current
database; you could opt to create this in another
database that you have access to.
Now, I run my query. As always, the easiest way is to
click the Red Exclamation Point button.
You will be stopped with the warning that you are
about to paste 2832 records (rows) into a new table.
Do it! It will look like nothing happened.
Your Query Design view looks the same.
But, now in your table list in the left pane, you will
see a new table called ‘Clean Imported data.
You can now close this query and delete it. No need
to leave it there and have others (or you, when you
forget) run it again and have it create a new table,
Here is my new table opened up.
It looks exactly like my run query a few minutes ago.
But! This is a table and is now storing this data.
Now we could delete our original Raw Entered Data
Another Make Table Query
Now that we have our Clean Raw Data table, let’s pull
some records from it.
Use it to build a Select Query, using all our fields, as at
Let’s add a criterion, ‘Albany’ to pull out all the records
that are identified as Albany in the City field.
If this is new to you, review notes for Course G04, we’re
not going to repeat everything here.
At right is our query result.
Note at the bottom left of the query table the record
count indicates that we have found 424 Albany records.
Remember these numbers as you go through these
action queries, as the numbers will keep coming up in
alert messages and it will be handy to know whether the
numbers are correct.
Now, back to Design view.
Click the ‘Make Table’ button again, and this time
call the new table ‘Capital District Data’.
You’ll see where I am going with this shortly.
You will be alerted that you are pasting 424
records… look familiar?
When you are done and run the query, it will
behave as before and look like nothing happened.
I deleted my query and then opened my new Capital District
Note it is now listed in the table list at left.
Note 424 records as we expected.
I could now delete my Clean Data table but I need it for
another exercise shortly.
To simplify my life over the next few exercises, I created a basic select query using the fields from my Clean
Imported Data table and called it ‘G06 Test Query’. I’ll be using it a few times.
So, I created my query just as I did last exercise,
using ‘Clean Imported Data’ and all of its fields.
I am simulating getting new imported data and
pulling out the records I need.
In that scenario, I might be looking for Albany from
this new set of data, but I already pulled my Albany
records from the dataset I am using here.
So, I query for ‘Delmar’.
I ran the query normally and the result is at right.
Note there were 98 records found for Delmar.
Click back to Design view…
Now, choose ‘Append’ from the Query type buttons.
The ‘Append’ screen will ask what table we want to
append these records to. Choose ‘Capital District Data
from the available tables. Run the Query.
When you append, it is critical that Field Names and
Datatypes are identical. They are here.
The Query result is at right.
Note we now have 522 records in the table (424
Albany and 98 Delmar.
On your own, create a new Append query that
adds records from Latham.
You should end up with 544 records if you are
using this dataset.
At left is my Capital District data table with the Latham records
added, which brings my total to 544 records.
A common scenario is that you find that some data was
entered incorrectly, or, possibly some part of the data has
aged (selling price?) and needs to be updated.
An Update Query allows you to change record content on a
field by field basis either by expression or simple substitution.
We’ll use simple substitution in our exercise.
So, this exercise assumes that we just heard from data
entry that all the records that should have been Troy,
were misidentified as Delmar. This kind of thing does
happen. F or purposes of brevity, let’s assume this
would be the only problem, though there would be
others issues associated with this (zip code?).
I have to build a new query, because now the data
that I need to work with is in the Capital District data
table, not the Clean Imported Data Table. You know
what to do…
I built the basic structure of the query, then clicked the
The query grid now had an ‘Update to’ row. So, for
criteria, under the ‘City’ field, I typed Delmar (Those are
the wrong records I need to find) and in the update row,
under City, I typed ‘Troy’ (what I need to change these
Then I run the query… It tells me I am updating 98 rows.
My result is at right. Note that my former Delmar
records now indicate the city as Troy.
An expression would be used when, for example, raising
a sell price by 10%. In the Update to row, you would
build an expression that would look something like this:
=sum([sellprice]*1.10). This assumes you have a field
A ‘Delete’ query will delete all records found via any
To continue our exercise, we now find out from
management that all those former Delmar records that
we just updated to Troy are all so badly messed up that
it is best to just delete them and start over.
I start with my basic query and click the ‘Delete’ button.
This adds a ‘Delete’ row.
Note the detail at right…
The default command in the Delete row is ‘Where’.
That means a specific criteria will be found. I typed
Troy to find the Troy records, specifically.
The ‘From’ command allows a range, e.g. from a certain
date and later.
I run the query and am told I am deleting 98 rows. The number is
My result is at right.
Note that all my former Delmar, recent Troy
records are now deleted. I’m down to 446
records (544-98) which is a correct total.
In general, you have to keep your brain at
attention, especially when dealing with
Update and delete queries, because you need
to know that what they are doing actually is
specifically what needs to be done.
Finally, as mentioned before, you should consider these Action Queries to be single use queries, so get into the
habit, right now, of deleting them once you have run them. Leaving them hanging around will just tempt you or
others to run them, causing them to attempt to do again what they just did.