Ms Access 2007: Step by Step

By Asim Abbasi

Everything in this book is covered in a step by step fashion by first building reader's concept then taking him/her to the steps of design implementation. For advanced MS Access users, please check our recommended book: Oracle Data Manipulation, Knowledge Discovery & Reporting Using MS Access

• Language: English
• Publisher: Lulu.com
• Release date: Mar 29, 2011
• ISBN: 9781257198191

Book preview

Index

Chapter 1

Overview

RDBMS Basics

Basically RDBMS (Relational Database Management System) is nothing but a standard. For many years database industry was not having any standard for storing and retrieving information. As a result of this standard we came up the language called SQL or Structured Query Language. We use SQL to communicate with RDBMS databases including but not limited to MS Access, Oracle, MS SQL Server etc. MS Access databases used to have file extension as .mdb but in 2007 release the default extension is .accdb.

Using MS Access GUI (Graphical User Interface) we can now build very complex SQL quires very fast visually just by Click, Drag & Drop operation. While we are performing such tasks MS Access writes the SQL code for us in the background.

According to the RDBMS standard, a database should be comprised of tables and data inside each of these tables should reside in the form horizontal rows called ‘Records’ and vertical columns called ‘Fields’.

e9781257198191_i0002.jpg

In the above screenshot ‘Last Name’, ‘First Name’ and ‘Hire Date’ are the fields of this table. If you move horizontally, you see records. We can say that the table has 3 Fields and 6 records.

The standard also says that the each table may have relationship with one or more tables. Relationships in between the tables are developed using Primary/Foreign Key concept which we will discuss later in this book. The sole purpose of developing such relationship amongst tables is to reduce the redundancy of data inside the database. It also helps in increasing the data integrity of the system.

Where MS Access Stands?

You might have heard about different databases names like MS Access, Oracle, SQL Server etc. and must be thinking which one to select for learning or implementation purpose. Nearly all the good, famous databases are RDBMS including but not limited to Oracle, DB2, MS SQL Server, MS Access, MySQL etc.

Small companies :: MS Access

Mid-size organizations :: MS SQL Server

Large Enterprises :: Oracle

Since all these databases mentioned above follow RDBMS standard, learning and mastering one will definitely reduce the time to master the other.

Having a Brief SQL Flavor

SQL for MS Access differ from SQL for Oracle very minutely. All companies having RDBMS database as product have to fulfill the basic RDBMS commands. SQL is composed of bunch of commands rather complete English words and I sometimes say to my students that anyone knowing just English can imagine the outcome of SQL commands e.g.

SELECT ename, salary

FROM emp;

SELECT * FROM emp;

SELECT ename, salary FROM emp;

SELECT ename, salary FROM emp WHERE salary > 2000;

SELECT ename, salary FROM emp WHERE salary > 2000 ORDER BY ename;

Here ename and salary are the column names (a.k.a. field names) of emp table. This is one of the basic forms of SELECT statement in SQL. SELECT statements are used to retrieve the information from the database. If you want to limit the number of records you can write in the following manner.

SELECT ename, salary

FROM emp

WHERE salary < 30000;

Using the WHERE clause can help in filtering records or in other words limiting the number of records in the output of a query. There is one more very interesting clause in the SELECT statement that is the ORDER BY clause. Using this clause you can have the data arranged in order e.g. alphabetically ascending or descending etc.

SELECT ename, salary

FROM emp

WHERE salary < 30000

ORDER BY ename;

If you want the order to be descending then just write DESC at the end of the statement as shown below.

SELECT ename, salary

FROM emp

WHERE salary < 30000

ORDER BY ename DESC;

Other DML Statements:

UPDATE statement is used to edit the information already existing in the table. If you want to insert a new record in the table then we use the INSERT statement and if you want to delete any record or more than one record we use the DELETE statement.

UPDATE Statement

UPDATE supplier

SET name =‘HP’

WHERE name =‘IBM’;

INSERT Statement

INSERT INTO supplier

(supplier_id, supplier_name)

VALUES

(24553, ‘IBM’);

DELETE Statement

DELETE FROM supplier

WHERE supplier name = ‘IBM’;

Example:

UPDATE emp

SET comm = NULL

WHERE job = ‘TRAINEE’;

In this example, emp table gets updated and the comm field will be having NULL values where job field values are equal to ‘TRAINEE’.

Example:

INSERT INTO dept

VALUES (50, ‘PRODUCTION’, ‘SAN

FRANCISCO’);

In this example, one record gets inserted (appended) into the dept table. Remember that this command will work only if the order of the fields’ data-type matches with the order of data-type of the values you are going to insert. e.g. if the first field of dept table is of integer data-type but instead of inserting integer (50) you insert date then the command will result in an error.

Another version,

INSERT INTO emp (empno, ename, job, sal,

comm, deptno)

VALUES (7890, ‘JINKS’, ‘CLERK’, 1.2E3, NULL,

40);

In this particular example, the order of data-types of values and explicit table fields names should match e.g. if you have deptno (Integer data-type) at the last place then in the values you should have some integer data-type value at the last place otherwise the SQL statement will result in error.

Example:

DELETE FROM emp

WHERE JOB = ‘SALESMAN’ AND COMM <

100;

Here records from emp table gets deleted but only those where JOB field value is equal to ‘SALES MAN’ and COMM field value is less than 100.

7 Areas of Action

Once you run the MS Access 2007 software residing under and menu after installation, you will notice that the software has seven objects or seven areas of operation.

e9781257198191_i0003.jpg