CSEC IT - Home



MS ACCESS

MS Access is a computerized database management software, which allows you to collect and organize related data. Data can be stored on anything e.g. student records, employee records, stock inventory records etc.

BASIC CONCEPTS

A database file is the entire database.

A table is an object in the database that contains related data on a specific topic e.g. personal information on all employees in a department.

A record is group of related fields e.g. all the personal information of one person in the department. In a table it is represented as a row in a table.

A field is a specific piece of data in a table e.g. the address of one person in the department. A record is made up of multiple fields. In a table it is represented as a column in a table.

CREATING A DATABASE USING MS ACCESS

Steps:

1. Click on the MS Access shortcut or go to All programs, select MS Office and then click on MS Access. This opens the Getting started screen.

2. To create a new database click blank database and in the panel on the right enter the database filename [STUDENTS RECORDS]. Then click create.

(Have them close the database)

OPENING AN EXISTING DATABASE

Steps:

1. Click on the MS Access shortcut or go to ALL PROGRAMS, select MS Office and then click on MS Access. This opens the GETTING STARTED screen.

2. If the database filename is listed in the panel in the right, open it or if not, select MORE and locate the file where it is stored and open it.

(Have them open STUDENTS RECORDS)

N.B. If a security warning is displayed, click on OPTIONS and check the radio button that is labeled ENABLE THIS CONTENT.

ACCESS NAVIGATION PANE

This is located on the left hand side of the MS Access window. It lists all of the objects – tables, forms, queries and reports that have been created in this database. To open an object you simply have to click on it.

CREATING A TABLE

By default when a database is created a table is created. To define the structure of the table and attributes of the fields in that table, you must go to the DESIGN VIEW.

Steps:

1. Click on the icon labeled VIEW and select DESIGN VIEW. This will open a SAVE dialogue box. Enter the name you will like the table to have: [FORM 1 STUDENTS]. This opens the table in design view and allows you to enter the field names required in this table and specify their attributes - data type, description and properties. (Use the Log on to IT pg. 237 to give explanation of data types).

|Field name |Data type |

|StudID {PK} |Number |

|Surname |Text |

|Firstname |Text |

|DOB |Date/Time |

|Address |Text |

|HPHone |Text |

|Religion |Lookup wizard |

|Sex |Text |

2. After entering the fields and their attributes, select a primary key. A primary key is a field in the table that is chosen to uniquely identify a record. To do this select the field and click the primary key icon. If the wrong field is selected it can be changed by selecting the right field and clicking the PK icon.

3. After choosing the PK, save the structure of the table by clicking on the SAVE icon on the quick access toolbar.

4. Change the view of the table to DATASHEET icon.

CREATING A TABLE FROM SCRATCH

Steps:

1. Click the CREATE tab and select TABLE DESIGN. This will open a table in design view and allow you to enter the fields and their attributes.

|Field name |Data type |

|StudID {PK} |Number |

|Maths |Number |

|Phys |Number |

|Chem |Number |

|Bio |Number |

|Eng |Number |

2. Select the PK and click the SAVE icon. This will prompt you for a table name

[FORM 1 TEST]

ENTER DATA IN A TABLE

Data can be entered into a table in several ways: using datasheet view, using a form or using a query.

Data Entry Using Datasheet View

Steps:

1. Ensure that the table is open in datasheet view.

2. Select the first field in the first record and enter the require data, move to the other fields in that record using the arrow keys or TAB key and enter the data. N.B. if any field is defined as AUTONUMBER, that field will be filled automatically as a record is added.

3. After completing one record move to the next by pressing the ENTER key. The record is automatically saved.

N.B. A primary key field must NEVER have a null value i.e. be blank. If it is left blank, an error message is displayed. To continue a value must be entered in that field.

Form 1 Students

|StudID |Surname |Firstname |DOB |Address |HPhone |Religion |Sex |

|0001 |Ali |Fyzool |5/10/91 |Penal |456-6789 |Hindu |M |

|0003 |Barnes |David |6/8/94 |Mason Hall |639-4531 |Anglican |M |

|0047 |Lara |Leanna |23/3/91 |Couva |677-5559 |Pentecostal |F |

|0032 |King |Mary |12/12/90 |Fyzabad |987-5421 |Anglican |F |

|0209 |George |Gary |28/9/90 |Canaan |655-0975 |Catholic |M |

Creating a Form Using the Form Wizard

A form is a database object that provides a secure more aesthetically pleasing display for data entry into a table. It allows you to add, update and delete records in a table from the form.

Steps:

1. Click the CREATE tab and select MORE forms from the forms group. From the menu select FORM WIZARD.

2. From the first page of the form wizard select the table to be used as the data source: [FORM 1 TEST].

3. Select the fields you wish to have displayed on the form, individually or all at once using the > or >> buttons. Click NEXT.

4. Select a form layout of your choice. Click NEXT.

5. Select a style of your choice. Click NEXT.

6. Name the form [FORM 1 TEST].

7. Click FINISH.

Navigating records using a form

At the bottom left of the form are navigation buttons. To move to the next record click the right arrow button. Click the >I button to move to the last record. To move backwards through the record set use the left arrow button.

Create a New Record Using a Access form

To add a new record click on the navigation button with the star on the right of it, this should bring up a blank record. Enter the given data. When finished close the form.

|StudID |Maths |Phys |Chem |Bio |Eng |

|0001 |70 |85 |80 |65 |75 |

|0003 |25 |40 |55 |45 |65 |

|0047 |80 |70 |60 |41 |75 |

|0032 |75 |65 |76 |65 |80 |

|0209 |60 |65 |75 |65 |88 |

Create a Query Using the Query Design Grid

Queries are used to select records from one or more tables based on specific criteria. There are different types of queries:

• Select: this extracts data from a table or tables

• Upadate: this makes changes to existing records

• Append: this adds records to the end of tables

• Delete: this deletes records

• Make table: this creates a new table from specific fields

• Crosstab: this calculates and restructures data for easy analysis

Steps: {SELECT QUERY}

E.g. {List the name and date of birth of each female student in the class}

1. Select the CREATE tab, go to the OTHERS group and click QUERY DESIGN.

2. Select a query, table or the tables from which the data must be selected. To do this, right click in the window and select SHOW TABLE. When that window opens highlight the table needed [FORM 1 STUDENTS] and click ADD. Repeat by highlighting any other table or query necessary and then click ADD. When all the tables have been added click CLOSE.

3. Select fields [FIRSTNAME, SURNAME, DOB, SEX] from the tables by double clicking on their names or in the QUERY DESIGN GRID, in each column click on the arrow and select the appropriate field from the list.

4. Enter the query criteria [F] in the appropriate field in the row labeled CRITERIA.

5. Run the query by clicking on the RUN icon in the RESULTS group. This should display the results of the query in datasheet view.

Practise the following:

1. List the names and sex of all Anglican students. Save as ANGLICAN

2. List the names and date of birth of each student born before 1993. N.B. date criteria must be placed between # signs e.g. ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download