It’s unrealistic to expect to turn a first-time Access user into an Access power user over the phone. However, if the caller has a lot of experience using Excel, you can often help that person jump-start his or her understanding of how Access works by comparing features of Excel and Access.
For example, a friend called me in a panic, because the Access guru had left the company without documenting any of the tables, forms, and reports he had left behind. My friend was asked to step up and take over. “I know Excel,” my friend said, “but I’ve just never had to use Access before.”
I gave my friend a lesson that consisted mainly of sentences that started out, “You know how you do such-and-such in Excel? Well, to do the same thing in Access, you…” One of the highlights from that lesson was the epiphany my friend experienced when we compared the way Excel sorts records with the way Access sorts the same kinds of records. I hope this lesson will help you in training and supporting new Access users, whether or not they have experience in using Excel.
The worksheet and the datasheet
The first question was, “How do I get to the data?” I compared the Access .mdb file (the database) to the Excel workbook this way: As the Excel workbook can contain many different worksheets of data, an Access database can contain a number of different tables. When we located the correct database, I directed my friend to Access 2000’s Objects pane, where he double-clicked on Tables.
“When you open a table this way, it’s called the Datasheet view,” I said. “It looks and acts a lot like a spreadsheet, except the rows aren’t numbered and the columns aren’t labeled A, B, C, etc.”
So far, so good.
More Access help on TechRepublic
For additional Access lessons, check out these articles:
- “Learn the basics of Microsoft Access 2000”
- “Learn more about the basics of Access 2000”
- “Tips on teaching an introduction to Access”
- “Assessing someone’s Access skills”
Sorting out Sort options
The second question was, “How do I sort these records?” I talked to him about how you sort records in Excel: Click anywhere in the range, go to Data | Sort, and Excel lets you sort on up to three columns.
“Sorting in Access is easier in many ways,” I said. If you want to sort your records on a single column, you have three choices:
- Click anywhere in that column and then click the Sort Ascending button, located on the Table Datasheet toolbar.
- Click in the column on which you want to sort, go to Records | Sort, and choose either the Ascending or Descending option.
- Right-click a column and choose either Sort Ascending or Sort Descending from the context menu.
In the Datasheet view, you can also sort your data on two (or more) columns, as long as they’re adjacent to one another. Access will sort the records starting with the leftmost selected column.
To illustrate this technique, suppose you want to use Excel to sort some trouble tickets by Status (Complete or Pending) and secondarily by Date. Figure A shows how Excel’s Sort dialog box lets you accomplish that task.
|Excel’s Sort dialog box lets you sort records by at least two columns.|
Figure B shows the result of the sort.
|Here’s how our Excel sheet looks after we sorted the records.|
Let’s take a look at how to sort the same records in Access 2000’s Datasheet view. Figure C shows what the table looks like when you use the mouse to select two adjacent columns.
|Select two adjacent columns in Access 2000’s Datasheet view.|
Figure D shows the datasheet after you click the Sort Ascending button on the toolbar.
|Here’s how your Access datasheet looks after sorting.|
Only the beginning
Once I got my friend over his initial fear of the Access interface, our lesson quickly progressed to cover other topics, such as creating queries and reports. In almost no time, he felt confident that he could pick up the slack left behind by the Access guru who had left the company.
Although I won’t go into all the details here, I’d like to close by mentioning some of the other comparisons I made between Excel and Access that helped this particular user:
- AutoFilter is like a Query. In Excel, when you go to Data | Filter | AutoFilter, Excel adds drop-down arrows to your column headings. Clicking on a column’s drop-down arrow displays a list of all the unique values in that column. Clicking Top Ten displays the top ten values in the column, and clicking Custom lets you use Boolean operators to customize the filter. To demonstrate that functionality in Access, I had my friend create a new query in Design View. Although that process takes a little longer than simply clicking on a drop-down arrow, understanding the similarities between Access 2000’s Select Query and Excel 2000’s AutoFilter made it easier for my friend to tackle the Access query grid. He particularly liked the fact that Access lets you save a query as an object separate from the table itself.
- Data | Subtotals is one of the Access Report Wizard options. The other Access topic that perplexed my friend was how to group and subtotal records based on different fields (columns). Since he was well aware of how to use Data | Subtotals in Excel, I challenged him to create a subtotaled report using the Report Wizard in Access, and he was dismayed at how easily he could customize his reports.
Since that first telephone lesson, I’ve spent some time with my friend bringing his Access skills up to speed. I think the lesson for help desk support staff and technical trainers is to find out what applications your students already know and use. Then, as much as possible, compare features between the applications they know and the ones they’re trying to learn.