Spreadsheets are a great way to get started with CAR. But what happens when that dataset gets a little too big, or your analysis too complex? That’s when it’s time to move to a database manager like Microsoft Access. The following notes look at basics of working with databases, including simple queries, filtering and sorting.
In the olden days, Access had separate windows for tables and queries. Today, they’re in one index on the left. To look at a table double-click on it from the index.
To create a new query (remember, you can only ask questions in a query). Click ‘create’ tab, then click ‘query design’.
Access will prompt you for the table that you want to query.
To run your query, click this button
Click this button to go back to your query design.
Queries
Every query consists of up to four steps:
1. Select the fields you want to show. You do this by double-clicking on the item from the table list in the top left corner of the query window.
Access will put the field name on the field line of the query grid – it also checks the ‘show’ box so you’ll see it in the answer.
2. Sort the data. On the second line of the query grid is the sort box. If you click on it, it gives you the sort options.
3. Filter the data. If you only want part of your database, you do this with ‘criteria’.
You have many criteria options using operators such as greater than > less than < and the wildcard *
>20 in the SAGE field = all shooters older than 20
<19 in the VAGE field = all victims younger than 19
*deer* in the cause field = all causes that contain the word “deer”
*deer = all causes that end with “deer”
deer*= all causes that begin with “deer”
4. Summarise your data. If you don’t just want to see individual records,
click this button
Access will add a ‘total’ line to your query and add the term ‘group by’. This simply means you’re making groups of certain types of records. Below we’ve made groups of counties.
Once you have your groups, you can go back at them and count, sum, average. Here’s what you’d do if you wanted to count the number of counties in this database:
Note: This only counts non-blanks – if you want to include blanks in your list use ‘count’ (*) and change your total line to ‘expression’.
Here’s what you’d do to average the ages by county:
Note: When you want to filter data that is in a summary query, use ‘where’ on the ‘total’ line:
Joining tables
When you need to join two or more tables, they must have some field in common (it doesn’t have to have the same name, just the same data). Click and hold on one of the field names and drop it on the other – Access gives you this great line to illustrate the join:
Putting the results in a table
Most queries are called ‘select queries’ – that means you just see the answer on the screen, but it’s not saved anywhere. To take the result of a query and put it in a table- before you run the query – click ‘query’, ‘make table’ – you won’t see the answer. Access will tell you that it copied so many records to the table.
Importing text files
To import a text file, click ‘file/import’ – access will prompt you for the type of file you want to import. Most text files are either ‘fixed’ or ‘delimited’. Access will then ask you for the file name.
If it’s a fixed file, you will also have to create a ‘spec’ file – basically the record layout for the database. If you don’t have that information, you can’t use the data. Once, you’ve typed in the spec, save the spec file – then Access will do the import.