More queries

1
More 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
General notes - tables
Design view for Tables is used to create
structure NOT enter data
3
4
General notes - tables

• Once you create a look up field, you MUST verify
  that it actually worked.
• Go back to data sheet view and see if you get a
  pull down menu. If you dont then it didnt
  work.
• Go do the Relationship window, right click and
  select Show All and ensure you edit the
  relationships
• Without proper relationships between your tables,
  your queries will return meaningless results.

4
5
General notes - queries

• Create all queries in Design view, unless it is
  an SQL query
• For calculated fields remember everything to the
  left of the is the field name and everything to
  the right is the calculation. If your
  calculations refer to fields in your table encase
  them in brackets. The field names must
  appear exactly as they are in your table. For
  the purpose of our class calculated fields CANNOT
  be in the criteria. For fields specified in
  everything to the left of the . is the table the
  field is coming from.
• When setting up criteria remember that if you
  dont have a criteria that means it will return
  ANY results. Remember criteria on the same line
  are evaluated with an AND statement. (That is
  they must ALL hold true.) Criteria on separate
  lines are evaluated with an OR statement. (That
  is any ONE of them can hold true.)
• Order of precedence when sorting is done LEFT to
  RIGHT

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
Calculated field with IIF function

• Access has many specialized functions, one such
  is the IIF function which is similar to IF in
  Excel.
• Syntax FieldName IIF(condition, what to do if
  true, what to do if false)
• Example use the IIF function to return Seating
  available if the Weekend PM Customer Count is
  less than Seating Capacity. Otherwise return
  Seating at capacity.
• Capacity IIf(Weekend PM Customer
  CountAvailable","Seating At Capacity")

14
15
Next class

• Wrapping up Access
• Answer questions on HW 5

15