Access in the real world More queries

1
Access in the real worldMore queries

• Sean Banerjee
• sean.banerjee_at_mail.wvu.edu

1
1
2
Upcoming Deadlines

• Homework 4 is due on March 27
• MyITLab Lesson 4 is due on March 30
• Homework 5 is due on April 3
• Exam 2 Section 26 April 6
• Exam 2 Section 25 April 8
• MyITLab Lesson 5 is due on April 13
• MyITLab Lesson 6 is due on April 20
• Homework 6 is due on April 24
• Final Exam Section 25 on Tuesday, May 5 at 800
  AM
• Final Exam Section 26 on Friday, May 8 at 300
  PM

2
3
Access in the real world

• Access databases are generally not the preferred
  database of choice in industry, but you can use
  it to perform data management, inventory control,
  etc.
• Most websites run databases in the backend, it is
  easier to update a database than it is to update
  static pages.
• Web programming languages (eg ASP) can be used
  to connect to Access databases.

3
4
CCMC Enterprises example

• CCMC Enterprises uses a MS Access database to
  manage appointments. Following fields are used,
  along with associated data types

4
5
CCMC Enterprises example

• Queries are used to generate the following
• Number of appointments per day (displaying all
  pertinent information)
• Number of appointments per location (displaying
  all pertinent information)
• Number of appointments per customer
• A table is also generated that acts as a mailing
  list, this stores the persons name, email, phone
  number, last appointment date and total number of
  appointments.
• This brings us to a new set of queries Make
  Table query, Delete Query and Update Query

5
6
Queries using restaurants.accdb

• Make Table queries creates a table based on
  your query.
• Create a new query in Query Design, include
  States and Restaurants.
• Include the fields States, Restaurants, Day of
  week and Seating Capacity
• Set the criteria for Day of week to Friday and
  Seating Capacity to 50
• Click on Make Table
• Save it as All Restaurants Friday gr 50 and
  save it in the same database
• Run the query
• We essentially created a table that stored all
  Restaurants where the seating capacity was
  greater than 50 on Friday.

6
7
Queries using restaurants.accdb

• Delete queries deletes records from the
  original table based on your query.
• Create a new query in Query Design, include
  Restaurants.
• Include the fields Restaurants, and Seating
  Capacity
• Set the criteria for Restaurant to Garfields and
  Seating Capacity to 75
• Click on Delete
• Run the query
• NOTE be careful when using Delete queries, its
  a permanent change. In this case we are deleting
  all entries from Restaurants where the Restaurant
  is Garfields and the Seating Capacity is greater
  than 75.

7
8
Queries using restaurants.accdb

• Update queries we can use this to auto update
  entries in our table, instead of manually
  changing them
• Create a new query in Query Design, include
  Restaurants.
• Include the fields Restaurants, and Seating
  Capacity
• Click on Update
• Set the criteria for Restaurant to AppleBees and
  Seating Capacity to 55 and Update To under
  Seating Capacity to 70
• Run the query
• The query basically updates all entries in our
  table Restaurants where the restaurant is
  AppleBees and the seating capacity is 55 by
  updating the value of the seating capacity to 70.

8
9
Introducing SQL

• SQL Structured Query Language
• It is the language used to create queries,
  using design view/wizard is OK but in the real
  world developers tend to use the language
  itself to build queries
• Example
• Create a query that will display all restaurants
  where the Seating Capacity is greater than 50.
  Show the name of the restaurant and the Seating
  Capacity

9
10
Query in Design View

• If we were to create the query in Design view we
  would perform the following steps
• Go to create
• Click on Query Design
• Add in the Restaurants table
• Add in the fields Restaurants and Seating
  Capacity
• Set a criteria 50 for Seating Capacity
• So what does this look like in SQL?

10
11
Query in SQL

• SELECT Restaurants.Restaurant, Restaurants.Seatin
  g Capacity
• FROM Restaurants
• WHERE (((Restaurants.Seating Capacity)50))
• SQL is very verbose, read it left to right
• SELECT the fields Restaurants and Seating
  Capacity from the Restaurants table, return
  results WHERE Seating Capacity is 50

11
12
SQL Queries

• To create queries in SQL just click on Create
  then Query Design and close out the Show Table
  box. Change the View to SQL view and copy and
  paste in the SQL code
• Enter the following SQL query
• SELECT States.State, Restaurants.Restaurant,
  Avg(Week Day AM Customer Count/Seating
  Capacity) AS Percentage Week Day AM FROM
  States INNER JOIN Restaurants ON
  States.Abbreviation Restaurants.StateAbbr GROUP
  BY States.State, Restaurants.Restaurant ORDER BY
  States.State, Avg(Week Day AM Customer
  Count/Seating Capacity)
• What does this query return? It shows us the
  percentage usage for each restaurant in each
  state.

12
13
SQL Queries

• To create queries in SQL just click on Create
  then Query Design and close out the Show Table
  box. Change the View to SQL view and copy and
  paste in the SQL code
• Enter the following SQL query
• SELECT States.State, Restaurants.Restaurant,
  Restaurants.Day of week, Restaurants.Seating
  Capacity, Restaurants!Week Day AM Customer
  CountRestaurants!Week Day PM Customer Count
  AS Week Day Count FROM States INNER JOIN
  Restaurants ON States.Abbreviation
  Restaurants.StateAbbr WHERE (((Restaurants.Day
  of week)"Monday") AND ((Restaurants.Seating
  Capacity)50)) OR (((Restaurants.Day of
  week)"Tuesday")) ORDER BY States.State,
  Restaurants.Restaurant
• What does this query return? It shows us all
  restaurants where the seating capacity was 50 on
  Monday or all restaurants on Tuesday regardless
  of seating capacity.

13
14
Next class

• Wrapping up Access
• Answer questions on HW 4 5

14