Office Button
Access is the database management system in Microsoft Office. A database is an organized collection of facts about a particular subject. Examples of databases are an address book, a library’s card catalog, and CIMS student management system. It lets you organize the facts and provides a way for you to maintain the data electronically.
A database management system provides functions to store, search, filter, query, and report on the data in a database. Access data is stored in a table. Therefore it is very important before creating the table, to think carefully about all the information that will be needed. Make a written list of all data to be collected.
Starting Access in XP
Click the “Start Button”, All Programs, Microsoft Office, Microsoft Access 2010
Starting Access in ‘07
Click the Microsoft Office Button with the Office flag logo “Start Button”, All
Programs, Microsoft Office, Microsoft Access 2010.Launching the Program
Open Access by going to the Start button, All Programs, Microsoft Office, Microsoft Access 2010. Access opens, displaying the getting started screen, giving you several options. If you have already created a database, under Open Recent Database, click on the database file listed or click “More” to browse for the desired file. We want to begin a new database, so click Blank database button.
Creating a Database File
On the left side of the screen, you will need to name your file. Type a file name, browse to the location you wish to save the file, click Create.
For training purposes: Name the file “Student” Click the folder and choose
save to “Desktop”
Once that is complete, click Create. The Database Window opens, ready for you to begin creating your
new database.
pg. 2 3/28/2012 Quick
Access Toolbar
Tabs
Ribbons
Groups
Dialog box
launc
her Introduction to Access: Things to Remember
When Access opens, the appearance of the screen will be very different. Once you get used to the new 2010 features, we think you’ll find it much easier to use as you create and edit your databases. There are several features that you should remember as you work within Access 2010: the Microsoft Office Button, the Quick Access Toolbar, the Ribbon and Tab Bar, and Navigation Pane.
Quick Access Toolbar
The quick access toolbar is a customizable toolbar that contains commands that you may want to use. You can place the quick access toolbar above or below the ribbon. To change the location of the quick access toolbar, click on the error at the end of the toolbar and click Show Below the Ribbon. You can also add items to the quick access toolbar, simply right click on any item in the Office Button or the
Ribbon. Click Add to Quick Access Toolbar and a shortcut will be added.
Tab Bar and Ribbon
The tab bar is the panel at the top portion of the screen which contains tabs that display tools and commands in the ribbon. It has five tabs: Home, Create, External Data, Database tools, and Table Tools: Fields and Table. Each tab is divided into groups.
To view features in each tab, click the tab. Below is the list of groups within each tab.
Home: Views, Clipboard, Records, Sort & Filter, Find, Text Formatting Create: Templates, Tables, Queries, Forms, Reports
External Data: Import & Link, Export, Collect Data
Database Tools: Tools, Macro, Relationships, Analyze, Move Data, Add Ins Table Tools: Views, Add & Delete, Properties, Formatting, Field Validation
To view additional features within each group, click the dialog box launcher
(arrow) at the bottom right corner of each group.
pg. 3 3/28/2012
Expand/Collapse Button
Navigation
Pane Menu
Navigation Pane
The navigation pane displays database objects such as tables, forms, queries, and reports. The pane replaces the Database window which allows you to work with all objects in the database.
Navigation Pane Menu: allows you to change the objects displayed in the pane
Shutter Open/Close: allows you to hide the navigation pane
Expand/Collapse Button: allows you to switch groups.
Database Terminology
Table: A database table is very similar to a spreadsheet in that information is stored in rows and columns. Each row in a table is referred to as a record. Records are where the individual pieces of information are stored. Each record consists of one or more fields. Fields correspond to the columns in the table. Columns contain the same type of information for each item. The table has a header row that tells you what data is contained in the columns.
Query: select records from one or more tables in a database so they can be viewed, analyzed, and sorted on a common datasheet. Sometimes you don't want to see all the records at once, so queries let you add criteria to "filter" the data to just the records you want. Queries often serve as the record source for forms and reports. They help find, filter and retrieve data.
Recordset: a table that displays groups of records from a base table or as a query result.
pg. 4 3/28/2012
Design View
Forms: for viewing, adding and updating data. They can be developed from a table or a query and can include calculations, graphics, and objects.
Report: an output of data in the order you specified. It can perform calculations and display results. Reports can also print data.
There are two ways to view a table in Access to add data: Design View and Datasheet View. In Design
View you can only view the data types and descriptions, the records are not viewable.
pg. 5 3/28/2012 Datasheet View: you can display the records in a table where one record is a row. The columns are the fields you have defined for that table.
Adding New Fields
New fields can be added in either view, design or datasheet.
To add a new field in datasheet view:
Click on the column “Click to Add”, choose the type of field (text, numeric, etc). Rename the field, name it Homeroom. A new column is auto-created, choose the field type and rename it: Last Name, First Name, Middle Initial and Parents Name (Tip: to move from one column to the next, press the tab key)
To add a new field by using the New Field Button in datasheet view (must have table selected):
Click the Fields tab on the ribbon
Choose the field type
Enter these additional fields: Address, City & State, Zip Code, Home Phone To add a new field in design view:
Click the View button
Choose Design View
o Window appears to save table as:
Datasheet
View
pg. 6 3/28/2012 o Name it: Student Info
o Click Ok
Enter the heading for the new field by clicking on an empty field. All data types will be text. Use the down arrow to move to the next field.
Enter these additional fields: Business Phone, Mobile Phone, Way Home, Grade
Data Types
There are different types of data that can be entered in a database. When fields are created in database view, the data type defaults to text. To change the data type, you can do it in either view.
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 e-mail 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.
Lookup Displays either a list of values that is retrieved from a table or query, or a set of values that you specified when you created the field. The Lookup Wizard starts and you can create a Lookup field. The data type of a Lookup field is either Text or Number, depending on the choices that you make in the wizard.
NOTE Lookup fields have an additional set of field properties, which are located on the Lookup tab in the Field Properties pane.
pg. 7 3/28/2012 To edit data type in Design View:
Click the field name you wish to define
Use the drop down arrow
Select the appropriate data type
Format the fields in the fields properties box at the bottom
On the Grade Level field, change the date type to Number
To switch view & close the design view:
o Click the drop down arrow on View o Select Datasheet view
o will ask you do you want to save it, click yes o if prompted about data loss: click yes to continue To edit data type in Database View:
Switch to database view
Click the Table Tools-Fields
Click the column of the field you wish to change: Zip Code
Choose the appropriate data type: Number (a new field is added to database) Add Records to a Table: (You must be in datasheet view)
Click the new cell and type information (you can use the tab button to move from field to field)
Enter 4 students with the information below
When entering data, Access automatically assigns each record in a table a Primary key. The primary key is a numbering field that stores a unique number for each record as it is entered into a database. Do not worry about entering any information in the Id column.
o Wednesday G. Adams Gomez & Morticia 0001 Cemetery Lane Spookyville, LA 70502
337-300-5200, 337-333-5689, 337- 567-8965
walker
o Marcia C. Brady Mike & Carol 222 Clinton Way Hippy, LA. 70502
337-456-7523, 337-654-7459, 337- 985-7456
bus
o BamBam W. Flinstone Fred & Wilma
123 Stone Rd.
Bedrock, LA. 70502
337-123-4567, 337-236-5874, 337- 412-8965
car
o Judy B. Jetson George & Jane 456 Orbit Lane
Outerspace, LA. 70502
337-895-5982, 337-365-1254, 337- 745-6542
jet
pg. 8 3/28/2012 Find and Replace
To find data:
o The table must be selected/highlighted rather than a cell. In order to select the entire table, click the top left corner of the table (looks like a piece of paper folded down) this will select all records.
o Click the Home tab
o Click the Find button on the home tab.
o A dialog box appears, type what you are looking for (ex. Douet)
To replace data:
o The table must be selected/ highlighted o Click the replace button on the home tab.
o A dialog box appears, type what you want to find and what you want to replace it with (ex. Douet with Smith)
o Click Replace or Replace All (if you choose replace all, if cannot be undone. Also, will prompt and ask you if you want to continue.
Primary Key
The primary key is the unique identifier for each record. No two records can have the same primary key.
The field cannot be left blank. The best option is to leave with the default of “AutoNumber”
Add a Description to a Table
To add a description to a table
Right click on a table and choose Table Properties
On the pop-up window, click the Description text box
Type the description
o This table is created to record information about each student in my 2011-2012 class.
o Click OK
Primary Key inDesignView
Primary Key in Datasheet View
pg. 9 3/28/2012 Delete a Table
To delete a table:
Right click on the table and choose delete Rename a Table
To rename a table:
Right click on the table and choose rename
Rearrange Columns
To move a column in a table:
Select the column that you need to move
Do one of the following:
o In Datasheet view, drag the selected columns horizontally to the new position (A thick black line will show where the column will be located)
o In Design view, drag the selected columns vertically to the new position Sort Records
You can sort records by a single column or by two adjacent columns. Sorting columns is only temporary unless you save the table or form.
To sort by one column:
Highlight the column/field you wish to sort: way home
Choose the Sort ascending or Sort descending button
To sort two columns:
Move the columns so they are adjacent to each other (ex. grade level next to homeroom)
Select the columns that you want to sort, by holding the shift key and clicking the columns
Choose the Sort ascending or Sort descending from the Home Tab To clear the sort
Click the Remove Sorts button Filter Records
There are times when the most efficient way to gather information from a datasheet or form is to isolate (filter out).
You can filter records to include only the records that you want to display.
To filter by columns:
Open the table in datasheet view
Click the drop down arrow on the appropriate column (way home)
Uncheck the filters you do not want to include (jet, bus)
Click ok
pg. 10 3/28/2012 To remove a filter:
Click the filter button next to the field label
Click Clear Filter
Click Ok
Queries
Creating queries is one of the most important and frequently performed database tasks. Before creating a query, it is important to understand some fundamental query concepts.
Queries enable the user to view, select, change, and analyze data in a variety of different ways. Queries can also be used as the source of data for forms and reports. Queries enable users to ask questions about the data in tables. Queries can be saved and utilized as often as you need them.
Using a Query Wizard to Create a Query
A Query Wizard prompts the user for information and then creates a query based on the answers given.
To create a query using the wizard, follow these steps:
Click Create tab
Click the Query Wizard button
Choose the query type you wish to run (Simple Query Wizard)
Click Ok
To select fields from other tables, click the tables/queries drop down arrow
Choose the fields that you wish to include from the table o Double click the field or
o Select the field and click the single arrow
o Choose the fields: Parent’s Name, Address, Zip Code, State
Click Next
Name the Query in the Title field (Parent’s Address List)
You can choose to view the information or modify the design
Click Finish
To switch between tables and queries:
Open the Navigation Pane
Double click the name of the table or query you wish to view
Forms
There are three ways to view a form:
Design View: Allows you to design a form that includes a header, a footer, and details in the form. You can also add images and control which fields appear on the form.
Form View: Allows the user to enter and edit data or navigate through data in a field.
Layout View: Allows you to design the form and manipulate
data
pg. 11 3/28/2012 Create a Form
You can create a form from a table or a query.
Click the Create tab
Click the Form Design button
Click the Add Existing Fields
In the Field List box on the right, click show all tables, click the + symbol next to the appropriate table to expand the category.
Drag the fields you would like on the form (First Name, Last Name, Address, City, State, Zip Code)
Arrange the fields how you want them to be displayed, they overlap to begin with (click the grey square at the top of the field to move it)
To Preview the Form:
o Click the View button on the form tab o Click the Form View
o Use the arrows at the bottom to scroll through the different records.
Form Wizard
To create a form using the wizard:
Click Create tab
Click Form Wizard
Choose the Table/Queries that will be used in the form from the drop down list.
Select the fields that you need on the form by choosing the field and clicking the arrow that points to the right (to Remove, select the field and click the arrow to the left) or double click.
o For class purposes, select: First Name, Last Name, Address, City, State, Zip Code
Choose the layout that want (choose Columnar)
Click Next
Enter the title (Student Addresses)
Choose whether you want to open the form or modify the form
Click Finish
pg. 12 3/28/2012 To Edit the View of the Form:
Click View
Click Design View
Click and drag the boxes to the correct location for the form. To resize a box, click the corner handles and drag in or out.
To return to form view, click View-Form View
Create a Report
To create a blank report:
Click the Create Tab
Click Blank Report button
Click the Add Existing Fields button if it is not already visible on the right side of the screen.
From the Field List on the right side, click and drag the fields to the report o (drag first name, last name, home phone number)
To edit the layout, click View—Design View
To view the report, click View—Report View Report Wizard
To create using the report wizard
Click the Create tab
Click the Report Wizard button
Choose which Table/Queries you wish to have on the form
Choose the fields you want in your report (First Name, Last Name, Way Home)
Click Next
Choose the grouping levels you need (Way Home)
Click Next
Choose the sort order for your report (First Name, Last Name)
Choose the layout for your report (Tabular)
Click Next
Create a title for the report (Way Home Report)
Choose if you want to view the report or modify the form
Click Finish
pg. 13 3/28/2012 Printing a Report
To print a report
Choose the report you wish to print
Right click on the tab, click Print Preview
Choose the appropriate layout, margins, and paper size
Click Print
Click OK
Close Print Preview
To Print Labels
Click Create tab
Click Labels
Choose the size label you need (Avery 5160)
Click Next
Choose the font size, font name, font color, and font weight
Click Next
Choose the fields you want added to the labels. Press “Enter” to move to the next line and remember to add a space between your fields (First Name, Last Name, Address, City, State, Zip Code)
Click Next
Determine how you want the labels sorted (Last Name)
Click Next
Name the labels (Student Address Labels)
Determine if you want to see the labels the way they will look printed or modify the design
Click Finish
pg. 14 3/28/2012 Importing from Excel
Must have Access database open
If you want to import the data into a new database, need to create a blank database (that does not contain any tables, forms, or reports) before importing
Determine the location of the Excel spreadsheet that you are importing
Steps in Access:
o Click External Data tab o Click Excel
o Pop-up window appears
o Click Browse to find the location of Excel Spreadsheet
o Determine how and where you want to store the data in the current database (import the source data into a new table, append a copy of the records to the table, etc)
o Click OK
o Pop-up window appears, review data, click Next
o Determine if you want Excel headings to be field names in database, if so, check the box.
o Click Next
o To rename the columns, enter a new field name and choose the correct field type (text, numeric, etc)
o To rename another column, click that particular column and make necessary changes.
o Click Next for Access to add primary keys
o Name your sheet “Sheet 1”
o Click Finish
o Check the box to “Save Import Steps”
o Name your file in the “Save Database As” box and add a description o Click Save
To open database
o Look at Navigation Menu o Locate “Sheet 1”
o Double click to open and view
![(μ Methylenediphosphonato κ4O,O′:O′′,O′′′)bis[(ethylenediamine κ2N,N′)palladium(II)] tetrahydrate](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACH5BAEAAAAALAAAAAABAAEAAAICRAEAOw==)