Introduction to database using ACCESS (2007)

What is a database?

A collection of related files

We will use following tables to create and explore database in MS ACCESS

Assume two tables:

CITY table with attributes:

Ø  City name (PK)

Ø  City population

Ø  State

Restaurants table with following attributes:

Ø  Restaurant name (PK)

Ø  Salary

Ø  Number of employees

Ø  city (FK)

Assume following data for CITY table:

City Name / City population / State
Baltimore / 2800 / MD
Washington / 1200 / DC
Annapolis / 500 / MD
Arlington / 600 / VA
Las Vegas / 2200 / NV

Assume following data for Restaurant table:

Restaurant name / salary / No of employees / city
kingrus / 100 / 50 / washington
Thai King / 200 / 20 / baltimore
Phillips / 300 / 10 / baltimore
Steak house / 500 / 50 / arlington
Cho mein / 100 / 20 / arlington
Crab house / 50 / 10 / baltimore

Step 1:

CREATING TABLES:

Open ACCESS

Select new

Name the database

You will see following screen:

Give your database a name (say trial) and then select create.

Then go to CREATE option in menu

and select table design, you will see a worksheet open up

Start entering field names and their data types

To declare PK

Click on the row that you want to declare as PK

Select key symbol from tool bar

Close the table, it will ask you for a name

Save it under city

Now you have a table called CITY

Repeat the same process for Restaurant table

Step 2.

Ø  Populating data in tables

Ø  Select a table to populate from the left menu

Ø  Start entering data in that spreadsheet

Once data is entered, your tables are ready.

To Declare a FK:

go to data base

tool menu,

you will see the following icon:

Click on relation icon in tool bar

Select both tables

Close the pop up window

Drag the field from one table to another table where you want the link

A new window will open up

Say yes to create the link

You will see following screen

It will ask you if you want to make changes to relationship

Answer yes. It will create the above relationship for your database.

Step 4: Creating a simple query

Working with one table

Query1: get the contents of table city

go to Create option in menu

select Query design mode, following window will open up

Select

·  Create

·  query design

A grid will open

Select CITY table from the available tables

Select fields in different columns and you will see the following screen:

mark (Select) all boxes that you want in your output

using run the query

your output will look like

Close the window, it will ask for a name,

Save it as query1 (or any name you like)

If you only want a see few fields,

QUERY2: Get the cityname and population in ascending order of the city

Click on query Design

Select CITY table

Select the fields from drop down menu

Select ascending from sort row and

Execute the query using !

Using view mode you can make changes to your query (in case there is an error)

Searching for specific records:

Query 3:: you only want names of cities that have population of >2000

In Criteria row put >2000 under population

You will see the following output:

Calculated fields:

Query 4: find the revenue for restaurants

Select a new query in design mode

Select restaurant table

Select all the fields from the table

We will create calculations in a new field

General format:

Field name : [field1 operation field2]

Revenue will be calculated multiplying salary by number of employees

Create a new field and type

Method 1 (expression builder)

select Restaurant table, select all fields, right click on an empty cell and select build

a new window will open, type total: and then select Restaurant table from table folder

select salary, select * and select no of employees from the list

remove *expr* from the expression and select Ok

run the query and you will see:

note the query gives you the total

Note the last column, it has total revenue,

To format the last column,

go to design mode

right click on the new column total revenue and select property option

select format and

then currency option

close the property window

and execute the query

Finding Totals:

Query 6: Find the overall total by city

Add the city field to the grid

Click on summation icon (Totals) in tool bar

A new row total will open up, which initially will say group by in all columns

We need totals by CITY so we need to group by city only.

Under the new column,

select sum where it says Group by from drop down menu

Click Group By under total and select sum from drop down menu

Deselect all fields, except city and total rev

Change the format in total as Currency (see pervious query)

Output will look like:

On the left menu and on the top-- it shows you your tables and queries. You can view any table or query by double clicking on them.

NEXT WORKING WITH TWO TABLES!!!!

2