DATABASE APPLICATION | MICROSOFT ACCESS

STARTING MICROSOFT ACCESS

To start Microsoft Access, follow the steps below:

1. Click start button, the program menu
2. Move to the MS Office menu
3. Click on MS Access

Once Access is running, an initial screen will be displayed. From the initial screen, you can either (i) create a new blank database (ii) open an existing database

Choose blank database and specify a new filename for the database.

Click on the OK button to create the new database. Creating a table using the design view tables is the foundation of every database.

To create a table, follow the procedure below.

1. Click create tab and click on the table icon
2. Pull down the view menu and choose design view. The table design view will appear.
3. Fill in the field Name, Data Type and description for each column filed in the table.

1. Highlight the require filed and click on the primary key button on the button bar. A small key appears next to the field name on the left side.
2. Pull down the office (or file) menu and choose Save As menu item. Change the Table name to a name of your choice and click OK button.

Viewing and adding data to a table

In table using a simple spreadsheet – like display. To bring up the customer table, highlight the name of the table and then double-click on the name of the table.

To add data to the table, simply type in values for each of the fields. Press the Tab key to move between fields within a record. Use the up and down arrow keys to move between records.

To save the new data, pull down the office menu and choose save. To navigate to other records in the table, use the navigation bar at the bottom of the screen.

Creating relationship between table

Note that one of the main characteristics of relational database is the fact that all tables are related to one another.

In the bank database under consideration, the Customer table is related to the account table by virtue of the Customer ID appearing in both tables. MS. Access has a way of showing this relationship by using the relationship screen.

Activity

1. Close the account table and Customer table by right clicking on one of them and select close ALL
2. Click on the database tools tab and then click on relationships button. The blank relationship screen will appear. The Show Table dialog box will also appear.
3. Highlight both the Customer table and Account table and then click on the Add button

1. Click on the close button to close the dialog box. The relationship screen will reappear.
2. Click on the Customer ID field in the Customer table and drag it on top of the Customer ID field in the accounts table. Upon releasing the mouse button, the edit relationship dialing box will appear.
3. Click on the create the relationship. The relationship screen will reappear with the new relationship in place with the symbol “1” (indicating “one”) and the infinity symbol (indicating “many”) on Customer ID of Customer and Accounts tables respectively.

Creating and running query

Queries are fundamental means of accessing and displaying data from tables. Queries can access a simple table or multiple table.

Examples are:

1. Which students are in JSS 2?
2. Which students are children of staff members?
3. Which parents does not live in Lagos?
4. Which student was born in 2003?
5. Which students are male?

Activity

Single table query

1. Click on create tab and select query wizard button. 3 selections will appear – Table/query, Available filed and Selected fields.
2. File down the query/table list and choose parents table notice that, available fields change to only those field in the parent table.
3. From the list of available field on the left, move the first name, last name, Address, city and state over to the selected fields on the right.

1. Click on the next button to move to the next and final step in the simple query wizard.
2. Set parent Address as the name of the query.
3. Choose open the query to view the information and click on finish button when the query executes only the selected fields will appear in all the rows.
4. Close the query by clicking (x) button on the right area of the query result.
5. The access main screen showing the query will appear.

Note that new query; “parent Address” appear under the parent table

Evaluation

1. Create and run a query
2. Create a single query
3. Setup parent address as the name of the query
4. State the steps to be followed in creating a table
5. How do you save a table