Figure 1 shows a copy of the Balance Sheet table adjusted so that it contains missing data similar to the situation described. Duplicate data was deleted in each of the three Group columns, leaving only the first unique value. This represents the way that data may come in from Excel without repeating in the columns. To use that data properly, we need to fill in the blanks.Figure 1. Table with Missing Data
Create a new field in the table named “ID Plus One.” The values in this field will take the value in the ID field, which is created by autonumber, and add one to it. This will enable us to connect the data in an offset of one. To add the values to this field, create an Update query with the Field set to “ID Plus One” and the Update To line set to [ID]+1. Run the query and check the table to ensure the update was completed.
This new field will let us compare the data in one record to the data in the preceding record. To do that, create a new Update query using the Copy of Balance Sheet table as the record source. Because we want to link two different fields in that one table, make sure to add it twice to the query design (see Figure 2). Note that the second time the table appears, its name is listed as “Copy of Balance Sheet_1”. Link the ID field in the Copy of Balance Sheet table to the ID Plus One field in Copy of Balance Sheet_1.Figure 2. Query to Fill in Data
The first field we’re going to fill in the blanks for is Group 1. As you can see in Figure 2, the Group 1 field is set to update to [Copy Of Balance Sheet_1].[Group 1], with the Criteria set to “Is Null.” This tells Access to look at the value of Group 1. If it’s empty, it will insert the Group 1 value of the preceding record. In essence, we’re creating an Access version of Excel’s fill handle. Save the query as “Update ID Plus One Values Group 1.” Run it to test that it fills the null values. Then adjust the query to update Group 2 and Group 3, respectively. Be sure to save a copy of each query with the appropriate name.
An alternative method is to fix the data in Excel before you import it. That’s fine if it’s a one-time import, but the advantage of creating this process in Access is that, once set up, it’s easily repeated. The three queries can even be combined into a macro to make it a one-step process, which is great if you need to import the spreadsheet on a regular basis or if there is a great deal of data that would take a long time to correct in Excel.
ADD AN INDEX
In this small example, the Update queries process really fast. But imagine if the data involves hundreds or thousands of lines. You can add indexes to speed up queries on large data. An index stores the location of records based on the field or fields that you choose. After Access gets the location from the index, it can then find the data by moving directly to the correct location, rather than having to search through all the records in the table to find the right data.
While indexes speed up queries, too many of them will slow down the database when updating or adding data. The goal is to only add indexes when appropriate. For example, if Employee ID is the key identifier for records in your database but you also want to quickly look up people by the Last Name field, you can add an index to that table for Last Name.
To add an index, open the table in Design view. On the Design tab of the ribbon, click the Indexes button. Assign the index a name, and then select the field you want to index, such as Last Name, and a sort order. Close the Indexes box when you are done. Now running queries involving the Last Name field will process more quickly.
One question that I am often asked is how to take the sort out of a table. Let’s say you have a table open and right-click on the Last Name field to sort from A to Z. That sort is now saved as a property of the table. In order to remove the sort and return your table to its initial order, go to Design view, click the Property Sheet, and delete the contents of the Order By property.
Download this month’s database here: SF_OCT_2015.
SF BEST PRACTICES
Add indexes to a table to speed up the search for key data elements. But don’t add too many—it will slow data entry.