BA271 Week 6 Lecture

1. BA271 Week 6 Lecture Dave Sullivan

2. Goal for today… • Status Report – Review where we are … • Begin learning about Microsoft Access and databases

3. Microsoft Access Assignments Tutorial 1 – Introduction To Microsoft Access 2003 Tutorial 2 -- Creating and Maintaining a Database Assignment 1: Creating a Database

4. Define some key Access terminology • Field – A single characteristic or attribute of a person, place, object, event, or idea. • Record– A set of related field values. • Table – A collection of records that identify a category of data, such as Customers, Orders, or Inventory.

5. Illustration of fields, recordsand a table Fields are grouped into records, and records are grouped into a table.

6. A sample Access database • Nwind.mdb, a database for the NorthWind Traders company

7. Relating tables using a common field The primary key in the Employer table (EmployerID) is the common field that relates this table to the Position table. PositionID is the primary key in the Position table. The EmployerID field is a foreign key in this table. Primary keys can only have one occurrence in a table. Foreign keys may have multiple occurrences.

8. Activities you should do: • Complete Access Assignment #1.

9. Relational database and keys • A relational database is a collection of tables that are related to one another based on a common field. • A field, or a collection of fields, is designated as the primary key. • The primary key uniquely identifies a record in the table. • When the primary key of one table is represented in a second table to form a relationship, it is called a foreign key.

10. The components of the Accessand Database windows

11. Use the Objects bar to view database objects • To see a list of objects in a category, click that category onthe Objects bar. • The Queries category has been selected, and a query named Contacts is shown in the window. • You can alsocreate newobjects within each object category.

12. The Database window • TheDatabase windowis the main control center for working with an Access database. • The Database window contains amenu bar, an objects bar, and a groups bar. • The Objects barlists all the objects available in the database • The list of objects consists oftables, queries, forms, reports, pages, macros, andmodules. • You can click on any of the objects in the Objects bar to obtain a list of objects of that type

13. A table in datasheet view

14. The navigation bar buttons

15. Learn how Access saves a database • TheSave buttonin Access differs from the Save button in other Windows programs. • When you press the Save button in Access, you aresaving the design of the Access objects and NOT the data itself. • Access saves data as it is entered • For this reason, the location at which you are storing yourdatabase must always be accessiblewhile working with a particular database. • If the database is located on a diskette, the diskette must be in the diskette drive at all times while working with this particular database

16. Sample Query Datasheet view Query Datasheet view looks very similar to Table view. However, the data displayed in a query does not have to be in the same sequence as it appears in table view. You candisplay fields in any order in a query.

17. What is an Access query? • If you want to see just a portion of the data in a table you can create a query. • Aqueryis a question you ask about the data stored in a database table. • Access responds by displaying the data according to your question. • For example, if you ask to see all the customers from New York, the response would be to display only the records whose state field matches with NY

18. Open an existing query and create new queries • You canopen an existing queryby clicking Queries on the Objects bar and then selecting the query you want to open. • You can alsocreate your own queriesby clicking New on the Database window. • To create a new query, you can use theSimple Query Wizard, which will bring you through the selections you want for your query.

19. Navigating a query and sorting the results • When you run and get the results of your query, you can reorganize the data bysorting the datasheetin either ascending or descending order. • Click the pointer anywhere in the column you wish to sort • Click the Sort Ascending or Sort Descending buttons on the Query Datasheet toolbar to sort the results in the desired sequence • You can navigate through the records by using thenavigation buttonson the Navigations toolbar.

20. The Simple Query Wizard The Simple Query Wizard assists you in the creation of a new query.

21. Selecting fields to include in a query • You can create a query that willdisplay only selected fieldsfrom a table instead of displaying all fields. • In theSimple Query Wizarddialog box, select which fields you want included in the query. • Move all the fields into the Selected Fields box • Move the fields one at a time • Remove fields out of the Selected Fields box by pressing one of the remove buttons • If you wanted to select all the fields except one, you can move them all to the selected fields list and thenremove the one fieldyou don't want. • Once you have made your selections, press Next to move to the next dialog box in the Wizard.

22. Access Form View Forms display one Table or Query record at a time. The navigation bar has the same buttons seen in Table and Query Datasheet view.

23. Create an Access form • Aformallows you to view your dataone record at a time. • Forms are useful formaintaining,viewing, andprintingrecords in a database. • You can create your own form in theForms Design windowor you can use theForms Wizardto create a form. • The easiest way to create a form is to use theAutoForm Wizard.

24. Use the AutoForm Wizard • TheAutoForm Wizarduses a table (or query) you select as the basis to create a form that displays all the fields of the table (or query). • Once you have created your form, you canview the records one record at a time. • The form has a navigation bar just like thenavigation baryou have already used in the Table or Query Datasheet view.

25. The Report Preview window When a report has been created, you can preview it on the screen. You can navigate through the pages in the report by using thenavigation buttons at the bottom of the preview.

26. Create, preview, and navigate a report • You can create areport, which is a printed version of your data, that is formatted according to your specifications. • The datain the report can consist of datafrom a single table or multiple tables. • Access has aReports Wizardthat allows you to easily create a report. • The report can bebased on a tableor it can bebased on a query. • If the report is based on a query, it will contain the same fields that were selected for the query

27. Backup and restore a database • You may want to create abackupof your data so that if you lose or damage your database, you can recover from the backup. • You can use a backup tool, such as the Microsoft backup tool, or some other backup program. • To restorethe data from the backup, you need touse the same backup toolaccording to the instructions associated with that tool.

28. Compacting reduces database storage size Compacting a database reduces the space used by the database, making the space available for other uses.

29. Setting the Compact on Close option Select the General tab of the Options dialog box. Click the check box to insert a check mark so the database will be compacted every time it is closed.

30. Compacting a database • It is a good idea to periodicallycompact and repaira database torecover wasted spacecreated by adding, deleting, and modifying records. • Access has a Compact and Repair feature: • Open a database • Click onToolsand thenOptions • On the Options menu, selectCompact on Close. Selecting thisoption will cause your database to be compacted and repaired every time you close it

31. Convert an existing databaseto Access 2002 format • It is possible to convert a database created in Access 2000 to an Access 2002 database. • To convert an Access 2000 database to Access 2002 format: • Make certain thedatabase to be converted is closed • ClickToolson the menu bar, then point toDatabase Utilities • Point toConvert databaseand choose theformat to convert to • Enter the nameof the database to convert, and aname for the converted databaseto be stored under

32. Steps to complete Tutorial 1described on pages AC1.27 to 1.28 4. Copy “Seasons.mdb” to your classwork folder Rename “Table 1” as “Employers” 5. Open “Employer” table 6. Open Help – “set page setup options for printing” Select “for a table, query, form or report” Read explanation & close

33. Steps to complete Tutorial 1described on pages AC1.27 to 1.28 7. Create Query w/wizard Table = “Employers” Fields = in this order:>City >Employer Name >Contact First Name >Contact Last Name >Phone fields Next>> Name = “Employer Phone List ” Modify Query Design… Sort “City ” ascending

34. Steps to complete Tutorial 1described on pages AC1.27 to 1.28 8. AutoformWizard Create Form using “Employers”table All Fields Columnar format Standard = OK Name form “Employer Info” “Open form to view or enter info” Click on Help “What’s this” & point to Record # box Goto Record #42

35. Steps to complete Tutorial 1described on pages AC1.27 to 1.28 12. Auto Report Wizard on “Employers Table” Save as ”Employers” 13.Set option to compact “Seasons” database on close 14. Convert to: Access 2002 format & save as “Seasons 2002” Access 97 format & save as “Seasons 97” View differences in the file sizes with a file manager 15. ExitAccess