Microsoft
®
Access
®
Microsoft
®
Access vs. Excel
Excel is used primarily for:
• Data analysis• Data manipulation
Access is used primarily for:
• Data storageYou should know several terms to properly work with Microsoft Access: 1. Data
Facts and elements, such as numbers, dates, names, and prices 2. Information
Organized data that is meaningful 3. Database
A storage location for data 4. Table
A storage container within the database for a specific type of data 5. Column/Row
A column is the vertical representative placeholder for an individual data element within a table.
A row is a collection of related data elements (columns). 6. Data Type
The category of data in a column, such as text or number 7. Query
A question or directive given to the database management system 8. Record Set/Result Set
The results of a query 9. Form
A graphical application interface that allows for simple data viewing, entry, and modification 10. Report
A graphical representation of data for viewing on screen or printing
A relational database is composed of tables containing data and, when properly presented, this data forms information.
Creating Databases
When you open Access 2013, Backstage view displays the File tab. The File tab provides several ways that you can create a new database:
• From a blank database
• From a template that is installed with Access • From a template from Office.com
Create a Database From a Template
1. If you have a database open, on the File tab, click Close. 2. Click File and the Backstage view displays the New tab.
3. Several sets of templates are available in the New tab, some of which are stored with Access on your local computer. Additional templates may be downloaded from Office.com. 4. Select the template you wish to use. The template appears in a pop-up pane above the
screen, and you should enter a file name in the File Name box.
5. A file name is suggested for your database in the File Name box — you can change this file name. To save the database in a different location than the one displayed below the file name box, click , browse to the folder in which you want to save it, then click OK. 6. Click Create. Access creates a database from the template that you chose and then opens
the database.
Tables are created to store similar data elements that typically represent a record or unique entity. The Tables group on the Create tab provides the buttons used to create tables.
Tables May Be Created in Two Views
1. Datasheet viewSimply enter data to create the columns for the table automatically.
2. Design view
Manually specify each column and the appropriate data type.
Access Supports Many Basic Data Types
Format
Use to Display
Text Short, alphanumeric values, such as a last name or a street address
Number Numeric values, such as distances (Note that there is a separate data type for currency.)
Currency Monetary values
Yes/No Yes and No values and fields that contain only one of two values
Date/Time Date and Time values for the years 100 through 9999
Rich Text Text or combinations of text and numbers that can be formatted using color and font controls
Calculated Field Results of a calculation; the calculation must refer to other fields in the same table. You would use the Expression Builder to create the calculation.
Attachment Attached images, spreadsheet files, documents, charts, and other types of supported files to the records in your database, similar to attaching files to email messages
Hyperlink Text or combinations of text and numbers stored as text and used as a hyperlink address
Memo Long blocks of text; a typical use of a Memo field would be a detailed product description.
Number Data Types
Data and Time Data Types
AutoNumber is a special number data type. Access will automatically enter the values for the AutoNumber column and increment each new record by 1, or it will allow for random but unique values to be entered automatically.
Format
Use to Display
Short Date Display the date in a short format. This depends on your regional date and time settings. For example, 3/14/2001 for U.S.
Medium Date Display the date in medium format. For example, 3-Apr-09 for U.S. Long Date Display the date in a long format. This depends on your regional date and
time settings. For example, Wednesday, March 14, 2001, for U.S.
Time a.m./p.m. Display the time only using a 12-hour format that will respond to changes in the regional date and time settings.
Medium Time Display the time followed by AM/PM.
Time 24-hour Display the time only using a 24-hour format that will respond to changes in the regional date and time settings.
Format
Use to Display
General Numbers without additional formatting exactly as it is stored Currency General monetary values
Euro General monetary values stored in the EU format Fixed Numeric data
Standard Numeric data with decimal Percentage Percentages
Relational Tables
Access includes a wizard that can be used to quickly and easily create forms. 1. On the Create tab in the Forms group, click Form Wizard.
2. Follow the directions on the pages of the Form Wizard to create the form. 3. On the final page of the wizard, click Finish.
A single item form, such as the following Customers form, can be quickly created with the form tool.
Using the Query Builder
The Query Builder is the graphical interface provided to create queries within Access databases.
Query filters or criteria allow you to limit the returned data to only that information desired. To return a single unique record, use the unique record ID column to filter the query. To return all records matching a specific criteria, enter it in the appropriate column as shown in the following image:
You can filter on a column that is not displayed in the query results as shown in the following image:
Query-Based Forms
After creating a query, you can generate a form that displays the query results as if the query results were a static table in the database.
To create a query-based form:
1. Click on the query for which you wish to create a form. 2. Select the Create tab.
3. Click the Form button.
Creating Reports
Reports may be generated against tables or queries. To create a report with the Report Wizard: 1. Click on the table or query on which you wish to create the report.
2. Click on the Create tab.
3. Click the Report Wizard button in the Reports group. 4. On the first page, select the fields you want
to include in the report and then click Next.
5. Choose any grouping options you desire and then click Next. 6. Choose any sorting options you desire and then click Next. 7. Choose the report layout and then click Next.
Customizing Reports
After generating a report with the Report Wizard, you can customize it using Design view. To customize a report:
1. Open the report by double-clicking it in the All Access Objects pane. 2. Select the Home tab if it is not already selected.