A Reporter’s Guide to Microsoft Access
For education journalists with basic and intermediate Access skills
Published May 2016
What is Microsoft Access?
Microsoft Access is a great database manager. It allows you to use queries to pull specific information from a database. For instance, if you have a database with a million rows of information and 30 columns, you can specify what information in that data you want to see by using a query – it’s like creating a mini-database. It’s different than doing filters in Excel, because filtered information is still there but you just can’t see it. Access also allows you to join two files with ease.
Accompanying this guide is a set of worksheets that you can use to practice the skills you’re about to learn. The examples in this guide mirror the examples in the spreadsheets.
Importing your data:
There are two different ways to bring your Excel spreadsheet into Access. One is to create a “linked table” and one is to create an “imported table.” A linked table, like the name suggests, is connected to the Excel file. If you make changes to that, they will show up on your Access file. You cannot make any changes to the data in Access, however, such as renaming a field or changing a data type.
An imported table, on the other hand, simply makes a separate copy of the table in Access into Excel. You can edit it in Access, but changes to the Excel file will not be reflected in Access.
To create a linked table:
Save the Excel spreadsheet you want to use. Open Access. Click on the Office Button in the upper left corner. Click on Open and find the Excel spreadsheet that you saved. (If you don’t see your Excel file, check to make sure you’re looking for “All files” or “Excel Workbook” in the dropdown menu in the bottom right.) Double-click on it.
Select the sheet with your information and click Next. Make sure the button for “First Row Contains Column Headings” is checked. Click Next. Enter the name of your database in the “Linked Table Name.” Click Finish. Your table will appear in the left-hand column.
To create an imported table:
Click on the External Data tab and click on Excel in the import section. Find the second file that you want to import and click OK. Like before, select the sheet with your information and make sure the “First Row Contains Column Headings” is checked.
You’ll have a chance to review the data types (more on that later) and to skip any columns you don’t want imported on the next window. The final window asks if you want Access to add a “Primary Key,” or an individual identifier for each row of data. Select “No primary key” and click next. Name your table and click finish. Once more, it should appear in the left hand-column.
If there are any errors in importing, Access will alert you and add a second table to the left-hand column that tells you what the import error was, what field it was in and what row. A common culprit for this is if a state uses a dash or other symbol for any schools it does not have data for. You can clean those out in Excel to make sure you’re importing your data error-free.
Before you begin working with your data, you need to know a little bit about data types. Each field in Access will be one of 10 data types, such as number, text and date. For the most part, your data should already be coming in as the type you want it to be and you will know what type you want it to be. The name of a school should be “text,” for instance, and the total enrollment should be “number.”
As mentioned above, you can check and change data types when importing a table, or you can change them after importing by clicking on the “View” button under the Home tab and selecting “Design View.” This will give you a list of your field names and the data type for each, which you can change if you need to.
Creating a Query:
A query is what you use to analyze your data by breaking off smaller pieces of the table that meet certain specifications or only looking at certain fields. Sometimes it helps to think of the query very literally. What questions do you want to ask the database?
Under the Create tab, go to “Query Design.” The “Show Table” box will pop up. Add the table that you just created. A box with your column headers will be created in the “query field.” It can be adjusted by dragging on the sides or at the corners. The “grid” is at the bottom half of the screen.
Example Figure1 shows an Excel database on hunting incidents that was imported into Access. We’ll call it the Deer database. Note that the Query 1 tab is open and the table is waiting in the query field.
Here are the fields in the Grid:
- Field – Shows the column of information selected.
- Table – Shows the database that the information is being drawn from. This comes in handy when you join multiple databases.
- Sort – Allows you to determine whether to sort in ascending or descending order.
- Show – Check this box if you want the information to appear in the database your query makes.
- Criteria – Is where you set criteria for your query. For example, if you want to see all deer incidents that occurred in Tarrant County, you would put Tarrant in the criteria cell.
Now we’re going to decide which of the columns from the Deer database we want to pull into our query. In order to select a column (e.g., case, date, time, etc.) double-click on the name of the column. The selected column header will appear on the grid. You are telling Access that you want to see information in the selected columns. Alternatively, you can select the column headers by using the pull-down tab in the Field cell on the grid. Or you can simply begin typing the column header in the Field and it will pop up as an option.
In Figure2, County, Wound and Cause were selected for a query.
Let’s run the query in Figure2. Under the Design tab, click Run. Here’s what your information will look like:
Under the Home tab, click the View button to go back to the grid where you can edit the query.
Setting more specific criteria
In the above example, we chose what columns we wanted to see. To tell Access what rows of information you want to see, you use “criteria.” This is where you can pare down your database to look at only the entries that meet certain specifications.
Using AND in the criteria field allows you to specify more than one requirement in a field. For instance, you can set the criteria to see which incidents involved shooters over age 16 and younger than 27. You would type this in the criteria field: >16 and <27 (It should return 78 records)
Access is sensitive, so you have to type the criteria field exactly how it appears in the database to get the information – or you can use a wildcard to look for specific keywords. The wildcard, an *, tells Access to look for the word or words you’ve written exactly as they are, but to include any with other letters before or after the wildcard sign(*).
For example, to find how many victims were mistaken for game, use: *mistaken* *game* in the criteria field (See Figure3.) If you suspect a cell may have “gam” instead of game, you can also search for *gam*.
Under the Design tab, click Run. Two records fit the criteria. Here are the results:
Click View on the home tab to get back to the grid.
Using OR in the criteria allows you to set more than one criteria. In Figure4, you could specify that you want to see all wounds to the arm or head by typing these words in the criteria field: arm or head (Access will automatically enter quotes around the words.)
The OR connector can also be used with numbers. To find shooters age 16 and 17, you would put this in the criteria field: 16 or 17
Use Not to exclude criteria. To exclude knee wounds from a query, you would type this in the criteria cell: Not knee
Less Than/Greater Than:
Use the symbols < > to indicate less than and greater than. To see incidents involved shooters over age 15 and under 21, you would type >15 and <21 in the criteria field.
Less Than/Greater Than can also be used for dates. To find all hunting incidents that occurred after Dec. 1, 1994, the criteria would be >12/1/94. To find incidents that occurred between Dec. 1, 1994 and Jan. 31, 1997, you would put this in the criteria: >12/1/94 and <1/31/94
This is a case where data types matter: this won’t work if your dates or numbers have been imported as text, for instance.
The Totals button allows you to do math equations and counts in Access. Essentially, it performs the same function that a Pivot Table does in Excel. Example Figure5 shows you how to count the number of incidents per county in the Deer database. Here are the steps:
Make the Field name County.
Under the Design table, click the Totals button. You should see a line for Totals in the grid. “Group By” is the default in the Total field. This tells Access to group all the like counties.
Under the design tab, click Run. You should get the list of all counties represented in the database – no duplicates as you grouped all like counties.
Now, we need to count the counties. Click on the View tab to go back to the grid. In the second column, make the Field name County. Using the pull-down tab, change Group By to Count in the Total field. You are telling Access to count the grouped counties.
Under the Design tab, click Run. See the end result in Figure6.
The Group By button can also be used for math problems, such as averaging, finding the sum and maximum and minimum numbers. With that said, it is much easier to do math problems in Excel.
The great thing about Access is the ease in which you can join two or more files. When linking two files, they both must have a common field, such as both having an employee ID field. The data types for the fields should be the same. If employee IDs were text in one file and numbers in another, they will not match. The values must also be absolutely identical. Steve Smith and Steve A. Smith will not match when joining, for instance.
Go to Page 14 for the importing instructions. You will need to import both files.
Now it’s time to link the files! In Figure7, we will link a file with employee names and a file with salary information. The common field we will link on is ‘name’. To link, hold the left click on the mouse and drag from ‘name’ in the Employee file to ‘name’ in the Salary file. You should get a dark line linking the two. If you make a mistake, right click on the line and hit delete.
Now do a query using information from both files. The connection was successful if you don’t get an error message when running the query.
Right and left joins:
This join will only show information from each dataset on IDs where there is a match. Say, for instance, that you knew the school district was missing some salary information, however. This join will only include employees who have salary data. Still, you want the full list of employees so you can see which ones have salary information and which do not.
A solution to this is to right-click the black line connecting the data sets. You will then be given the option to have the query produce all items in Data Set A and any items in Data Set B that are able to be linked to items in Data Set A.
Joining on multiple items:
Sometimes, you need to join on more than one field. If you have a master database of test scores that includes several grades and subjects, joining on just a school ID or code won’t work. Figure8 and Figure9 show what happens if you try. In Figure8, we link two sets of test score data to try to compare across years only on the school code.
And here’s the result:
Because each school code is used in multiple rows in each data set, each one of those is matched with each one in the other. In Figure9, the reading scores for white third-graders matched with the scores for each grade, subject and subgroup, when we really only wanted them to match to reading scores for white third-graders.
To solve this problem, we need to match on all those items as well, as shown in Figure10.
The end result, in Figure11, is what we’re looking for.
At the bottom of your screen, Access gives you a total count of how many records are in your database. Always check this before and after joins. If you only have 500 schools in your two databases and suddenly have 2,000 records after your join, something is probably wrong.
You can export queries into Excel. First you must save the most recent version of your query. Under the External Data tab, click Excel in the export section to begin the process.