CHAPTER 1 D MA TE RI AL Access Basics PY RI GH TE When working with Access for the first time, it is tempting to start filling tables right away and querying data to get fast results, but it is important to understand the basics of the relational database concept before pounding away at data. A good understanding of how a relational database works will help you take full advantage of Access as a powerful data analysis solution.
4 Chapter 1 Figure 1-1 The Database window has seven main sections you can work with: Tables, Queries, Forms, Reports, Pages, Macros, and Modules. What is a table? One way to think of a table is as a collection of data concerning a specific entity (for example, customers, branches, transactions, products, and so on). You want each of these entities to have its own unique table.
Access Basics Figure 1-2 Opening the table in Datasheet view allows you to view and edit the data stored in the table. T I P The number of records in a table is visible at the bottom left of the Datasheet view, next to the record selectors. Opening a table in Design view Through the Design view of a table, you are able to set the field names and data types. To get to the Design view of the CustomerMaster table, right-click the CustomerMaster table and select Design View.
6 Chapter 1 Figure 1-3 Opening the table in Design view allows you to add field names or change existing ones. Exploring data types The field’s data type ensures that only a certain type of data is allowed in the field. If a data type is tagged as a number, Access will not allow any text to be entered into that field. By setting the data type of each column, you go a long way toward ensuring the integrity and consistency of the data.
Access Basics Memo If you need to store text data that exceeds the 255-character limit of the Text field, you should use the Memo field. Long descriptions or notes about the record can be stored in fields of this type. Number The Number type is used for all numerical data that will be used in calculations, except money or currency (which has its own data type). Actually, Number is several data types under one heading.
8 Chapter 1 OLE Object This data type is not encountered very often in data analysis. 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.
Access Basics If you are neither importing nor linking data, the ideal way to create a table in Access is with the Design view. You looked at the Design view previously and saw that it is essentially a list of columns and data types in the table. Here you can enter the names of every column and its matching data type. Creating a table with Design view Imagine that a company’s human resources department asks you to create a simple list of employees in Access.
10 Chapter 1 Figure 1-4 Open the New Table dialog box and double-click Design View. Now you can save and name your table by selecting File → Save. Give the table an appropriate name, such as “Employees” or “EmployeeMaster.” Keep in mind that at this point, this table contains no data. You can start entering employee information directly into the table through the Datasheet view. If you have a table with a small number of records, you can enter your records manually.
Access Basics Tricks of the Trade: Working with the Field Builder A great tool for beginning Access users is the Field Builder. It works as a wizard to help you build your table and also gives you practice selecting data types. In the Design view, select the column for which you would like to build a field and click the Build button on the toolbar. In Figure 1-6, you can see what the Field Builder looks like.
12 Chapter 1 Field properties After entering field names, data types, and descriptions, you can set individual field properties for each column, which will affect how the data is stored and presented, among other things. The list of field properties is dependent on the data type chosen for that field. Some field properties are specific to Text fields, and others are specific to Number fields. The field properties are located in the Design view of a table on the General tab at the lower left.
Access Basics USING THE INPUT MASK WIZARD There is an extremely helpful tool called the Input Mask Wizard. To call this wizard, place your cursor inside the field for which you need an input mask. This will make visible a button with the ellipsis dots (...). Click the newly visible button to activate the Input Mask Wizard. The wizard provides the most common examples of Input Masks and even allows you to test their behavior. Figure 1-7 shows the Input Mask Wizard.
14 Chapter 1 Default Value An important database concept, Default Value can help save time in the data entry process. The default value is automatically placed in a column every time a new record is added. Defaults can be overridden, so your column is not forced to have only that particular value. Required Another important property, Required simply forces a user to enter some value, using the proper data type, in the designated field.
Access Basics Tricks of the Trade: Sorting and Filtering for On-the-fly Analysis There is inherent functionality within Access that can assist you in performing quick, impromptu data analysis. SORTING It is significantly safer to sort data in an Access table than in an Excel spreadsheet. One of the potential problems with sorting data in Excel has to do with the free-form nature of a spreadsheet, which allows you to inadvertently sort only one column in your dataset.
16 Chapter 1 To demonstrate this functionality in action, open the CustomerMaster table and right-click in the State column. The popup menu shown in Figure 1-9 activates. Type CA in the Filter For text field to filter only those customers based in California. Figure 1-9 The Filter For functionality allows you to filter your data on-the-fly. You can easily remove the filter and restore the table to its natural state by clicking the following: Records → Remove Filter/Sort.
Access Basics Importing With importing, you are making a copy of the data and filling a newly created table with the copied data. After importing, the data is disconnected from the source from which it was imported. If any future changes are made to the outside source, they will not be reflected in the Access data. This is true in the other direction, in that changes to your Access data will not affect the original source.
18 Chapter 1 WAR N I N G It is important to remember that Access does not let go of disk space on its own. This means that as time goes on, all the file space taken up by the data you imported will be held by your Access file, regardless of whether or not the data is still there. With that in mind, it is critical that you “compact and repair” your Access database regularly to ensure that your database does not grow to an unmanageable size, or even worse, become corrupted.
Access Basics T I P The Import Spreadsheet Wizard attempts to make an educated guess as to what data type to set for each column. Still, it is a good idea to check the data type of each column after importing the data, because you may need to make some changes. Importing data from text files Similar to importing data from spreadsheets, the data in text files must be in a consistent format in order for the wizard to extract the information correctly.
20 Chapter 1 Figure 1-11 Data is usually stored in an Excel spreadsheet using the flat-file format. In order to get the customer information for each invoice, several fields exist for customer-specific information: customer name, address, city, and so on. Because most firms sell to customers more than once, you can see that customer information is often repeated. Duplicate information is one of the main drawbacks of the flat-file format.
Access Basics Figure 1-12 The last record of CORRUL Corp. was not correctly updated to the new address. If the City data is not properly updated everywhere, when you attempt a by city filter/analysis, you will not get accurate results. Some of the invoice records could reflect the incorrect state locations of the customer. The attributes of data can and often do change, and if these changes are not accurately recorded, your data analysis will provide an incorrect picture of the actual situation.
22 Chapter 1 of that data. The problem is that the data has not really been related; it has simply been shown how it could relate to each other on a particular spreadsheet tab. A different tab may choose to “relate” the data in a completely different way. The problem for the analyst is that if there were relationships between the data that were consistent or even permanent, it would be easier to somehow reflect them in a behind-the-scenes representation of the data.
Access Basics Figure 1-13 A one-to-many relationship between tables can be identified by the infinity symbol on the line connecting the tables. Creating and editing relationships In the Relationships window, you can add tables by right-clicking the display and selecting Show Table. Once your tables have been added, relationships can be established by dragging one field from one table to a field in another table. Figure 1-14 shows the Edit Relationships dialog box that then appears.
24 Chapter 1 Referential integrity In addition to establishing relationships between tables, you are able to enforce certain rules that guide these relationships. For example, if you have an Invoice table with a Customer_Number foreign key, you will not be able to add an invoice for a customer number that does not exist in the other table. You must add the new customer to the customer table before the new foreign key can be placed in the invoice table.
Access Basics What is a query? By definition, a query is a question. For our purposes, it is a question about data, which is stored in tables. Queries can be exceedingly simple, like asking what all of the data in a table is. Queries can also be quite complex, testing for different criteria, sorting in certain orders, and performing calculations. In Access, two main types of queries exist: select and action queries. Select queries are perhaps the most common type.
26 Chapter 1 The Query By Design interface Go to your sample database and select the Queries tab. At the top, double-click “Create query in Design view.” The Show Table dialog box opens, sitting on top of a blank Query By Design (QBD) interface, as shown in Figure 1-15. Some also call this the query grid or design grid. When creating your “question” of the data, the first thing you must determine is from which tables you need to retrieve data.
Access Basics Figure 1-16 The Query By Design interface. For this example, select the following three fields: Branch_Num, Customer_Name, and State. To select fields from the top half of the QBD, you can either double-click the field or click it once and drag it down to the bottom half. Now that field will be included in the output of the query. Figure 1-17 shows you how the Query By Design should look after selecting the output fields.
28 Chapter 1 Figure 1-18 The Datasheet view of query shows the results of the query. Sorting query results Here, you examine how you can sort the results of this query. Just as you sorted in Excel, you are going to select a column and choose between ascending sort and descending sort. In the bottom half of the QBD, you’ll notice the Sort row on the grid. This is where you can select either one or multiple sort columns.
Access Basics Figure 1-19 The sort order options for a column are provided by the Query By Design interface. Figure 1-20 The results of the query are now sorted in ascending order by the State field.
30 Chapter 1 Filtering query results Next, you examine how you can filter the query output so that you retrieve only specific records to analyze. In Access, this filter is also called Criteria. You can see the Criteria section for each column added in your QBD. This is where you will enter the value, or values, for which you would like to query. When entering a value in the Criteria section, all records that match it are returned in the query output.
Access Basics Figure 1-22 The results of the query will be all records that match the criteria. Querying multiple tables This section explores how you can perform a query on multiple tables. Remember that you split the data into separate tables. You used Relationships to define the logical relationships between the data. Now you will query from the tables, based on the relationships that were established. Suppose you want to see the customer transactions from Colorado.
32 Chapter 1 Once the TransactionMaster has been added to the QBD, you will notice that the previously established relationship is represented, as shown in Figure 1-23. A line connecting the two tables indicates that you don’t have to set the relationship in the QBD; it is already there. You can see the one-to-many relationship, indicating possible multiple records in TransactionMaster for each individual customer in the CustomerMaster table.
Access Basics Figure 1-24 Run this query to examine the results. Refining the query further You can narrow down your results even further by filtering the query results according to a certain date. As you can see, there are several rows of criteria cells. These allow you to enter multiple criteria from which to filter. One thing to keep in mind is that each separate criteria row functions as its own separate set of criteria. Take a look at how this works.
34 Chapter 1 Click the Criteria cell in the Invoice_Date column and type “4/20/2004”. When you click out of that cell, you will notice that the date is now surrounded by number signs, as shown in Figure 1-26. When running the query, only results matching the two criteria (State = “CA” and InvoiceDate = “4/20/2004”) are returned. Now look at using multiple criteria for a single field. Suppose you want to bring in invoices for the data 11/19/2004 as well as 4/20/2004.
Access Basics Figure 1-27 Each line of criteria will be evaluated separately. Using operators in queries You can filter for multiple criteria on any given field by using operators. The following operators allow you to combine multiple values in different logical contexts so that you can create complex queries. Or Either condition can be true. Multiple criteria values for one field can either be separated by a criteria line or combined in one cell with the use of the Or operator.
36 Chapter 1 In Similar to Or. Tests for all records that have values that are contained in parentheses. For example, you can filter for both California and Colorado by typing “In (“CA”, “CO”)” in the criteria field. Not Opposite of writing a value in criteria. All records not matching that value will be returned. For example, you can filter for all states except California by typing “Not “CA”” in the criteria field. Is Null Filters all records that have the database value Null in that field.
Access Basics Figure 1-29 Here are your query results. Exporting query results Now that you have learned the basics of creating queries, you need to be able to export these results back to Excel or another format. The simplest way to do this in Access is to right-click the query after it has been saved. Select Export and choose the appropriate file type. The query will take a snapshot of the data at that moment in time and save the results in the requested format.
38 Chapter 1 Go up to the application menu and select Tools → Office Links → Analyze It with Microsoft Office Excel. In just a few seconds, Excel will open up and only the data you selected will be output to a spreadsheet with labels. Figure 1-31 demonstrates how this looks. This allows you to do some on-the-fly analysis between Access and Excel without saving a gaggle of temporary files. Figure 1-31 Your data has been output to Excel.
