Microsoft Access Manual 1

1. Microsoft Access Manual 1 By DhawalaKovuri ElhamS.Khorasani Ismail Guneydas

2. Creating a Blank Database • Open up Microsoft Access • Click on Blank document button • On the right panel, Specify the location for saving your database and click on create.

4. Creating Table • When creating a blank database, by default a blank table will be created .You can rename this table by simply right-click on the table name and choose rename. • For creating a new table, click on the create tab and on the ribbon choose table • The left panel is called the navigation bar and shows all the tables that have been created for this database.

6. Modifying Table • There are two different views used for modifying tables: • Datasheet view: used for inserting data to the table • Design view: used for modifying the table structure(fields)

8. Design view • Design view is where you can define your table fields and their data types. • Before switching to the design view, the table must be given a name. • By default an “ID” field is generated for each table. This field is a unique identifier of each record. It is referred to as the Primary Key Field. It is automatically incremented when a new record is added to the table.

10. Primary Key • A field, or a collection of fields, in a table is designated as the primary key. • The primary key uniquely identifies a record in the table. • Example: SSN is unique for each person, therefore it can serve as a primary key for the “person” table. • To set a field as a primary key, right click on the field name and choose the primary key option. • To set more that one filed as a primary key, first select all the fields, then right click and choose primary key option.

12. Field properties • Each field has some propertiesthat can be changed. Some of these properties are: • Field Name: • Field Type: • Field Size: Specifies the max size of the field. For example “first name” is not usually bigger than 50 characters • Caption: specify the label for the field when used on the form, by default caption is the field name • Default value: a value that automatically entered in this field for new records

13. Example (Client Table) • As an example, let’s create a new table to store client’s information and name it “client”. • Each client, has these attributes: SSN, firstname, lastname, phone, address, yearsOfFirm • SSN is the primary key for this table. • Firstname is not usually more than 40 characters, so the field size of firstname is specified as 40.

15. Datasheet view • Datasheet view is usually used for inserting/deleting/modifying records in the table. • Remember the data that you enter for each field must match the data-type and attributes of the field, for example in the person table, we specified the data type of the phone field as a number, now if I try to insert something like 453-6025 in phone field, access gives an error. Therefore the data type of the phone field should have text rather than number.

17. Adding/deleting Records • For adding a new record after a row, select the entire row, right click and choose new record option. • For deleting an existing row, select the entire row, right click and choose delete row option

19. Sort records • For sorting records by a specific field, right click on the column and choose “sort” option. • For example, sorting the records by their firstname in the person table

21. In-lab Exercise 2 As an exercise create a student table with the following fields: • StudentID :Number, Primary key • Name: text, fieldsize:50 • Nationality: text ,fieldsize:10 • Graduate: Yes/No • Tuition: Currency • Major: text, fieldsize:15 Insert some record to the table and sort the table by “name” field. Submit your file to the blackboard under lab-exercise2 link.