HW#2



Introduction to database using ACCESS

(PART TWO: Working with multiple tables)

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

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 |

Start

Open the database you created in tutorial 1, you will see following screen:

[pic]

Say no and select the open option on the next screen

You will see your database with tables and queries, if you saved any

Open table city and you will see “+” on the side of each record, implying this is part of one side of a relationship

If you click on + you will see associated records

Query 1: find the names of restaurants in Maryland

Note you need information from both tables state from city table and name of restaurant from restaurant table

Go to query and design mode

Select both table’s city and restaurant

Select state name and restaurant name

Since there is restriction on state name MD

[pic]

You will see the output:

[pic]

Query 2: Give the total of salaries by states:

Logic; we must group by state

Then sum them

Need both tables

[pic]

[pic]

Parameter Queries:

Useful when you want to use the SAME query for DIFFERENT criteria

Ex: if you want to find total for different state at different times

Put [which state?] in criteria

[pic]

When you run the query you will see the following:

[pic]

You enter the state and it will run the query for that state

Creating Reports:

Reports are used to create summary statistics

Exercise 1: Create report that lists resturants by city

Select report

Select report wizrd

Select table restarant

You will see the following window:

[pic]

[pic]

Select all fields

[pic]

Since we want to group by city select city as grouping option

Say Ok to next screen

Click NEXT, this will ask you if you want ascending or descending sort

Select ascendong on nmaes

Click next on options and finally finish. You will see following report:

[pic]

Exercise 2: Create a report that lists totals for each city

We already have a query that calculates total by state

Select report new, report wizard, instead of selecting a table this time select query which calculates totals by state, i.e.,

[pic]

Which gives the following output:

[pic]

Say it is query 5

Select this query in report wizard

[pic]

Select both state and total, keep selecting next until finish and you will see the following table:

[pic]

Exercise 3: Create a report by city & restaurant’s total

[pic]

Save as query 4

Select report wizrd and select query 4

Select name, city and total from query 4

Select city as grouping field

Select ascending on name

Select until finish and you will get sales by city

[pic]

Exercise 4: group by city and sub total

Easy method

After grouping by city selection, click on grouping option

On the next screen select summary option

Select sum

Select Ok until finish

You will see the desired report

Designer method:

Start with previous report

Open in design mode

Open report footer

In text box type: Total revenue

In the box next to it right click and in property box, select data tab

Type =sum([total]) and running sum over group

Go to format tab and select currency as format

[pic]

Next you need sub or control totals:

Click on sorting or grouping icon on the tool bar [pic]

Click on it ;

[pic]

Save it. Your report design should look like

[pic]

And run it. You should see the following report:

[pic]

................
................

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

Google Online Preview   Download