Learn Microsoft Access 2010

Access 2010 is a "Relational Database" that helps you store information for reference, reporting, and analysis. A relational database is a place where you store information that relates from one aspect of the database to the other. You divide your information into separate, subject-based tables and use table relationships to bring the information together.

Microsoft Access can help you overcome the limitations found when trying to manage large amounts of information in Microsoft Excel or other spreadsheet applications. A training company schedule, with the classes, instructors and locations, is an example of a relational database.

This clip will introduce you to Microsoft Access 2010, and show you how to open and exit the application. Access 2010 can be opened by clicking on the Start Button, choosing “All Programs” and locating Microsoft Office folder and then clicking on your Access program. To exit Access 2010, click on the red “X” in the upper right hand corner of the application, or go under the File Tab and choose Exit.

This video will introduce you to the Ribbon, Quick Access Toolbar, and the Status Bar. The ribbon contains commands are organized into tabs that group related commands together. The Quick Access toolbar contains icons of the commands used frequently. The Status Bar contains indicators and view buttons. All of these items can be customized as desired.

Backstage View, or the File tab, is where commands are located to create a New file, Open and existing file, Save changes to a file and Close a file. There is information about the current file as well as options to change how the program functions. You can also access Help from the File tab.

The Navigation Pane is where all the objects of the database; tables, queries, form, reports, etc. are located. The way the objects are displayed can be customized. The pane can be minimized to give you more room to work with the opened objects on the screen.

Tables are the foundation of a database and the first object that gets created. All the data in the database is stored in tables. Tables are made up of fields or columns of information that stores records or rows of information. When designing tables, consider all the field values needed and how to relate the tables to each other.

Queries are used to filter your data, to perform calculations with your data, or to summarize your data. You create the query structure to perform the type of query you need. The data that is displayed in the query results is the underlying data in the table so any changes made to the data is a query is also made to the data in the table.

Forms are a way to display data from an underlying table or query. Forms are used to enter data as well as find data and update it. A form can also be used to display labels, button, or controls to perform task with macros.

Reports are used to display the output of an underlying table or query in a way that can be viewed, printed or exported. Reports can contain titles, logos, labels, group and totals based on the requirements needed for the output. A report is just a structure that pulls the current information when it is run.

Instead of starting from a blank new database, Microsoft provides many templates for specific databases such as inventory control, client contacts and student database with the objects already created for you to use. You can use a template to get started, and then modify the objects as needed. Once the design is modified, if desired, you can start entering your data.

If you know the way you would like to track your information, start with a blank database. In a blank database you build the objects you need, when you need them. You start with your tables, then create your relationships, and build queries, forms and reports as needed.

In the clip you will explore the new features of Microsoft Access 2010 including new templates, exporting data to .pdf files, applying conditional formatting to fields, and working with office themes. You will also see the new layout view, backstage view, the click to add fields in datasheet view and some other new features. Check this clip out to learn what’s new in Access 2010.

When creating an Access database, there are normalization rules and guidelines to help with the design of the database. These guidelines include items such as identifying the purpose of the database, what fields of information do you need to track, how can the fields be divided into tables, what field is an unique identifier for each table and setting up table relationships. A good database design is the key to making the database as efficient as possible

One way to create a table is in Datasheet View. In this view, you can add a field to the table by entering data in the “add new field” column of the datasheet. Based on the information you type in the field, Access will determine the field type for that field. When you create a new table in Datasheet view, Access automatically creates a primary key for you and assigns it a field name of ID and the AutoNumber data type. The field types can be changed if needed, in Table Design View.

A table can be created in Table Design View by typing the field name, field type and a description for the field. The description is optional, but useful since it appears on the status bar during data entry. Once all of the fields are entered, then the field values will be typed in Datasheet View.

In an Access table, you can specify a primary key for a table. A primary key make sure that every record has a value and that each value in the key field is unique. Access automatically creates an index for the primary key, which can help improve database performance. The primary key field can be one field or a combination of fields. The field or combination of fields is never empty or null and the values do not change.

Once a table has been created in Datasheet or Design View, information can be added to the table. Each column is a field and each row is a record. The information added in each cell is called a value. The records are added to the tables first so that queries, forms and reports can be created using the table field information.

The table structure can be modified at any time to add additional field, change existing field or deleted unwanted fields. Regardless of whether a table was created in Datasheet or Design View, it can be modified in either view as needed. It is recommended that you add minimal data to the tables until the table structure is modified as much as needed to prevent loss of data.

After you create a field, you can set field properties to control its appearance and behavior. The properties available depend on the field type. By setting field properties, you can control the appearance of data in a field, prevent incorrect data entry in a field, and specify default values for a field as well as additional options. You can set some of the available field properties while you work in Datasheet View, but to have access to the complete list of field properties, you must use Design View.

Once a table is created, the way the fields are added is the way they are originally listed in the table from left to right. This can be changed by modifying the table layout. The fields can be moved, frozen, increased or decreased in width or height or hidden from the display. Once the changes are made, if the layout is saved, the table will be displayed in that view each time it is opened.

Find and Replace is used to find data and update the data, if needed, in a table through a dialog box. You specify a value that you want to find and then you can specify the value to use as the replacement. As each occurrence of the value is displayed, you can choose whether to replace that specific value or continue on to the next one. Using the Find and Replace dialog box is easier method than manually looking for the text to find and optionally replace it in a table.

The data is a table is usually displayed in order by the primary key field. There are often times when you need the data displayed in a different way. You can sort the data by any field or by multiple fields that are displayed next to each other from left to right. The sort order can be changed at any time as desired.

Sometimes you only need to see some of the data in a table. Filtering allows you to select the subset of data needed to be displayed for each field. The field drop-down list in Datasheet View makes it easy to select the desired information to be filtered.

A lookup field allows you to select a value from a list of choices instead of typing it. This keeps data entry consistent if there are limited choices. These choices can be entered when the field is created or values stored in a field in another table. The value selected can be limited to the choices in the list, or if desired, a different value can be typed in the field.

In Access, the data is divided into tables that can be related to each other by a common field in each table. Table relationships join the tables together so data can be pulled for either table. A permanent relationship is created once in the Relationship window. If multiple tables are to be used in a Query, the tables must be joined together before the fields can be used in the query. Access will join the tables together in the Query automatically if a common field is available.

Once tables have been joined together in a permanent relationship, the data from a related table can be displayed with the information in the current table. A plus symbol appears to the left of the record and that symbol can be selected to open the related record in another table. The plus symbol turns to a minus symbol and that symbol can be selected to close the related record.

One way to create a Query to pull data out of a table or multiple tables is to use the Simple Query Wizard. The Simple Query Wizard walks you through a series of steps to place the fields needed in the query design. Once the wizard is complete, the query can be run to display the data. If needed, the query can still be modified in Query Design View.

A query can be created in Query Design view by selecting the table or tables containing the fields needed for the results. Once the tables have been select, fields can be added to the design manually as desired. In this view you have more control on how fields are organized in the output.

Once a query has been created, fields can be added, deleted or rearranged to make the output more effective. A field can be dragged from a table in the query workspace and placed on the query design grid in any position. If a field exists in the query design grid, it can be removed by selecting it and deleting it. If the fields need to be rearranged, just drag them in the query design grid to the desired location.

When a query is run, usually the records are displayed in order by the key field or fist field. If the results need to be in a different order, then the fields can be sorted by selecting the desired field in the sort row of the query design grid. If there are multiple fields being sorted, Access reads the sort order from left to right in the grid.

A query is often used to pull information out based on a criteria or specific requirements. Criteria, such as a date range between 5/1/12 and 5/7/12, is added to the query design grid in the criteria row. Any criteria entered in the criteria row must be true for the record to be displayed. If criteria is also entered on the or row, than one of the two conditions must be true for a record to be displayed.

Once a query has been created, it can be saved and reused at another time. It can also be copied and saved as another query name since the query may be similar to another one that needs to be created. Once saved as a new name, the query can be changed to the new specifications.

Sometimes you need to see how many days classes are running in a month or how much income is being generated in a given month. The Show Totals feature will allow you to view totals on columns of information displayed in queries. The totals will change if the conditions in the query are modified.

In some cases, calculated values can become out-of-date because the values that they are based on change. But if a field is added to a query as a calculated field, the results will be modified each time the query is run. The calculated field often references a field or multiple fields from a table, such as price multiplied by quantity, in the calculation. Each time the query is run, it pulls the underlying field values and uses them in that calculation so the information is always up to date.

Sometimes you need statistical calculations on a group of records. For example, how many days are being taught in each of our locations? The query can group the location field and count the number of days in each location. The field to be calculated on can be added multiple times to count, sum or average, for example, on the same group.

Sometimes you cannot get the desired information out of a single query. You may need to create a query than query that query to get the results needed. An example may be that you need to create a calculated field to get totals and then you want to perform statistical information on those totals. The first query creates the calculated field, and the second query performs the statistical calculations on that query.

If a form for data entry or editing of records needs to be created, an easy way to get all the fields in the underlying table or query is to use the Form tool. This tool creates a form quickly with all the fields placed evenly in columns and rows. Once the form has been created, changes can be made to it in Layout or Design view.

When specific fields from a table or query are needed to create a form, the Form Wizard is a useful tool to create that form. The wizard walks you through a series of steps to select the desired fields to be placed on the form. Once created, the form can be modified in Layout or Design view

If fields from multiple tables or queries are needed on a form, then create the form in Form Design. If the selected fields from multiple tables are not already related, Access will create a query to relate them together so the form can be created. Using Form Design gives you more control on the placement of fields and controls.

Forms are often used to add records to an underlying table or tables. It is easier to add records to a form that displays one record at a time instead of adding data in datasheet view especially if the fields are from multiple tables. Once the records are entered in the form, the data is automatically added to the fields in the tables.

If records need to be modified and the data is from more than one table, it is easier to edit the data in a form. Once the form is created, the find and replace dialog box can be used on a field to find the data and replace it was something else. The find dialog box can also be used just to locate records even if replacements are not needed. Once the data is modified, it is updated in the underlying tables.

If the design of a form needs to be changed, the form structure can be modified. Fields can be added, moved, deleted or arranged as desired. Once the form is modified, save the changes so the form structure remains the way you changed it each time you open the form.

To make a form look different or to have certain fields stand out, the form can be formatted. Formatting changes such as font type, font size, bold, italic, underline and colors can be applied. Once these changes have been made, save the form so the form structure remains the way you formatted it.

When output is desired for all the fields in a table or query, use the Report tool to create the report. The tool places all of the fields neatly on the report so you can quickly print it as needed. Changes can be made to the report structure in Layout or Design view.

When specific fields from a table or query are needed to create a report, the Report Wizard is a useful tool to create that report. The wizard walks you through a series of steps to select the desired fields to be placed on the report. Once created, the report can be modified in Layout or Design view.

If fields from multiple tables or queries are needed on a report, then create the report in Report Design. If the selected fields from multiple tables are not already related, Access will create a query to relate them together so the report can be created. Using Report Design gives you more control on the placement of fields and controls.

If the design of a report needs to be changed, the report structure can be modified. Fields can be added, moved, deleted or arranged as desired. Once the report is modified, save the changes so the report structure remains the way you changed it each time you run the report.

If the underlying table or query does not contain the calculation you need, a calculated control can be added to the report. This control can calculate existing fields, such as price multiplied by quantity. When the report is run, the data is pulled for the underlying table or query to calculate the results at that given time. This way the calculation is always up to date.

If a report needs to be grouped by a field, such as location or class, and displayed in a particular order then grouping and sorting needs to be added to the report structure. You can select the field or fields to group on then sort the remaining fields in the desired sort order. This will create group headers and footers that can be used to place labels or controls to display additional information about the group.

Totals can be added to a report to calculate the results of a field or groups of fields, such as the total number of days taught in a location. Once the group headers and footers have been added, totals can be applied to these areas. The report header or footer is often used to display grand or final totals.

The fields or controls in a report may need to be aligned after new ones have been added or others have been moved around. The alignment tools help to align the controls by top, bottom, left or right as well as by sized to widest, tallest and so on. These tools help to make sure the report’s design is straight and aligned neatly when printed.

To make a report look different or to have certain fields stand out, the report controls can be formatted. Formatting changes such as font type, font size, bold, italic, underline and colors can be applied. Once these changes have been made, save the report so the report structure remains the way you formatted it when printed.

There are times when you need a particular value to be displayed differently from the rest of the values. For example, if the price is greater than $500, any price that meets that condition needs to be formatted in the color red. This is when conditional formatting would be applied to a control. The formatting changes when the criterion is met.

Themes are fonts and colors that can be applied to a report in Design view to change its appearance uniformly within the report. While in the design view the pre-created themes can be hovered over to see how they will change the report. Once a desired theme is applied, the report controls will pick up those colors and fonts. This is a quick way to format a report.

Once a report structure has been created, the report can be previewed to see how it will look printed on a piece of paper. While previewing the report, items to modify may be noticed, such as the title of the report not centered, and then fixed. When the print preview looks good, then the report can be printed.

When your Access database contains names and addresses, labels can be created to use for mailings. The label layout is a report structure that allows you to place fields on a selected label design that you choose. Once the fields are place, you preview the report and make any desired changes as needed. When the label report structure is complete, print the labels pulling the current data from the underlying table or query.

Many times list of information or databases are created in Excel. But often times, Excel does not have all the features of a relational database that are needed to pull information and generate reports. Instead of starting the database from scratch, the Excel data can be imported or copied into a table. Once in Access, it can be modified as needed.

You can create a table by linking to data that is stored elsewhere such as an Excel worksheet, a Windows SharePoint Services list, another Access database, as well as many more choices. When you link to data, you create a linked table in the current database that represents a live link to the existing information that is stored elsewhere. When you change data in a linked table, you are changing it in the source and vice versa, but you cannot change the design of a linked table.

When you export data to Excel, Access creates a copy of the selected data, and then stores the copied data in a file that can be opened in Excel. If you copy data from Access to Excel frequently, you can save the details of an export operation for future use. Data is often exported to Excel for data analysis and distribution to staff.

Many times a word document needs to be created and customized for individuals who are in the Access database. Access can be the data source for a Word Mail Merge. By selecting Word Merge form the Export group, it will open a Word document and attach the desired Access table or query to the merge. By following the screens in the Mail Merge Step by Step Wizard it will be easy to create a professional customized document.

If a table does not have a primary key field, it is possible that the table contains duplicate records. The Find Duplicates Query Wizard finds these duplicate records and places them in a table indicating how many of the same records it found. Once found, you can delete the duplicate records in table.

Sometimes there may be a record in one table that does not have a match in another table. The Find Unmatched Query Wizard will let you know what records in one table do not have a match in a related table. Once the records are found, you can manage them as needed.

A crosstab is a way to consolidate data in a table or query. It gives you the ability to place a field in a row, in a column and a value to calculate on to get the sum, average or count for example. When the query is run, the data is pulled from the underlying table or query and displays the results in concise calculated display.

A make table query retrieves data from one or more tables, and then loads the results into a new table. That new table can reside in the database that you have open, or you can create it in another database. Usually, you create make table queries when you need to copy or archive data for long term storage such as classes that have already happened. This reduces the size of the data in the current table to make it easier to manage.

If there are many records to delete in a table and they have the same criteria, you can create a Delete query to delete all the records at once. Once the query is run, the records will be automatically deleted if they meet the criteria. This can be potentially dangerous since this process cannot be undone. Therefore start with a select query to make sure you have the desired record set when you run the query and then change it to a Delete query.

You can use an update queries to add, change, or delete the data in one or more existing records. You can think of update queries as a powerful form of the Find and Replace dialog box. This can be potentially dangerous since this process cannot be undone. Therefore start with a select query to make sure you have the desired record set when you run the query and then change it to an Update query.

If there are records that need to be added from one table to another table, use an Append query to copy the records. With this query you select the records to be copied based on criteria. When the query is run, the records that match are copied into the other table. This can be potentially dangerous since this process cannot be undone. Therefore start with a select query to make sure you have the desired record set when you run the query and then change it to an Append query.

Sometimes you may want to run a query that is only slightly different from an existing query. You can change the original query to use your new criteria, but if you frequently want to run variations of a particular query, use a parameter query. When you run a parameter query, the query prompts you for field values, and then uses the values that you supply to create criteria for your query. This way you do not have to keep modifying the query each time you run it.

If you want to see only some of the records in a table based on the top or bottom values, create a Top Values Query. To create a top value query, start by creating a select query. Then, sort the data in ascending or descending order according to whether you are looking for the top or the bottom of a group. Once the select query is correct, choose the number of records or the percentage of records to display in the results.