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.
To fulfill the demand for quickly locating and searching documents.
It is intelligent file search solution for home and business.