Introduction to MS Access Bent Thomsen Microsoft Access

Introduction to MS Access Bent Thomsen

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

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.

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)

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

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

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

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

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

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.

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

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.

Create a Table by Using Wizard • • Double-click on Select the category of the wizard, “business” Select “Student” in the “sample table” window. Select “Student. ID” 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

Select a Wizard and Fields

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

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

Set Up Data Input

Create a Table by Entering Data • In database window – Double-click – In the Datasheet view • Double-click “field 1”, type a field name “ID”. • Double-click “field 2”, 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.

Navigating and Updating Data in the Table 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

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

Create a Form with Auto. Form • Form, easy to input and update data, update data on more than one table. • In the database window, select the table “anth 100 address”, go to and select

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

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

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.

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

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

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.

Set Up Relationships • In the Access window – Click the “relationship” icon – Click the “add a table icon” – Select “allgrades” table – Click – Select “anth 100 address” table – Click – Select “anth 100 grades” table – Click – Add all tables when necessary – Click

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

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.

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

Select Fields • Select Anth 100 grades, select a field, click Click In the “group” window.

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

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

Select Report Layout • Select a layout and click

Select a Report Style • Select a style and click

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

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

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 doubleclicking it. • In the Database Window, click • Double-click

Select a Table and Fields • Select a Table • Select a field – Click • Click

Select Ways of Displaying a Query

Name the Query

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.

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 to close the table selection window

Select the Table • Click , click

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.

A Query with Wild. Cards • Using Wild. Cards 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 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.

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.

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.

“And” Logic • Student grades >80 in final project and >80 for final grades – Type “>80” in Final Project and Score cells in the criteria row. Click

“Or” Logic • Student grades A or B • In the Score fields, type A in Criteria cell and B in Or cell. Click to run the query.

Sorting Data in A Query • List student grade query for Art 200 with alphabetical order in Last Name. • Select “Ascending” in Sort cell of Last Name field. • Click to run the query. Click to save the query.

Set up Calculations in a Query • Select “Classes”, “Scores”, and “Grades” fields. • Select “Avg” in the “Scores” field in Total row. • Select “Count” in the “Grades” field in Total row.

Total Query - Calculations in a Query • Display the average score and number of students in all classes. – Click , and double-click – Select “allgrades” table and click and – Select “Classes”, “Scores”, and “Grades” fields in the “field” row. – Click Total icon – In the “Total” row • the first “Scores” field, select “avg” • The second “Grades” field, select “count”

Query in SQL View Right click on query and select

Plan and Create a Database • Determine the purpose of the database. • Decide what tables to create (address, grades) • Determine fields in each table (Name, phone, …) • Decide to create an ID field for each table to serve as the primary key. – The primary key for a table is a unique identifier for each record in the table. • Student ID# is a type of primary key in a student database.