Fixing Bad Queries And some good queries

1
Fixing Bad QueriesAnd some good queries

• Sean Banerjee
• sean.banerjee_at_mail.wvu.edu

1
1
2
Upcoming Deadlines

• Homework 4 is due on March 27
• DO NOT procrastinate on homework 4.

2
3
Queries

• Query 1
• List all the students, show the StudentID, Name,
  AdvisorName, Major. Sort by the name of the
  student
• Query 2
• List only the students that are English majors
  with more than 40 credit hours. Show the
  StudentID, Name, AdvisorName, Major, but do not
  show Credit Hours

3
4
Queries

• Query 3
• List only the Engineering majors who are John
  Smiths student and who have more than 100
  quality points. Show Name, AdvisorName, Major,
  Quality Points
• Query 4
• List only the In State students who are taking
  more than 12 hours next semester. Also show all
  out of state students regardless of the hours
  they are taking. Show Name, Major, Status, Hours
  next semester.

4
5
Queries

• Query 5
• Copy query 1, add in a calculated field GPA. It
  should say GPA Quality Points/Credit Hours.
  Format it as a number type with 2 decimal places
• A few points about calculated fields
• Anything to the left of the is the field name
• Include all fields within brackets
• Anything between MUST be something in your
  table and spelled exactly as they appear
• To format a calculated field or any field, right
  click and go to properties
• If the Decimal property doesnt show up, run the
  query first

5
6
Queries

• Query 6
• Create a query that will display each advisor
  (full name), the total number of students they
  have, the average credit hours for each student,
  and the average quality points for each student
• A query like this is called a Totals Query
• Add in the fields AdvisorName, StudentID, Credit
  Hours, Quality Points
• Click on the Totals icon in the Show/Hide box
• Notice how it creates a Total row
• We group each AdvisorName
• We count each StudentID to get the total number
  of students
• We average the Credit Hours
• We average the Quality Points

6
7
Queries

• Query 7
• Create a query that will display each advisor
  (full name) and the number of students they have.
• It only shows 3? How can we fix that? We can do
  this by editing the relationship in the Query
  Design view.
• Query 8
• Create a query that will display each student,
  their major, their advisor. Then, if their GPA
  is greater than 3.0 we want to print OK other
  wise we want to print Talk to advisor under a
  field called Information
• Information IIf(Students!Quality
  Points/Students!Credit Hoursgt3,"OK","Talk to
  advisor")

7
8
Bad Query 1

• We want to create a query to display all students
  and the Total hours they have taken, Total hours
  is calculated as Credit Hours Hours taken next
  semester
• What is wrong with the query?
• How did you figure out what was wrong?
• How can we fix it?

8
9
Fixing Bad Query 1

• When you attempt to run Bad Query 1 it comes up
  with a pop up box that says Enter Parameter
  Value CreditHours. This means the field
  CreditHours could not be found.
• Step 1 go into Design View. Check where the
  field CreditHours is used, we notice it is used
  in GPA.
• Step 2 verify that CreditHours is a field in our
  table, we note that it is not a field in our
  table. Our field is called Credit Hours (note
  the space), so change CreditHours to Credit
  Hours. At the same time we note that we do not
  have a field Hrs next semester. We need to
  change it to Hours next semester.
• Step 3 run the query, it runs fine after we
  changed the two incorrect fields.

9
10
Bad Query 2

• We want to create a query to display all
  students, their advisors, their major and isolate
  all English majors.
• What is wrong with the query?
• How did you figure out what was wrong?
• How can we fix it?

10
11
Fixing Bad Query 2

• When you run Bad Query 2 it returns 80 results,
  we know this cannot be possible as we only have
  20 records in our table.
• Step 1 go to Design View. Verify we have the
  correct tables in place. We note that we have a
  table Students_1 that is not connected to any
  other table, nor is it being used in our query.
• Step 2 remove Students_1 by right clicking on it
  and selecting Remove Table. Run the query, it
  runs fine now.
• Only include tables you need, too many tables or
  too few tables will yield incorrect results.

11
12
Bad Query 3

• We wish to create a query to display all
  students, their advisor and isolate all of Judy
  Framptons students.
• What is wrong with the query?
• How did you figure out what was wrong?
• How can we fix it?

12
13
Fixing Bad Query 3

• When we run Bad Query 3 we get 0 results. We
  know we should get results based on the data we
  have.
• Step 1 Go into Design View. Verify that your
  criteria is correct. Remember that the criteria
  has to be something in your table. Note the use
  of JF01 as the Criteria for AdvisorName. JF01
  is the AdvisorID for Judy Frampton. We need to
  include Judy Frampton as the criteria.
• Step 2 change JF01 to Judy Frampton, run the
  query and it yields the correct results.

13
14
Bad Query 4

• We wish to create a query to group each major and
  then find the average, minimum and maximum credit
  hours.
• What is wrong with the query?
• How did you figure out what was wrong?
• How can we fix it?

14
15
Fixing Bad Query 4

• When you run Bad Query 4 we get a Data Type
  Mismatch error. This in general means something
  in your criteria is incorrect
• Step 1 Go to Design View, note how we have
  Average and Min and Max as our criteria for
  Credit Hours. We know Credit Hours has a numeric
  value, so this is incorrect.
• Step 2 Delete Average and Min and Max from
  the criteria. Set the Totals Row for the Credit
  Hours to Avg for the first instance, Min for the
  second instance and Max for the third. Avg, Min,
  Max are part of the totals row NOT criteria.

15
16
Bad Query 5

• We wish to create a query that will display each
  student, their major, their credit hours and GPA.
  We want to only show students with GPA gt 2.8
• What is wrong with the query?
• How did you figure out what was wrong?
• How can we fix it?

16
17
Fixing Bad Query 5

• When we run Bad Query 5 we get a error message
  that states Enter Parameter Value GPA. We need
  to check if we have this field in our table.
• Step 1 Go to Design View, check where the field
  GPA is used. Note that we are trying to create a
  calculated field. Expr1 GPA is NOT correct.
  GPA is the name of the calculated field. Also
  note, we have the calculation as the criteria.
  That is also not correct.
• Step 2 delete Quality Points/Credit Hours
  from the criteria. Change Expr1 GPA to GPA
  Quality Points/Credit Hours. Run the query
  and we get the right results.
• Remember that in general calculated fields must
  be entered as part of the field, not a criteria.

17