Introduction to MS Access: A Comprehensive Guide

1. IT – som værktøj Bent Thomsen Institut for Datalogi Aalborg Universitet

2. Introduction to MS Access Bent Thomsen

3. Microsoft Access Window • Open Access • Start-Programs-Microsoft Access • Double-click on an Access file (Student Record) Viewing Objects Properties Objects Operations Objects Bar

4. Microsoft Access Objects • Tables • Store information with Columns (fields), rows (records) • Queries • Acquire selected information with certain criteria. • Forms • Display one record in the window • Convenient for entering, displaying, and printing data. • Reports • Display records with selected fields in a report layout. • Display multiple records in a page.

5. Microsoft Access Objects • Pages • Display records in form of web pages. • Macros • A set of commands that are executed automatically one after another. Macros are used to automate the performance of any repetitive task. • Modules • Provides a greater degree of automation through programming in Visual Basics for Applications (VBA)

6. Table-Datasheet View • Double-click a table, you are in datasheet view. • Add, edit, or delete records Field Names Current Record New Record

7. Table-Design View • Define the table initially and to specify the fields it will contain. Define Field Name Define Field Type

8. Print or Change Properties of Tables, Forms, Reports, Queries • In the database window • Right-mouse click an object (report, form, query, page) • Print • Cut • Copy • Email • Delete • Rename

9. Create a Database • Start-Programs-Microsoft Access • Select • Click on

10. Save the Database • Type in name of the database file • If you need to save the file into a new folder, you click on and type in the folder name. Click on • Type in the file name. • Click

11. Create a Table in Design View • Double-click • For each field • Type in a field name • Select a data type • Select “student ID” field • Click on to set a primary key • Click on to save the table • Type in the table name • Click on • Click to input data. Use “Tab” or arrow keys to navigate.

12. Data Input in the Table • Click on to go to the datasheet view when you use Design View to create a new table. • Type in information in the cells • “tab” key, or to navigate to a different field. • keys to a different record. • Adjust the column width • Move the mouse to the boarder between the two columns until a sign shows, and drag the boarder to adjust the column width. • Double-click the boarder between the two columns and the column width will be adjusted automatically. • Delete a record, select any cell of the record and click

13. Add a Field in a Table Created In Design View • Add a “parent’s name” field before “address” field • Select the “address” field, go to Insert and select “rows” • Type the “parents’ name” for the field name. • Click to update the table set up.

14. Create a Table by Using Wizard • Double-click on • Select the category of the wizard, “business” • Select “Student” in the “sample table” window. • Select “StudentID” in the Sample Fields window, click on • Click on , type in the new name “ID”. Click on • Select “Firstname”, “Lastname”, and other fields individually and use to move the field to the selected field window. • Click on

15. Select a Wizard and Fields

16. Name the Table • Type in the name of the table • Select , click on

17. Select a Primary Key Field • Select “ID” as the primary key, Next

18. Set Up Data Input

19. Create a Table by Entering Data • In database window • Double-click • In the Datasheet view • Double-click “field1”, type a field name “ID”. • Double-click “field2”, type a field name “First Name” • … repeat the above steps until you type all the fields. • You need to set up a primary key by going to Design View • Click , select “ID” field cell and click • Click to save the table. • Click to Datasheet View to input data.

20. Navigating and Updating Data in the Table Save Data-Access automatically save a table as soon as you move to the next record or close the table. Previous Record Next Record Add a New Record

21. Updating Records in a Table • Adding a record • Type in the fields right next to • Deleting a record • Select a record by clicking , hit “delete” key or . • Change a record • Highlight or click the cell you want to change and type new information

22. Create a Form with AutoForm • Form, easy to input and update data, update data on more than one table. • In the database window, select the table “anth100address”, go to and select

23. Modify the Form • Click on to switch to Design View • Drag the right border of the form box to the right to enlarge the size of the form window. • Select the “Mailing Address” box, drag the right border to the right. • Click on to switch back to Form View

24. Save the Form • Click on , type in the form name and click

25. Update Records in a Form • In the Design View of the form • Use to select a record • Change the information by highlighting a field and typing in new information • To delete a record, select a record and click • Use to add a new record. The record will be added to the table simultaneously.

26. Search for a Record in a Form • In the Design View of the form • Edit-Find, type the last name “Carpenter”, select the table and “whole field”. • Click

27. Create a Form with Form Wizard • In the database window, select • Double-click • Select a table you want to create a form from. • Select a field and click . Click to select all fields. Click

28. Create a Form from Wizard • Select a form layout such as • Click • Select a style such as “Expedition” • Click • Type a form title. • Select “Modify the form’s design”. • Click • Then you are in form design view and you may modify the form.

29. Set Up Relationships • In the Access window • Click the “relationship” icon • Click the “add a table icon” • Select “allgrades” table • Click • Select “anth100address” table • Click • Select “anth100grades” table • Click • Add all tables when necessary • Click

30. Create a Relationship • Click on the “ID” field in “allgrades” table. • Drag the mouse to the “ID” field of “anth100address” table like you are drawing a line between the two fields. • Check “Enforce Referential Integrity” • Click

31. Final Relationship Chart • Repeat adding a table and drawing a relationship for all tables. • Drag the “Blue” bar on a table to rearrange them as follows and click to save the relationship chart.

32. Create a Report • Report • Display records in selected fields • Display students grades for Anthropology 100 with ID, names, final scores, and grades • In the Database Window, select • Double-click on

33. Select Fields • Select Anth100grades, select a field, click , click Click In the “group” window.

34. Sort Window • Click in the Sort Window. You may sort a field at the report design view later on.

35. Select Sort Field • Select fields for sorting. Click • In the report, the fields will be listed in the order as sorting order.

36. Select Report Layout • Select a layout and click

37. Select a Report Style • Select a style and click

38. Title the Report • Type the title of the report, select “modify the report’s design, click . Click to save the report.

39. Preview the Report • Click preview icon to go back to the report preview window.

40. Create a Query by Using Wizard • Running a query is to display selected fields with certain criteria. • Anthropology 100, students who scored higher than 80 with ID, names, final scores, grades. • Open the file “Student Records” by double-clicking it. • In the Database Window, click • Double-click

41. Select a Table and Fields • Select a Table • Select a field • Click • Click

42. Select Ways of Displaying a Query

43. Name the Query

44. Set Up the Criteria and Run the Query • Type in the “>80” in the “Scores” criteria cell. • Click to run the query. Click to save.

45. Create a Query in Design View • Display a query for Art 200 grades with ID, Names, Scores less than 90, and Final Grades. • In the database window, click on • Double-click on • Select • Click • Click to close the table selection window

46. Select the Table • Click , click , click

47. Select Fields, Input Criteria, Run Query • Double-click on ID, First Name, Last Name, Score, and Grade field individually. • Type “>70” in Grades/Criteria cell. • Click to run the query. Use Edit-Clear Grid to clear the query fields.

48. A Query with WildCards • Using WildCards to select students with Bs grade, including B, B+, and B- • Type “B*” in the criteria cell in the Grade field of Art 200 Grade Table query window. Hit Enter. • Click , and double-click • Select Art 200 Grade table, click , click • Select fields: First Name, Last Name, Scores, Grade. • Type B* in the Grade criteria cell of Grade field and hit enter. • Click to run the query.

49. Fields, Criterion, and Run a Query • Select fields by double-clicking the field. • Type B* in Criteria cell of Grade. Hit enter. • Click to run the query.

50. A Query with Compound Criteria • And • One criteria and the other criteria are all met. • Students grades >80 in final scores and >80 in final project in Art 200 • Type in criteria in two different cells in the “criteria” row. • Practice---Grades >=60 and <=90 • Or • One criteria or the other criteria is met. • Students grades A or B (display A and B students) • Type in the second criteria in the “or” row • Practice---grades either <=70 or >=90.