Fourth assignments_Access

Getting started with Microsoft office access

Microsoft Access is part of the Microsoft Office suite. It is targeted for the information worker market, and is the natural progression for managing data when the need for a relational database arises or after reaching the limits of Microsoft Excel.

Microsoft Access is used by programmers and non-programmers to create their own database solutions. Access tables support a variety of standard field types, indices, and referential integrity. Access also includes a very intuitive query interface, forms to display and enter data, and reports for printing. The underlying Jet database which contains these objects is multiuser aware and handles record locking and referential integrity including cascading updates and deletes.

First, of course we have to double click on the access icon. Then, we will see Getting Started with Microsoft Office Access. Click Blank Database at New Blank Database (1).

Then, browse for a location to put the database file (2). File New Database | File name put maklumat pelajar. File New Database | Save as type Microsoft Office Access 2007 Databases. Then see at box 2, click create. A database is opened.

Table

We can define the fields directly in the new table.

See the box 3, it shows Table1. There we can see ID and Add New Field. ID is a discrete number to identify the particular student. So we no need to key in anything. At Add New Field, we key in any field such as identity card number (IC), name, address and so on and so forth by selecting Add New Field -> right hand click -> rename. Then save it by select Table1 (box 3) -> right hand click -> save -> Peribadi. Then select Maklumat Pelajar -> right hand click -> close table.

Remember that the name of the field should not space. Example: No IC. So we only need to key in IC.

For the table, I had built three tables which are Peribadi, Kokurikulum and Peperiksaan1 and Peperiksaan2.

Relationships

The function of relationship is to define how the data in tables is related, such as ID fields or name fields in different tables that should match.

Menu bar | Databases Tools -> Relationships. Relationship Tools | Design. It will pop out Show Table. In the Show Table, we select the tables that we want to relate. I had select all of my three tables, and it show as box 4.

Then we select ID from the first table box 5. We drag ID from first table to ID of second table. Edit Relationships will be shown. Then click create. And a line joint ID of first table and ID of second table. I did the same to join ID from Second table and ID from third table (see box 9).

Form

Form is used to display the selected criteria on a piece of paper.

Menu bar | Create -> More Forms -> Form Wizard (6). Then we select the criteria from Available Fields to Selected Fields (7). Then just follow the wizard. We can modify the form that we had chosen by using Menu BAR | Create -> Form Design.

Queries

Menu bar | Create -> Query Wizard.

We can select the criteria that we want by using Query Tools. View| Design View | Query Tools | Design -> select. See box 8. From the field Sukan 1 of kokurikulum Table, I would like to sort students that play hoki, therefore at the criteria I key in “hoki”.

If text we need to use “ ” , while if currency we use > for more than and <>

Report

After query, we report it. Menu bar | Create -> Report | Report Wizard. Then just follow the wizard.

Picture above shows the wizard view of the form. We can change or modify the layout of form and report by change the Design | view (10).

In my opinion, creating a database is not an easy task. We need to understand it deeply. As Pn. Sakinah said if we key in wrongly, we might be need to create one more time.