First steps with Access

Learn, step-by-step to create your first Access database.

The first time you use Access you'll be excused for thinking you're using a very big hammer to crack a very small nut. You're right. Access is an extremely powerful and complex database program and when you use it to manage a very simple database it is overkill. However, there's no better way to begin learning about Access than actually using it.

To get you started, I'll show you how to develop a small database in Access. You will see how to create a table to store your data and then how to enter the data.

Once the data is in the database you'll learn how to get information out of it by printing a report listing all the contents and by searching the data.

I'm assuming that you have never used Access before - or perhaps that you've opened it, taken a look around and closed it thinking it all looks a bit difficult. I agree. At first look Access does appear overwhelming, but, by the time you've finished this tutorial you'll be comfortable using it and ready to try more things.

The data for our database
Since this is a sample database we'll keep it very simple. Here is the information a local cricket club records for its players:

The instructions use Access 2013 but they'll work pretty much the same in any version of Access from Acccess 97 on. If you are using another version, check the screen to find the option which matches the one I have suggested you use.

Before you open Access

Before you launch Access, design your database on paper. To do this, begin by considering what you want your database to do. In this case the club president wants to be able to find quick answers to questions such as "Who hasn't paid their fees?", "Who are the A grade players?" and "Who is due for their 10 year pin this year?".

The president already knows the information he needs to store in the database because it's been recorded using a card system for some time. If this wasn't the case and if you are creating a database, for example, for your CD collection where you haven't had one before then you'll need to consider all the possible questions you'll want the database to provide answers to and the lists you'll want from it. In this case you might want a list of all your CDs by title or by musician. You might want to type in a singer or group and be able to get a list of all the CDs you have of theirs. It's up to you to decide the information your database needs to give you. It's not until you know what you need to get out of a database that you can determine what you need to put into it!

It's worthwhile thinking about this for a minute; you design a database by looking at the end product first – what you want out of it, then you make sure you put into it the information that will allow you to get this out. This is an important step, unless you do it carefully you'll end up with a database full of data that you don't use or that doesn't allow you to get the information you need out of it.

When you have decided the data to put into the database to get the results you want, you need to determine how you'll organise the data. When you're storing people's names it is easier to manage these if you split these into first name and last name. If you do this you can search for a person by last name and create lists in alphabetical order. You can also use the mail merge option to send letters which read "Dear Mary," etc.. If you don't split people's names into first name and last name, then these tasks will be more difficult to complete or you'll end up with letters which read "Dear Mary Brown" or you'll have James Smith appearing before Xavier Brown in an alphabetical listing!

If you are entering addresses, split these into Street Address , Town, Country and Zip code so that you can use this information to create mailing labels and mail merge letters. If you split this information into multiple parts you'll be able, for example, to get a list of everyone who lives in Bath or in France.

Each individual piece of data, for example; last name, zip code , town, becomes one field (or column) in your database table. You need to give each field a name and to specify the type of data you're storing in each field - whether it is text, date, number, Boolean (yes/no), etc.. This is an important step as affects the way you get information out of your database later on and how you search it.

In Access, you'll also need a Primary Key which is a field (or a combination of fields) which are unique to each record. In our case, we couldn't use the last name as our Primary Key because there are some people with the same last name (so it isn't unique) but we could use a combination of first name and last name as that is unique for this data. If you don't have a field or combination of fields which are unique, you can add a counter field to your table which will allocate each entry a unique number – then you can set this as your primary key field.

Our database is a simple two dimensional table but Access is capable of much more complex structures. For example you can have a database containing multiple tables which are related to each other. In our example, we could add a table with the player's statistics for each year they have been playing for the club and we could link this to the player table. When you create a database containing more than one table of data you must determine how your tables relate to each other and how they will be linked so that you can get information from more than one table. In our scenario there's only one table so we'll bypass this step for now.

At the end of the planning stage you should have a list of field names and data types something like this:

Create the Table

Once you've planned your database you're ready to create it so open Access and choose Blank Desktop Database and click OK.

Give your database a name (CricketClub), select a folder to store it in and click Create.

You'll see the Database dialog appear on the screen and you're ready to begin.

You'll create your table in the Design view by clicking the View button on the ribbon, type Clubmembers as the table name when prompted to do so and click Ok. In some earlier versions of Access you will need to choose Table > Create table in Design view.

Click in the text area and type the field name FirstName, press Tab and you'll see Short Text appears as the default data type, press Tab and type a Description for this field and then press Tab again to move to the second field. Continue adding the data for each of the fields – making sure you select the correct data type from the dropdown list.

When you have entered the basic field descriptions for the database your screen should look like this.

Add Captions to Fields
The shortened names we've used for the fields will make some tasks easier to perform later on but they won't look very attractive if they appear in reports. We can solve this problem at this stage by adding Captions for our fields.

To do this, click in the FirstName field to select it and, in the General tab at the foot of the dialog locate the Caption area. In there type 'First Name' which is a clearer indication of what the field contains. Repeat this and add Captions for the other fields: "Last Name", "Current Grade", "Fees Paid?", "Years a member" and "Date of Birth".

Set the Primary Key
To set the Primary key for the database, click in the margin opposite the FirstName field to select it and hold the Shift key as you select the LastName field. Now right click and choose Primary Key from the menu. You'll see a key appear in the column to the left of both fields indicating the key has been set.

Now save the table by clicking the Save button on the QAT (Quick Access Toolbar). You can then click the close button to close the table design.

Entering data

Now that the table is created, you can enter your data.

To do this, switch to the Datasheet view by double clicking the table Clubmembers in the All Access Objects list (in earlier versions of Access choose View, Datasheet View).

You should now enter the data for your table one item per cell. Use the Tab, Enter or Right Arrow key to move to the next field in the row, type your dates in the format 12/31/1999 (using slashes) or pick it using the date picker. To enter the data in the Yes/No field, press the Spacebar for Yes and leave the checkbox unchecked for No.

Enter data into your new table by typing it and pressing Tab or Enter to move to the next cell.

When you've entered the data you can close the table by clicking the Close button in its top right corner.

Sort your data

You can open the Clubmembers table at anytime by double clicking it in the All Access Objects panel.

You can sort the data using the buttons in the Sort & Filter area of the Ribbon. First choose the column to sort on - for example, the Last Name column - by clicking the column heading. Click the Sort Ascending button and the data will be sorted in last name order. You can do this at anytime for any field in the data table.

Find Data
You can also use the Find button on the toolbar to locate a person's record in the table. To find everyone with the last name Southdown, for example, select the Last Name column and click the Find button (it shows as a pair of binoculars). In the Find what text area, type Southdown and, from the Match dropdown list, choose Whole field and click Find Next. You'll be taken to the first matching record. Click Find Next to move to the next match. If you click again you'll be told there are no more matches.

Filter Data
To filter the data to display only a small subset of the information it contains use the Filter tool. To see this at work, click the Grade field and click in the entry for one of the players who plays A grade. If you click the Filter by Selection button on the toolbar (it's a funnel with a lightening bolt) and click Equals "A" - you'll see only those records for members who play A grade.

To return to viewing all the data in the table click the Remove Filter button on the toolbar (it is the funnel button and it is currently 'pressed in'). At this stage you're only working with a small amount of data so you could probably have located this information by just looking at it, however, when you are working with a lot of data, tools like these are invaluable.

Printing the data

To print your data you will create an Access Report. You can set up a report so it contains all the data in the table or simply a subset of it. To create a report listing the members grouped together by Grade, begin by choosing Create and click Report Wizard.

From the Tables/Queries dropdown list you'll choose Clubmembers so that the data for the report will be sourced from the table. You'll use all the fields in the report so click the double chevron (>>) to move all the fields from the left window to the right and click Next.

Now choose the field that the data will be grouped by, this is the Grade field so select it and click the right pointing arrow to move it to the window on the right.

Click Next and choose the fields LastName and then FirstName as the sort fields from the dropdown lists. This will ensure that the members will appear in alphabetical order within the grade groups – they will be sorted firstly in last name order and, if two players have the same last name, they'll be sorted in first name order.

Click the Summary Options button and from the YearsAMember row enable the Avg, Min and Max checkboxes, click the Detail and Summary option button and click Ok then Next.

From the layout options choose Outline, Landscape and enable the "Adjust the field width so all fields fit on a page" checkbox and click Next.

Give the report the name "Club members grouped by Grade (all details)", choose the "Preview the report" option and click Finish.

You can print this report or just view it on the screen. The report's design is saved so you can create a report using these settings anytime by double clicking the report in the Reports area of the panel on the left of the screen.

So, there's the Access 101 quick tour. Of course there's a lot more to Access than what is covered here, but you're off to a good start and you can now go ahead and confidently create your own database.

These posts take you the next steps with your Access database:

Query your Access data - Learn how to write queries to find information in your Access data base

Create an Access form- Learn to create a form to make it easeir to enter data into your database

Relational databases - Learn to create a relational database in Access

(c) 2019, Helen Bradley, All Rights Reserved.