8 Part I ■ Basic Integration Techniques
The Access Table
Now that you have reviewed what type of Excel data format is compatible
with Access, let’s take a brief look at where the Excel data will be going — the
Access table. Access contains many objects that are very useful in manipulat-
ing and presenting data. The table is where the data is stored. Queries, forms,
reports, and other Access objects ultimately reference data in an Access table.
The Table in the Datasheet View
In the sample files for this book, you will find a sample Access database. Open
this database. When the database is open, go up to the application ribbon,
select the Create tab, and then click the Table command button. A new table
similar to the one illustrated in Figure 1-4 is activated in Datasheet view.
You will notice how similar the table is to a blank Excel spreadsheet. Both
are organized by row and column. As with an Excel flat file and indexed list,
each row corresponds to a record of data and each column corresponds to a
field or a unique data element within the record.
As you can imagine, one way to create a table in Access is to start entering
data in the Datasheet view. You can enter new data fields by entering data in
the cells and pressing the Tab key. Enter a new record by pressing Enter. This
method of entry will work if you need to get very small Excel lists into Access.
However, there are much more efficient and powerful methods such as
importing and linking, which you will explore later in this chapter.
The Table in the Design View
At the far left end of the Access ribbon, you will see the View icon. Click the
View icon and select Design from the drop down menu. After being prompted
to save and name the table, you will see the Design view (see Figure 1-5).
Figure 1-4: The Access table in the Datasheet view
Chapter 1 ■ Getting Excel Data into Access 9
Figure 1-5: The Access table in the Design view
Here you can change the properties of the fields in the table: the field name,
the data type (which characterizes what kind of information exists in the field),
and a description (where you can manually enter a more descriptive word or
phrase about the field).
Different Types of Data
Ten data types can be defined in Access. You will probably use just a few of
them. However, this section includes a brief description of all the types in case
you are relatively new to Access. The 10 data types are: Text, Memo, Number,
Date/Time, Currency, AutoNumber, Yes/No, OLE Object, Hyperlink, and
Attachment.
■■ Text: Text is the most common data type you will use in Access. Techni-
cally, any combination of letters, numbers, and characters is text —
even spaces! Keep in mind that any number stored as text cannot be
used in a calculation. Examples of numbers commonly stored as the
Text data type are customer numbers, product SKUs, or serial numbers.
Obviously, you would never perform any calculations on these types of
numbers. The Text data type is limited to a maximum of 255 characters.
■■ Memo: The Memo field allows you to store text data that exceeds the
255-character limit of the text field.
■■ Number: The Number field is a numeric data type that is actually sev-
eral data types under one heading. Use this data type with fields that
might be summed or otherwise modified through arithmetic operations.
10 Part I ■ Basic Integration Techniques
After selecting the Number data type in the Design view of the table, go
to the Field Size field at the top of the Field Properties menu. Selecting
this menu will give you the following choices: Byte, Integer, Long Inte-
ger, Single, Double, Replication ID, and Decimal. The most common
field sizes of the Number data type are Long Integer and Double. Long
Integer should be selected if the numbers are whole numbers (no deci-
mals). Double should be selected if decimal numbers need to be stored
in that field.
■■ Date/Time: The Date/Time data type is used to record the exact time or
date that certain events occurred. The posting date of a transaction and
the exact time a service call was placed are perfect examples of fields
where the Date/Time data type is most useful.
■■ Currency: The Double field size of the Number data type can also be
used for currency fields, but the Currency data type is ideal to store all
data that represents amounts of money.
■■ AutoNumber: This data type is a Long Integer that is automatically cre-
ated for each new record added to a table, so you will never enter data
into this field. The AutoNumber can be one mechanism by which you
can uniquely identify each individual record in a table, but it is best
practice to use a unique record identifier that already exists in your
data set.
■■ Yes/No: There are situations where the data that needs to be repre-
sented is in a simple Yes/No format. Although you could use the Text
data type for creating a True/False field, it is much more intuitive to
use Access’s native data type for this purpose.
■■ OLE Object: This data type is not encountered very often in data analy-
sis. It is used when the field must store a binary file, such as a picture or
sound file.
■■ Hyperlink: When you need to store an address to a web site, this is the
preferred data type.
■■ Attachment: You can use attachments to store several files, and even
different types of files, in a single field. The Attachment field is new for
Access 2007 and stores data files more efficiently than using other fields
like the OLE Object field.
Different data types and field sizes can get overwhelming, but don’t worry.
When you import your data from Excel, Access will choose a default type for
you. Most of the time, the default type is correct. If it’s not, however, you have
the opportunity to change it when importing or when you data is already in
Access.
Chapter 1 ■ Getting Excel Data into Access 11
Table and Field Naming Conventions
There are important conventions and limitations when it comes to naming
your access database tables and fields within those tables. The maximum
length of a field name is 64 characters. Although you should give your fields
descriptive names to clarify what the field represents, try using considerably
less than the 64-character limit. In addition, your field name cannot include a
period (.), an exclamation point (!), an accent grave (`), or brackets ([ ]).
T I P It’s good practice not to put any spaces in field or table names. When
constructing queries or referring to tables in VBA code, spaces in the field
names can lead to problems. If you need to indicate a space in your field name,
use the underscore character (_).
Bringing Your Excel Data into Access
From the prior section, you know that your Excel data must be in flat file or
indexed list format to be compatible with Access. Once you have your Excel
data in the correct form, you can start bringing that data into Access. This sec-
tion introduces the many ways of getting Excel data into Access.
Importing a Worksheet into a New Table
Open Microsoft Access and select the Blank Database icon as demonstrated in
Figure 1-6. On the right, you see an input box used to name your new database.
Figure 1-6: Menu for creating a new database
12 Part I ■ Basic Integration Techniques
N OT E By default, all new databases are automatically created in the My
Documents directory (in Vista, the default directory is the Documents directory).
You can select a different location for your database by clicking the folder icon
next to the input box containing the name of the database.
When you click the Create button, you will have an empty database. At this
point, start by bringing in employee data. The employee data you need comes
from Human Resources. They export it from their HR system into an Excel file
and make some manual adjustments to it each month. You can take a look at
the data in the Excel file EmployeeMaster (see Figure 1-7).
T I P The ExcelMaster.xlsx file can be found within the sample files
for this book installed under C:\Integration.
The data looks to be in indexed list format with Employee Number uniquely
identifying each record. Now let’s import our worksheet to a new Access table
with the Access Import Spreadsheet Wizard. To begin importing an Excel
spreadsheet, simply click the External Data tab and then click the Excel icon
above the import section. If you are familiar with earlier versions of Access,
you will remember that this functionality was buried several layers deep
within the File menu.
Now browse for the file you want to import and then select the option
Import the source data into a new table in the current database. Figure 1-8
shows you what the wizard should look like now.
Figure 1-7: Employee data in Excel
Chapter 1 ■ Getting Excel Data into Access 13
Figure 1-8: Select the data source and select the import option.
Click the OK button to activate the Import Spreadsheet Wizard shown in
Figure 1-9. The first box in the Import Spreadsheet Wizard allows you to spec-
ify the worksheet or range you want to import. If your workbook has more
than one worksheet, all worksheets will be listed here. In this case, there is only
one worksheet. Select the target worksheet and click the Next button.
Figure 1-9: Identify the worksheet or range you want to import.
14 Part I ■ Basic Integration Techniques
The next screen allows you to select whether the source data has headings at
the tops of the columns (see Figure 1-10). As you can see, you simply check the
check box if your source data has headings. Click the Next button to move on.
The next screen allows you to specify the data type for each field (see Figure
1-11). This setting allows you to tell Access whether the given field is a num-
ber, text, currency, date, or so on. The idea is to select each field and check to
make sure the data type for that field is correct. In addition, you can specify
whether any given field is to be indexed. When you index a field, Access cre-
ates a kind of organizational mapping of the field allowing for faster querying
and grouping.
The best way to illustrate indexing is by an analogy. Imagine you had a file
cabinet with 10,000 folders, each dedicated to a specific customer. Now imag-
ine these files were in random order. To access the customer name Schnogg’s
Accounting Service, you would have to pore through every customer file until
you found it. Now imagine finding the file if your customer folders were orga-
nized (or indexed) alphabetically.
When you sort or filter on a non-indexed field, Access will search every
record until the correct record is found. Indexing a field in Access is conceptu-
ally identical to alphabetizing the file system. Indexing a field makes Access
create an organizational scheme for that field such that it can be found much
more rapidly.
Figure 1-10: Specify whether your data source comes with headings.
Chapter 1 ■ Getting Excel Data into Access 15
Figure 1-11: Apply data types and indexing to your fields
T I P You may wonder why you would not index all your fields. Wouldn’t that
make your queries run faster? The answer is an emphatic no! Indexing is a good
idea on fields you expect to filter or join to another table. Indexing is not a
good idea for fields you expect to perform calculations on. You should also be
aware that while indexing can improve the performance for some types of
analysis, other types could actually be slowed by using indexed fields. The
relevance and importance of indexing fields will become clearer as we discuss
different Access query types in Chapter 2.
The next screen allows you to select the primary key (see Figure 1-12). A pri-
mary key is a data field that uniquely identifies each record in a data set. Each
table in a properly designed relational database should contain information
about one entity, and each record should be uniquely identified by one field.
That one field is called the primary key. In this example, the Employee_Num-
ber field contains unique numbers; one for each unique employee represented.
Sometimes the Excel data you import will be in flat file format and will not
have one field that uniquely identifies each record. In these cases, the Import
Spreadsheet Wizard will default to assigning an Autonumber primary key
for you.
16 Part I ■ Basic Integration Techniques
Figure 1-12: Define which field will be your primary key.
A WORD ON CREATING COMPOUND KEYS
Sometimes a flat file will have two or more fields that together uniquely
identify a record. In these cases you will need to create what is called a
compound key. Take the table shown here in Figure 1-13, for example. This
table contains both an invoice number and a product number. There are
duplicate values for each field when looked at separately. A sales rep may have
sold multiple products to a customer on the same invoice. By combining the
invoice and product number, however, you can create a compound primary key
that is truly unique for each record.
Figure 1-13: You will often require a key on two fields to make a unique value.
Chapter 1 ■ Getting Excel Data into Access 17
If you need a compound key, import your data without specifying a key in
the Import Spreadsheet Wizard. Then when your table is created, view your
table in the Design view. When in Design view, select the fields that together
make up your compound key, right-click, and select the primary key icon from
the drop-down menu. Close the table and save your changes.
The last screen of the Import Spreadsheet Wizard asks to you name your
new table (see Figure 1-14). By default, the name of your new table is the name
of your imported worksheet, but you can change the name to suit your needs.
At this point, you can click the Finish button to start the import.
Figure 1-14: Name your imported table and click the Finish button.
N OT E It’s important to note that naming your import table the same name as
an existing table in your database causes Access to give you a warning that you
are about to overwrite your existing table. Be careful that you do not
inadvertently overwrite an existing table with a careless table name choice.
After your data has been imported, you see a dialog box that asks whether
you want to save your import steps (see Figure 1-15). This is a new feature in
Access 2007, allowing you to save time when the same dataset must be rou-
tinely imported. As you can see in Figure 1-15, clicking the Save import steps
check box allows you to save your import steps as a named task that can be
used whenever you need. To recall a saved import task, simply click the Saved
Imports command button under the External Data tab in the Access ribbon.
18 Part I ■ Basic Integration Techniques
Figure 1-15: You now have the option of saving your import steps.
T I P Another quick way to create a new table in Access from Excel is to drag
and drop between the two applications. Open Access and Excel and arrange the
windows so you can see both applications. Now simply highlight the selected
Excel range you want to import and drag it into Access. You’ve immediately
created an Access table.
Be aware that this action actually moves the data from Excel to Access. The
data will no longer be in Excel! To copy the data instead of moving it, hold
down the Ctrl key on your keyboard while you drag from Excel to Access.
Linking an Excel Worksheet to Access
Sometimes the Excel data you want to incorporate into Access is going to
change frequently. Perhaps the Excel data you need is owned by someone else
and is updated daily. Does it make sense to import that data into a new Access
table every time it changes? What if you do not know when it changes, yet it is
critical to have the most up-to-date information for your analysis? In these sit-
uations it makes more sense to get your Excel data by linking a worksheet.
Linking data is different from importing data in that Access references the
linked data in its original location. Importing data brings a local copy of the
information into the Access database. Any changes made to the local copy in
Chapter 1 ■ Getting Excel Data into Access 19
Access do not affect the original spreadsheet. Likewise, any changes made to
the original spreadsheet after importing are not reflected in the Access table.
Conversely, a linked Excel sheet exists in real time. Changes made to the
sheet in the original Excel file are reflected in Access upon refresh. However,
you cannot make changes to the Excel data through Access. Linking is a one-
way street of data flow.
As with the prior example, start by selecting the External Data tab and then
select the Import Excel icon. In the Get External Data dialog box, browse for
the Location_Master.xlsx file. This time, select the option Link to the data
source by creating a linked table (see Figure 1-16). Click the OK button to
continue.
This launches the Link Spreadsheet Wizard shown in Figure 1-17. You will
notice it looks nearly identical to the Import Spreadsheet Wizard. As with the
Import Spreadsheet Wizard, the idea is to go through each screen of the wiz-
ard, answering the questions posed and clicking the Next button. The last step
in the wizard gives you the option to name your linked table. Again, the
default name is the worksheet name or named range. Click the Finish button
to apply the link.
N OT E When linking to a data source, you can’t specify data types, indexing, or
primary keys. Therefore, you can’t see those selections in the Link Spreadsheet
Wizard.
Figure 1-16: To link to a data source, select the Link option.
20 Part I ■ Basic Integration Techniques
Figure 1-17: The steps in this wizard are nearly identical to those of the Import Spreadsheet
Wizard.
Figure 1-18 illustrates the difference in the icons between imported tables
and linked tables. Notice that tables linked to an Excel data source have an
Excel icon.
Earlier you learned that a linked Excel table incorporates changes from the
Excel file to Access but does not allow changes from Access back to Excel. To
test this, open the linked table in Access by double-clicking the linked Excel
table icon. Next, try to change the data by replacing branch number 101313
with 999999. When you try to enter the first 9, you hear an alert. Look in the
bottom-left corner of the screen, and you will see the warning “This Recordset
is not updateable” (see Figure 1-19).
Figure 1-18: A linked table has a different-looking icon than an imported table.
Figure 1-19: You cannot update a linked Excel table through the Access interface.
Chapter 1 ■ Getting Excel Data into Access 21
Now that you’ve proven that you cannot update the Excel spreadsheet
through Access, test whether Access sees changes made to the source data.
Close the Location_Master table in Access by clicking the X in the upper-right
corner of the table window. Open Excel, locate the Location_Master worksheet
and open it. Now try the same exercise of changing branch 101419 to 999999.
Save and close the Excel file. Now open up the Access linked table Loca-
tion_Master to verify that our change carried through (see Figure 1-20).
N OT E You may be wondering why anyone would link to an Excel file when
they can’t change the data in Access. Well, the primary utility of a linked table
is to get the latest data from a source that changes often without having to
import the data repeatedly. Imagine you have an Excel file updated nightly. If
you were to use that file in your Access queries or reports, linking to that
source would allow you to get the latest changes without importing the data
every day.
If the structure of the data source for your linked table changes, you will
have to refresh your link in order to get those structural changes. For example,
if you were to add a column to the Location_Master Excel file, you would not
be able to see that change in Access until you refresh the link to the Loca-
tion_Master Excel file. To do so, use the Linked Table Manager. The linked
table manager can be found under the Database Tools tab in the Access ribbon.
When you click the Linked Table Manager button, the dialog box shown in
Figure 1-21 activates, displaying all the linked tables in the current database. In
this case, you’ll see one linked table: the Location_Master table. Select the
check box beside the linked table and click the OK button. Your data has been
successfully refreshed!
Figure 1-20: Updates to the linked Excel table carry through to Access.
22 Part I ■ Basic Integration Techniques
IMPORTING OR LINKING TO NON-EXCEL DATA
You may want to import and analyze non-Excel data and then send it to Excel
after analysis in Access. One of the most common data types for import is text-
delimited data. Delimited text is simply text where the individual fields in a
record are separated by a specific character like a tab, comma, or space.
Fortunately, Access recognizes delimited text and even allows you to choose
the specific character that separates fields. To get delimited text into Access,
simply choose Text file from the External Data ribbon and walk through the
same process you would when importing or linking to an Excel file.
Figure 1-21: View of the Linked Table Manager
Appending an Excel Worksheet to an Existing Table
The last topic on importing Excel data to Access is how to use the Append
function in the Get External Data dialog box. When you append data to a table,
you essentially add records to the end of the table. This is useful when you
have a process where records for a specific table are added over time (for
example, if new employees come into an organization and need to be added to
the EmployeeMaster table).
Select the External Data tab and click the import Excel icon. Browse for the
file that contains the records you want to add. In the Get External Data dialog
box, select the option Append a copy of the records to the table and specify the
table to which you would like to append your new records. In Figure 1-22, you
can see that new employee records from an Excel file called NewEmployees
are appended to the EmployeeMaster table.
Chapter 1 ■ Getting Excel Data into Access 23
Figure 1-22: Append data by choosing the append options in the Get External Data
dialog box.
Clicking the OK button activates the Import Spreadsheet Wizard. As with
importing and linking data, the idea is to go through each screen of the wizard,
answering the questions posed and clicking the Next button. When you get to
the last screen, click the Finish button to trigger the append action.
Potential Errors When Using the Append Import Wizard
It’s important to note that you may encounter some potential errors when
appending data. Unfortunately, Access is not very clear when it does not like
some portion of the append process. The most likely consequence of an
append error is simply not being able to get your data into Access. The wizard
spits out an error and does not let you continue with the import. Let’s review
some of the most common error messages, the underlying problem, and what
is required to fix it.
■■ Type Conversion Failure: This error occurs when the data type of a
value in the records you are trying to append does not match the data
type of the field to which you are appending. For example, suppose
you originally imported a transaction file and changed the default data
type of the Invoice Number field to Number. Now suppose that invoice
numbers in your organization start coming in as alphanumeric (such as
INV20231). When you append additional data to your transaction table,
you will receive an error because you are trying to put a text field into a
field designated for numbers.
24 Part I ■ Basic Integration Techniques
■■ Key violations: Key violations occur when you try to append a field
with duplicate values to a field designated as a primary key. A primary
key is a data field that uniquely defines a record in a data set. In other
words, a primary key cannot have duplicates. Access will not let you
append a field that has duplicate data to a field designated as a pri-
mary key.
■■ Lock violation: If the table you are trying to append to is open, Access
will not allow you to append the new records until you close the table.
Make sure the table you are appending to is closed before starting the
Append Import Wizard.
USING COMPACT AND REPAIR
Access has a size limit of about 2 GB. When an Access database gets close to
this limit, performance will degrade rapidly. Additionally, the risk of database
corruption increases as well. Properly designed Access Database applications
should rarely approach this size limit. I have used Access databases that
perform tons of query operations on several million record transaction tables
and have rarely approached this limit. The most common cause of approaching
this size limit is from not doing proper maintenance to the database.
When you delete a large table of data in Access, you may think you are
automatically reducing the size of the Access file. In fact, you are not. Access
does not automatically release the storage space associated with a deleted
table. Let’s say you import a million row transaction table to your Access
database only to realize that you forgot to import several relevant fields.
Conscious of the size limits in Access, you delete the table and import another
table with all the appropriate fields. Even though you have deleted the first
table, Access still has not released the space and your file size has doubled.
Another cause of this database bloat occurs when performing a series of action
queries, making numerous intermediate tables for a final analysis. Even if those
intermediate tables are deleted, Access is still allocating space for them.
The solution to this problem is to routinely compact and repair your
database. Press the Office File icon and select Manage, Compact and Repair
Database (see Figure 1-23).
Chapter 1 ■ Getting Excel Data into Access 25
Figure 1-23: Using the Compact and Repair Database Utility in Access
How often you perform this operation really depends on how frequently you
add and then delete tables from your database. The best practice is to compact
and repair your Access database at least every time you open or close it. This
action can be automated for you by selecting the file icon and then selecting the
Access Options button at the bottom of the menu. This opens the Access Options
dialog box. By selecting the Current Database menu item from the list on the left
and checking the Compact on Close check box, you will ensure your database is
compacted and repaired at least every time you close the application.
Summary
Getting your Excel data into Access is the first step in leveraging the powerful
utilities and functionality of Access. Outside of manually entering data into
your Access tables, there are three basic ways of getting Excel data into your
Access processes: importing, linking, and appending.
26 Part I ■ Basic Integration Techniques
Importing data allows you to create a table in Access that you can control
and use to run queries, build reports, and perform any other Access-related
task. Linking data creates a connection to your Excel file, allowing you to
instantly see any changes made to your source file without having to take fur-
ther action. The primary utility of a linked table is to get the latest data from a
source that changes often, without having to import the data repeatedly.
Appending data allows you to add records to the end of the table. This is use-
ful when you have a process where the records for a specific table are added
over time. Now that you understand the different ways of getting your data
into Access, you can effectively use whichever one best suits your needs in a
given situation.
CHAPTER
2
Analyzing Excel Data with
Access Queries
The previous chapter discusses how to get data into Access. Now it’s time to
do something with that data. Once you have your data stored in Access tables,
you can turn your focus to one of the more useful features of Access: Queries.
By definition, a query is a question. For the purposes of this chapter, it is a
question about the data that is stored in tables. Although most Excel users
have heard of Access queries, few have been able to relate to them. The pri-
mary reason is that in Excel, the concept of querying data is a bit nebulous as
it can take the form of different functionalities in Excel. That is to say, there is
no such object called a query in Excel. Instead, you have things like formulas,
AutoFilters and PivotTables. Consider this; in Excel, when you use AutoFilter,
a VLookup formula, or Subtotals, you are essentially running queries against
some set of data to achieve an answer Access queries revolve around the same
concept.
In this chapter, you will explore the world of Access queries, discovering the
various ways you can create analyses from your data.
Introduction to Access Queries
When you are working with data, it is often preferable to work with smaller
sets of data at a time. Although your tables may contain all the records per-
taining to a particular entity, you may need to extract or evaluate only a subset
27
Copyright by webtailieu.net