- Computers & Software
How to Keep Records With Microsoft Access
When you have a lot of information to maintain, a Microsoft Access file can be very helpful. Microsoft has easy to use templates that can allow anyone to use the database.
Benefits of Using Microsoft Access
There are many benefits to using Microsoft Access. Some of them are:
1. Flexibility - You can create a database from scratch or use a template provided by Microsoft, whatever suits your needs.
2. Access can hold thousands of records.
3. You can use Access to mail merges.
4. You can important your Outlook contacts into Access.
5. You can put your data into reports.
6. You can run queries to find information that you need within Access.
7. Access can handle complex abouts of data and produce reports based on that data pulled from multiple tables.
Access Database Terms & Functions
For beginners, there are four basis parts to an Access Database. They are Tables, Queries, Forms, and Reports. There functions are as follows:
Tables - Tables store data. They are made up of fields that each store a different type of data. For instance, you might have a table with four fields: First Name, Last Name, Address, Phone Number. Each type of data is a field. For instance, First Name is one field. Fields are also referred to as columns. If you decide to keep an address database with all of your clients, each clients data would be considered a record. That means, that the fields that contain their First Name, Last Name, Address, and Phone Number together in a row is their record.
Queries - Queries organize data. A Query is used to display data is different ways. You can sort data or apply criteria so you only see specific data. For instance, I may only want to see my customers that live in the 63105 zip code. If so, I would apply that criteria to a query so I can just look at my customers that live in the 63105 area.
Forms - Forms display data on screen. Forms save time by displaying information however you want. It allows you to display only the area's of the database that you want your employees to enter data in.
Reports - Reports can be created and presented to someone who is not using the database.
What Do You Want Your Database To Do?
Make a list of what you want your database to do and what types of tables you are going to need. Keep all of one kind of information in one table. Customer information should be in one table. Employees should be in one table. Products should be in another table.
Once you have selected the types of tables you want, then you need to choose what fields you want on each table. For instance, on the customer table you may want their First Name, Last Name, Address, City, State, Zip Code, Phone Number, and Birth Date.The more you break down the information, the easier it will be to sort the data later. For instance, notice that I referenced First Name and Last Name separately. That is because when you want to search, it will be easier to search with smaller amounts of data. For instance, if I search by Smith, it will be easier if the Smith's first names are in a different field.
Starting in Access
When you first open up Microsoft Access. You will see the page below. It allows you to select a blank database that you will format yourself or you can choose from one of the available templates provided by Microsoft.
To create your own database in your preferred format, select Blank Database. In the bottom right hand corner under File Name, choose the name for your database and save to the location you prefer on your computer. On the example below, I have put a red circle around the area where you name your database and select the location you will save the database to. Once you have choosen the name and location, select the create button immediately below the file name and path.
Before you get started creating your database, you need to know what some of the basic buttons that are in the Access Ribbon. I have highlighted the Access Ribbon on the below picture. The Ribbon groups types of commands together so that they are easier to find. For instance, on the home tab, cut, copy, and paste are all next to each other in the clip board section. The Ribbon will change based on what you are doing. If you need to minimize the ribbon, you can do so by double clicking on one of the ribbon tabs.To get the ribbon back viewable again, simply double click on the tab again.
The Navigation Pane
The Navigation Pane is a list of all your Access Objects on the middle left hand side of the screen. This is all of your forms, reports, queries, etc.
The Object Pane
The Object Pane is the main screen. This is the area where you will see see the detail of your tables, queries, reports, etc.
Creating a Table
Click create, and then table design. In Table Design View we will create the structure of the table before we actually put our data in.
You then need to put the first field name in. The field name should be all one word. This will help you later when you are creating a more detailed Access database.For instance, in my client database, I am going to start with first names. These should be seperated from the last names for better searching capabilities. When preparing the field instead of calling it First Name, it should be all one word: FirstName. Once you have created that Field Name, tab over to Data Type. You need to select what type of data you want in the field. Do you want it to be a text field, numbers field, date/time field, currency field, a Memo field (a very long text field), etc. In most cases, you will be using text fields. For my first example, of the first name, I will be using a text field because we will only be typing in a short amount of text. Once you select the data type, you can tab over to the description field. I don't normally use this field but you can put a description of the line in that field. For instance, I could say, this is the client's first name. Another Field Name I would encourage you to consider is An Auto Number Field. You can call it anything you want. I am calling mine ClientNumber. This will give you a way to identify each record or each client. In the Data Type select Auto Number. The Auto Number will always tie to that number. The Auto Numbers cannot be changed or ever reused. As you can see from the picture below, I have put all of my Client Data Structure in.
Once you have created your table, select Save As. Create a name for your table and choose a file location for your table. When you go to save you may get a message that says: "There is no primary key defined. Although a primary key isn't required, it's highly recommended." A primary key shows Access that each record is different. The Auto Number Field that I discussed above will meet that need. We need to tell Access that the Auto Number Field is the primary key. To make the Auto Number Field the primary key, just select that line, and then click on the big primary key button on the top left side of Access. There will then be a key symbol by the Auto Number line.
Enter Data into Table
To enter data into your table, you need to double click on your table on the left hand side, object pane. It will then bring up the table. The table will be blank except for the field you previously created. They will be listed at the top of the table. For instance, you can see my table below just has the Client Fields I selected called: ClientNumber, FirstName, LastName, StreetAddress, City, State, Zip, PhoneNumber, and DateofLastVisit.
Start typing in your record into the first available row. Tab over each time you have filled in a column. If you realize you need to add a field, just lick on the view button in the top left corner. As you can see, I added all my sample data into the table below.
How to Sort & Filter Data
As your table gets larger, it becomes harder to manage. Sorting & Filtering data becomes more necessary as your table grows. While in the table, you can sort your data by using the drop down arrow next to the column name. For instance, you can sort by Last Name. You can choose to sort Last Name from A to Z or backwards from Z to A. You can do that with any of the columns. If you want to undo a sort, you can click on the Remove Sort button on the Home Tab in the Sort and Filter Section of the Ribbon.
You can also filter by using the drop down arrow next to the column name. When you select the arrow, it will not only give you the option to sort but also to filter. On the bottom of the screen that comes up it will show you the data you have broken down. For instance, if I working with the zip code column, it will show me a list of all zip codes in my table with checked boxes by them. I can un-check anything I do not want to see. So, if I only choose to see clients in the 22212 zip code, then I could un-check all the other boxes and select OK so that I will only see the clients in the 22212 zip code.Reversely, I could click the Select All box and it would un-check everything. I could then, just click on the 22212 zip code to see those clients. Depending on how many zip codes you are working with, this might be the faster way to go.
How to Query
If you have a sort or filter that you are going to need on multiple occassions, you can set up a Query. This is an easy way to click one button and get the results that you need with up to the second data. To create a Query, click on the Create Tab. In the Queries Section of the Create Tab there are two options: Query Wizard and Query Design. Query Wizard helps you make advanced queries. For simple queries, you can use Query Design. I am going to do a simple query below so let's select Query Design. The below picture shows where the Query Design button is located on the ribbon.
The Show Table window will then pop up. You can select which table you want to choose from. Since I only have one table right now, I will have to pull data from that table. Select the table and click add.
Once you have selected your table, you will notice a new table will pop up on the left side of your screen with all your fields listed. You will be working with the fields listed in this box. A picture is below.
Click and drag any fields from the above box to the columns. For instance, if I wanted First Name, Last Name, and Phone Number, I would click and drag those fields to the field line. See picture below for example.
Now that we have the columns we want in the query, we need to select the Run button on the top left section of the ribbon in order to run the report. The Run button is pictured below.
Once I run my query with the First Name, Last Name, and Phone Number, it looks like the picture below.
Making Changes to Your Query
If you want to make changes to your query you just need to select Design View to get back to the Query Screen. A picture of that button is below.
At this point you can add any additional fields you want and re-run your query using the run button.
If you want to to sort your query in ascending or descending order click on the sort line. Then, a drop down box will give you the two options and you can choose how you would like to see the information. An example is below.
Once you have choosen how to sort, you can again run the query using the Run button on the ribbon.
Filtering A Query
If I want to filter a query to include less information, I can. Say, I only want to see clients with a 314 area code, I can narrow down my client list through a filter. I go back to the design view of my query. In the Criteria line, I add 314*. This means that I want everything that has a 314 in it. The * denotes that there will be additional numbers following it but I don't know what they are.This is a wild card option. Once I select 314* in the Phone Number section of the criteria as shown below, I will run my query and get only the phone numbers with a 314 area code.
To save your query, click on the save disk icon in the top left corner, above the ribbon. A Save As box will appear. Choose the name of your Query and click ok. If I want to bring the Query back up in the future I just need to click on that query in the navigation pane on the left hand side of the screen.
This is the very basic information on using Access for beginners. There is a lot of additional information to learn on using Access and a lot more that you can do with Access.