While most PC users would claim to be proficient in Office software like Word and Excel, the further reaches of the package are perhaps a little more daunting. Access and similar pieces of database software can be a particularly unfriendly piece of software to get started with — but they pose much less of a challenge if you know what you’re doing.
This guide will take you through the basic steps of creating your database, populating it with information, and using Queries to access it at a glance. Once you understand the fundamentals, you’ll be well on your way to true Access mastery.
Create a Database
Upon opening Access, you’ll be presented with a host of different options and templates. Select Blank desktop database to get started, and choose a file name and a location on your hard drive for it to be saved in. I’m going to create a database of the books that I own, but these same concepts could be used for a mailing list, your phone book or any number of other purposes.
The first column of my database is going to contain the title of each book, so I’m going to use the Click to Add button to stipulate that this is a Short Text entry — you should choose whatever type of entry suits your project. Afterwards, rename the column header to something appropriate. It’s worth renaming your first Table from the default “Table1” at this point, too.
Do the same for all the different columns that you’re looking to include. In my case, that was another Short Text entry for the author’s name, a Number cell for the year of release and a Yes/No checkbox for whether or not the book in question was a hardback edition. I’ve also set up a Date Added field, which we’ll automate in a moment. If you need to edit any data type after creation, head to the Fields tab on the Ribbon, highlight the desired column and change it via the Data Type dropdown menu.
Now switch to Design View using the icon at the bottom right corner of the screen. To automate a timestamp, choose the desired Field Name, select the date format via the dropdown menu and enter =Now() in the Default Value field below. This will autofill the cell with the date that the entry was added whenever a new item is entered into the database. The same method can be used to establish other defaults, whether they’re a standard text entry or a formula like this one.
Establishing a Primary Key
If you’re creating a database that’s going to grow and grow, a Primary Key is vital — but it’s a good habit to get into even if you’re working at a smaller scale. Confusion may arise when two items share the same name, but a Primary Key ensures that there’s another unique identifier available at all times.
Access 2013 creates an ID field by default, which it then automatically populates with a numeric identifier. However, you can specify a different Primary Key if you feel the need. To do so, simply right-click the desired Field Name in Design View and select Primary Key. However, this needs to be a truly unique entry to ensure that things work as they should.
Adding Data with Forms
If you’re looking to enter a lot of data into your Access document, it’s well worth setting up a Form. First, navigate to the Create tab in the Ribbon and click on Form. It’ll create a default layout for you, which you can then edit by dragging individual components to the appropriate position.
To test out your Form, use the View option at the left hand side of the Ribbon to switch to Form View. Here, you’ll be able to add an entry by populating the various fields — do that now to make sure that everything works as intended. To check your progress, head back to the Table from the Form and select Datasheet View. If your first entry is displayed as intended, head back to your Form and save your work.
Other Methods of Entering Data
A Form is a good way of standardizing data entry, especially if more than one user will be using the database. However, it’s not the only way of populating a file in Access. The most straightforward method is to simply add entries manually while in Datasheet View.
Alternatively, you can import a file that contains pre-existing data. Access 2013 provides support for a number of file types, including Excel spreadsheets, CSV files, XML, and HTML documents. To import data from a source, choose the External Data tab from the Ribbon and choose the desired file type. Access will lead you through the necessary steps to import it into your database.
It’s worth noting that Access makes a distinction between importing and linking data. The former is the correct method if your data isn’t going to change, but the latter allows for a separate document to continually be updated, reflecting these changes in Access. Depending on your needs, this might save you some time.
Once your database is sufficiently populated, it’s time to start reaping the benefits of having a great deal of information organized. Queries are a simple but powerful method of sorting through your database quickly and efficiently. To illustrate their usage, here’s a simple Query that will search my database for books from a particular time span.
To create a new Query, navigate to the Create tab in the Ribbon and select Query Wizard, then choose Simple Query Wizard. Select the desired Table, as well as the Fields that you’re interested in — in my case, this is Year of Release as well as the Title, so I know what entry I’m looking at.
On the next screen, decide whether you want to see the Detail view with every field of every record, or just a Summary. Then, select Finish to end the process. You’ll be presented with the data that you selected, and you can use the dropdown in the column header to slim things down even further. I’m looking for books written in the 1970s, so I’m going to click on the Year of Release dropdown then navigate to Number Filters > Between.
I then set an upper and lower boundary for my data, and was presented with a list of the books that released between 1970 and 1980. The same method can of course be used to match text strings, tickboxes or any other kind of information that you might have entered into your database.
Do you have any tips for getting started with Access? Are you struggling with a particular aspect of database creation and need some help? Let us know about your advice and issues in the comments section below.