Microsoft Access – Tutorial 1

1. Microsoft Access – Tutorial 1 • if you need to review general Microsoft Office procedures such as: • menus • toolbars • task panes • files • help • printing • please go through the first section of your text book

2. Microsoft Access – Tutorial 1 Access Tutorial Files • Tutorial 1 uses Northeast Seasonal Jobs International (NSJI) • this organization is a placement agency that helps foreign students to get seasonal work (summer jobs) in the U.S. and Canada • NSJI uses Microsoft Access to join students and to job postings

3. Microsoft Access – Tutorial 1 Introduction • databases are used to: • organize • store • maintain • retrieve • sort • information such as: • employer’s names and addresses • available positions and wages

4. Microsoft Access – Tutorial 1 Introduction • data must be organized into fields • fields are a single characteristic or attribute of a: • person • place • object • event • idea • examples of fields include: • employer ID • employer name • employer address • employer phone number • much as you would see in a contact list

5. Microsoft Access – Tutorial 1 Introduction • related fields are grouped together into tables • a table is a collection of fields that describe a person, place, object, event or idea • the field value is the specific content or value of a field and is listed at the top of the table column • the values that appear below the field values in rows are called records • a collection of related tables is called a database or relational database

6. Microsoft Access – Tutorial 1 Introduction • open the database file seasonal in the folder COMP1110TedSnellW2011 on the shared/common directory • the Database window will open • on the left of it, you will see the Objects bar • from the objects bar, you can view and work with the major object groups such as: • tables • queries • forms • reports

7. Microsoft Access – Tutorial 1 Tables • you will notice that there are two tables, Employer and NAICS • NAICS contains North American Industry Classification Codes which classify businesses according to their activities • open the Employer table and maximize it • the table opens in datasheet view and looks much like an Excel spreadsheet • this table has 13 fields and 45 records (scroll to see them) • you can select fields by clicking on the column headings • you can select records by clicking on the record selector (left side of the row) • you can navigate through records using the navigation buttons on the bottom left

8. Microsoft Access – Tutorial 1 Keys • you will notice the Employer ID field on the left side of the table • it is referred to as the primary key of this table • a primary key is a field (or collection of fields) whose values uniquely identify each record in a table • the primary key is unique and never repeats in the table

9. Microsoft Access – Tutorial 1 Keys • tables are connected together using common fields • common fields appear in more than one table • when the primary key from one table appears in another table, it is called a foreign key • foreign keys may be repeated

10. Microsoft Access – Tutorial 1 (relational) database management systems (DBMS) • a database management system, such as Microsoft Access is used to manage databases • you can: • create database structures containing • fields • tables • table relationships • add new records • change field values in existing records • delete records • build queries to answer questions about your data • create reports • protect databases through security, control and recovery

11. Microsoft Access – Tutorial 1 Queries • a query is a question you ask about the data stored in your database • Access responds to your query by displaying the specific records and fields that answer your question • create a query by telling Access: • which fields you need • what criteria should be used to select the records • Access displays only the information you want • ex. display employers located in Boston

12. Microsoft Access – Tutorial 1 Queries • close the seasonal table and click queries in the Objects bar • open the query called Contacts • the query displays the Employer information in a different way • it does not affect the original table • create a query using the Query Wizard • Create tab query wizard • select the Simple Query Wizard and the Employer table • include the following in the query: • EmployerName, City, StateProv, ContactFirstName, ContactLastName, Phone • name the query EmployerList • sort the query list by clicking the arrows beside the column names (ex. by name ascending)

13. Microsoft Access – Tutorial 1 Forms • forms allow you to maintain, view and print records • click Forms in the Object bar • Create > More Forms > Form Wizard to open the New Form Dialog box • select the query Employer List that we created earlier and choose AutoForm: Columnar • you can edit information in this view • navigate your records with the buttons on the bottom • new records can be created using the >* button

14. Microsoft Access – Tutorial 1 Reports • a report is a formatted printout (or screen display) of the contents of one or more tables • reports can be easily created using the Report Wizard • go to the Reports section of the Objects bar • create a New report with the Report Wizard (Create > Report Wizard) • Add all the fields from the Employer List Query • Make a columnnar report with the Aspect Theme • you can navigate your records using the buttons on the bottom