Ok, so I feel compelled to write about Access, seeing as it's the only Office product I haven't covered in my short tenure as a contributor to this fine magazine. Access will probably have the least amount of coverage in this column, as I don't think a lot of you are necessarily using it, and as it's hard for me to be positive about the program at all. To its credit, though, it works fine as a standalone database with fewer than a couple thousand records. The reason I am tormented every day is because I have it hooked up to a backend SQL Server database with over a hundred thousand records in one table alone, and I expect the forms to work the way they should. But enough of my issues...

Microsoft Access is an efficient choice for small databases without the need for advanced functionality. It is an excellent all-in-one tool to use for personal databases that will not grow very large. I used to tell my students to use Access to create a database that tracks household inventory, so that you can get used to how it works - if you have to use Access at work. It has sample databases already in it, and you can customize the tables to your personal preference. 

The purpose of this article is to cover some basic tips for Access, in particular, data entry shortcuts, creating a parameter query, and creating a switchboard. I will use the Inventory Control database template for my discussion. Directions to create the database follow. Keyboard combinations will be in bold.

To cover Access properly, I will briefly introduce some very common database terminology. The basic possible units (objects) of an Access database are tables, queries, forms and reports, each of which have a tab or button for easy navigation. The data is stored in tables, which have fields to further classify the data into logical groupings. Queries are used to ask specific questions of the data, and can combine data from multiple tables to serve as the basis for a form or report. Forms are a user-friendly way to enter and display data. Reports are used for data printouts. The basic design of a database, which you will see if you choose one of the database templates, is to logically group the data into separate tables. So if I go to File...New in Access, and click on the database tab in the New dialog box, I can choose Inventory Control to get the inventory database template. Once I name it, and accept the defaults in the wizard, it will create the inventory database for me, prompting me to enter personal information at the initial startup. Once I close that box, I see what's called a switchboard. We will cover how to create that in a few paragraphs. You should also see a minimized main database window that has all of the objects. When you maximize that window, you should see the different objects that the Inventory Control database template automatically created for you. 

Ok, so maybe you don't need the employees table or the transactions table. Whatever you don't need, you simply select and delete. Don't be afraid when you see the relationships warning dialog box, it's simply telling you that you will have to delete the relationships first. What are relationships? Well, I could go into a whole separate article about relationships and normalization. But to be brief, relationships are set up as a way to retrieve data from separate tables and ensure that the data is entered correctly. So to move on to data entry tricks, we will open the Products table. A lot of people think the best way to enter data is through forms. While that may be a pretty way to enter data, it is usually not the fastest, especially if you have a lot of repetitive data. Here in the datasheet view of the Products table, we can enter data fast by tabbing through the fields, and using a couple of handy-dandy keyboard shortcuts. I have two lamps that I want to inventory. For this example, we will be populating 4 fields, Product, Product Description, Unit Price, and Lead Time (Product Id will be automatically filled out for you). Tab to the Product field, and type, "Lamp". Tab to Product Description and type "Brown with tan cover". Then skip to Unit Price. Type in "25". Skip to Lead Time, and type in the keyboard combination Ctrl+; . That will automatically fill in the current date. Now, those are all the fields we want to enter, so to save the record, hit Shift+Enter on your keyboard. I see a lot of beginning Access users clicking on the save floppy disk icon, thinking they are saving the record. That's not how Access works. The save floppy disk icon will save any changes to the table, not the data. The good news is that Access automatically saves the data as you go along. The trick to tell whether the data is saved is to look at the record selector column, the very first column with no header. If the data is saved, it will display a triangle pointing right, on the current record. If you are editing the data, it will display a pencil with two dots before it. So now that you've saved the record, let's log the second lamp. Under Product, hit the keyboard combination, Ctrl+' . That will repeat the data in the record directly above it, for that field. For the Product Description field, type "Florescent Orange with Purple Trim" . For the Unit Price enter "5" .  For the Lead Time field, hit the keyboard combination Ctrl+Shift+; . This will automatically fill in the current time.

Now that we've filled in some data, we can move on to querying it. So we want to be able to search through the data and very specific items, based on a criteria that we provide. To do this, we will create a parameter query, and have Access prompt us for the criteria every time the query is run. So, go to queries and create a new query in design view. Select the Products table and then close the show table dialog box. From the Products table, double click on the Product and Product Description fields to place them in the grid (you can also drag and drop). We want to search by Product Description, so that we can enter data about the product, and it will display all records with our search criteria anywhere in the field. In the criteria row, under product description, type the following: 
Like "*" & [Enter a color for the description] & "*" 

Here are the basic pieces of this statement: Like and * are wildcards, meaning to search for anything like whatever you type in – it does not have to match the field exactly. Having the * before and after means that the criteria can occur anywhere in the field, not just at the beginning. [Brackets] include the text for the prompt. Run the query and observe the prompt. Type in "brown" and click ok. You should see the first record for the first lamp. Hit the keyboard combination Shift+F9 (function key) to requery. This time type in purple, and you should see the second lamp you typed in, the really pretty one. ;) Press Shift+F9 to requery again, and this time, leave the criteria blank and just click ok to see all records. Parameter queries are fun and exciting for all ages, and the nice thing about them is that you can tie them to reports or forms and see only the records you want to see! It's simple - when creating your form or report, you just select the parameter query you created as the data source. 

So now that we've conquered queries, we can move on to switchboard. You saw a switchboard when the database started up, a form aptly named, "switchboard."  A switchboard is neat because it gives your users choices that they can understand. If you have users that will be accessing your database, you will definitely want a switchboard so that you don't confuse them. Despite the mysteriousness and power surrounding a switchboard, it is nothing more than a form with command buttons. You can create a blank form and then use the ever versatile control wizard to create command buttons that will open forms or reports, or even exit the database. Once you've made the form incredibly beautiful and it has all the possible buttons you desire, then it's time to make it the switchboard for the database. You can name the form whatever you want, it's just easier to remember it if you name it "switchboard." Close the form, and in your main database window screen, go to Tools...Database Utilities...Startup. In the Startup dialog, you should see a Display Form/Page dropdown box to the right. Simply select your form to use as the switchboard, and voila! you have a switchboard! Now every time you start the database, this form will be displayed. The Startup dialog is also pretty handy for locking down the database to prevent the users from doing something stupid. Check out some of the options you can take away from your users! 

So there you have it – data entry tips, parameter queries, and switchboards to make your database fun and efficient. I invite you to write in your Access questions; I will answer them with unabated zeal and joy in my heart.