Pinterest Stumbleupon Whatsapp

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 5 Easy to Use & Free Alternatives to Microsoft Access 5 Easy to Use & Free Alternatives to Microsoft Access Microsoft Access is a complex database tool with a steep learning curve. If you're searching for more intuitive and free alternatives with comparable features, we've got you covered! Read More 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 The Swizzle: Unsubscribe From Mailing Lists & Reduce Incoming Spam The Swizzle: Unsubscribe From Mailing Lists & Reduce Incoming Spam Read More , 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.

new column

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 Need Help with Excel Formulas? 7 Resources to Consult Need Help with Excel Formulas? 7 Resources to Consult Excel is the spreadsheet gold standard. If you are required to use Excel and yet have to familiarize yourself with it, these resources will quickly introduce you to the basics and more. Read More .

default entry

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 A Quick Tutorial On Forms In Microsoft Access 2013 A Quick Tutorial On Forms In Microsoft Access 2013 What's the difference between a well maintained and a neglected database? Potentially an easy to use data entry form. Creating an Access Form is easy, if you follow our tutorial. Read More . 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.

form design

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 8 of the Best Free Collaborative Tools For Programmers 8 of the Best Free Collaborative Tools For Programmers If you're a programmer and you aren't yet sharing or collaborating, you're behind the curve. Reap the benefits of collaboration with the right web apps. Read More  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.

manual entry

Alternatively, you can import a file that contains pre-existing data. Access 2013 provides support for a number of file types, including Excel spreadsheets Excel Vs. Access - Can a Spreadsheet Replace a Database? Excel Vs. Access - Can a Spreadsheet Replace a Database? Which tool should you use to manage data? Access and Excel both feature data filtering, collation and querying. We'll show you which one is best suited for your needs. Read More , 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.

Using Queries

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.

query wizard

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.

query edit

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.

Leave a Reply

Your email address will not be published. Required fields are marked *

  1. Sarah
    June 15, 2017 at 7:27 am

    Thanks for these tutorials, very useful! I have made a table in access and then created a form which I will be using to collect and input questionnaire responses simultaneously. What I'm wondering is how you save responses for individuals. Also, I will be carrying out follow up questionnaires so would be looking at collecting multiple sets of responses for each participant and preferably storing/being able to relate them to one another?