Access

Microsoft Access – a relational database software program used to manage data that can be organized into lists of related information

Advantages of Access

· Duplicate data is minimized

· Information is more accurate

· Data entry is faster and easier

· Information can be viewed and sorted in multiple ways

· Information is more secure

· Information can be shared among several users

· Information retrieval is faster and easier

Database Terminology

· Field – consists of a specific category of data such as a customer’s name, city state, or phone number

· Record – a group of related fields that describe a person, place, or thing

· Key field – a field that contains unique information for each record, such as a Social Security number for an employee or a customer number for a customer

· Table – a collection of records for a single subject, such as all of the customer records

o databases – a collection of tables associated with a general topic (for example, sales of products to customers)

· Database objects – the parts of an Access database that help you enter, view, and manage the data are tables, queries, forms, reports, pages, macros, and modules.

· Entering and editing – cab be done in tables, queries, forms, and pages.

Access objects and their purpose

· Table – contains all of the raw data within the database in a spreadsheet-like view; tables are linked with a common field to minimize data redundancy

· Query – provides a spreadsheet-like view of the data similar to tables, but allows the user to select a subset of fields or records from one or more tables; queries are created when a user has a question about the data in the database

· Form – provides an easy-to-use data entry screen which often shows only one record at a time

· Report – provides a professional printout of data that may contain enhancements such as headers, footers, graphics, and calculations on groups of records

· Page – creates dynamic Web pages that interact with an Access database; also called Data Access Page

· Macro – stores a set of keystrokes or commands, such as the commands to display a particular toolbar when a form opens

· Module – stores Visual Basic for Applications programming code that extends the functions and automated processes of Access

Using Access

Starting Access

· by using the menus found when you click the Start button on the taskbar

· when available, by clicking on an icon on the desktop

To open a specific database within Access

· click the Open button on the Database toolbar

· use the Open portion of the Getting Started task pane

· open the database file from My Computer or Windows Explorer

Editing Records

· Change contents of a Record– , click the field you want to change, then type the new information

· Deleting unwanted Records – clicking the field and using [Backspace] or [Delete] to delete text to the left or right of the insertion point.

Edit mode keyboard shortcuts

· [Backspace] – Deletes one character to the left of the insertion point

· [Delete] – Deletes one character to the right of the insertion point

· [F2] – Switches between Edit and Navigation mode

· [Esc] – Undoes the change to the current field

· [Esc][Esc] – Undoes all changes to the current record

· [F7] – Starts the spell check feature

· [Ctrl][‘] – Inserts the value from the same field in the previous record into the current field

· [Ctrl][;] – Inserts the current date in a Date field

Organizing Fields

· Design the reports that you want the database to produce – designing or sketching the reports that you want the database to produce helps you identify the fields that the database should store

· Collect the raw data that is required to produce the reports – use raw data, the individual pieces of information entered into each field

· Identify a name and data type for each field – based on the reports you design and the raw data you collect, identify the field name and data type for each field that you want

Choosing between the Text and the Number Data Type

Assigning data types – avoid choosing the Number data type for a Telephone or Zip Code field.

Data Type

· Text – information or combinations of text and numbers, such as a street address, name, or phone number

· Memo – lengthy text such as comments or notes

· Number – numeric information such as quantities

· Date/Time – dates and time

· Currency– monetary values

· AutoNumber – integers assigned by Access to sequentially order each record added to a table

· Yes/No – only one of two values stored (Yes/No, On/Off, True/False)

· OLE Object – objects and files linked or embedded (OLE) that are created in other programs, such as pictures, sound clips, documents, or sp4readsheets

· Hyperlink – web and email addresses

· Lookup Wizard – not a data type, but a wizard that helps link the current table to another table or list

Understanding Sorting, Filtering, and Finding

· Sorting – refers to reorganizing the records in either ascending or descending order based on the contents of a field.

· Ascending Order

o Text fields – sort from A to A

o Number and Currency fields – sort from the lowest to the highest value

o Date/Time fields – sort from the oldest date to the date furthest into the future.

· Filtering – means temporarily isolating a subset of records

· Finding – columns are inserted to the left of the cell pointer

o Find What – provides a text box for your search criteria

o Look in – determines whether Access looks for the search criteria in the current field or in the entire data sheet

o Match – determines whether the search criteria must exactly match the contents of the whole filed, any art of the field, or the start of the field

o Search Fields as Formatted – determines whether the search criteria are compared to the actual value of the field or the formatted appearance of the value

· Replace tab – provides a Replace With text box for you to specify replacement text

· Using wildcards – symbols you use as substitutes for characters to locate data that matches your criteria.

o Asterisks (*) – represents any group of characters

o Question mark (?) – stands for any single character

o Pound sign (#) – stands for a single number digit

Sort, Filter, and Find buttons

· Sort Ascending – sorts records based on the selected field in ascending order (0 to 9, A to Z)

· Sort Descending – sorts records based on the selected field in descending order (Z to A, 9 to 0)

· Filter By Selection – filters records based on selected data and hides records that do not match

· Filter By Form – filters records based on more than one selection criteria by using the Filter by Form window

· Apply Filter or Remove Filter – applies or removes the filter

· Find – searches for a string of characters in the current field or all fields

Filtering Records

Filtering the datasheet temporarily displays only those records that match given criteria.

· Criteria – rules or limiting conditions you set

Comparison Operators

· Greater than (>)

· Greater than or equal to (>=)

· Less than (<)

· Less than or equal to (<=)

· Not equal to (<>)

· Searching for blank fields

o Is Null – finds all records where no entry has been made in the field

o Is Not Null – finds all records where there is an entry in the field, even if the entry is 0. Primary key fields cannot have a null entity.

Planning a Form

· Control – an element placed on the form to display or describe data

· Form design tasks

o Gather the source documents used to design your form

o Determine the best type of control to use for each item on the form

§ Bound controls – display data from the underlying record source and are also used to edit and enter data

§ Unbound controls – do not change from record to record and exist only to clarify and enhance the appearance of the form

o Determine the underlying record source

§ Record source (recordset) – supplies the data that is presented by the bound controls on the form

o Name the form

Form Controls

· Label – provide consistent descriptive consistent descriptive text as you navigate from record to record; the label is the most common type of unbound control and can also be used as a hyperlink to another database object, external file, or Web page

· Text box – display, edit, or enter data for each record from an underlying record source; the text box is the most common type of bound control

· List box – display a list of possible data entries

· Combo box – display a list of possible data entries for a field, and provide a text box for an entry from the keyboard; combines the list box and the text box controls

· Tab control – create a three-dimensional aspect to a form

· Check box – display “yes” or “no” answers for a field; if the box is checked, it means “yes”

· Toggle button – display ”yes” or “no” answers for a field; if the button is pressed, it means ”yes”

· Option button – display a choice for a field

· Option group – display and organize choices (usually presented as option buttons) for a field

· Bound object frame – display data stored by an OLE (Object Linking and Embedding ) field, such as a picture

· Unbound object frame – display a picture or clip art image that doesn’t change from record to record

· Line and Rectangle – draw lines and rectangles on the form

· Command button – provide an easy way to initiate a command or run a macro

Creating a Form

· Form Design View – provides a layout screen in which the form developer has complete control over the data, layout, and formatting choices that the form will display. Because Form Design View is the most powerful and flexible technique used to create a form, it is also the most complex. Form Design View is also used to modify existing forms, regardless of how they were created

· Form Wizard – provides a guided series of steps to create a form. Prompts for record source, layout, style, and title

· AutoForm – Instantly creates a form that displays all the fields in the chosen record source. The five different AutoForm options (Columnar, Tabular, Datasheet, Pivot Table, and Pivot Chart) correspond to five different form layouts in the New Form dialog box

· Chart Wizard – provides a guided series of steps to create a graphical arrangement of data in the form of a business chart such as a bar, column, line, or pie chart that is placed on a form

· Pivot Table Wizard – provides a guided series of steps to create a summarized arrangement of data in a form called a Pivot Table. Fields used for the column and row headings determine how the data is grouped and summarized

Form Sections

· Form Header – controls placed in the Form Header section print only once a t the top of the printout; by default, this section is closed in Form Design View

· Detail – controls placed in the Detail section appear in Form View and print once for every record in the underlying table or query object; all controls created by the Form Wizard are placed in this section

· Form Footer – controls placed in the Form Footer section print only once at the end of the printout; by default, this section is closed in Form Design View

Multimedia controls

· Image – adds a single piece of clip art, a photo, or a log to a form

· Unbound object frame – adds a sound clip, a movie clip, a document, or other type of unbound data (data that isn’t stored in a table of the database) to a form

· Bound object frame – displays the contents of a field with an OLE Object (object linking and embedding) data type; an OLE Object field might contain pictures, sound clips, documents, or other data created by other software applications

Report Sections

· Report Header – at the top of the first page of the report. Label controls containing the report title; can also include clip art, a logo image, or a line separating the title from the rest of the report

· Page Header—at the top of every page (but below the Report Header on page one) – text box controls containing a page number or date expression

· Group Header – before every group of records. Text box controls for the field by which the records are grouped

· Detail – once for every record. Text box controls for the rest of the fields in the recordset (the table or query upon which the report is built)

· Group Footer – after very group of records. Text box controls containing calculated expressions, such as subtotals or counts, for the records in that group

· Page Footer – at the bottom of every page. Text box controls containing a page number or date expression

· Report Footer – at the end of the entire report. Text box controls containing expressions such as grand totals or counts that calculate a value for all of the records in the report.

Hands-On exam section

You will create a database that requires the following skills:

· Design and create a table

· Enter data

· Switch to the Datasheet View

· Adjust the field widths

· Print

· Edit data

· Sort

· Create a query