Tutorial 8 Action Queries

1
Tutorial 8Action Queries

• Review Assignmentpp 8.55 to 8.56

2
Action Query Considerations

• Action queries make changes to your database.
  Thus, extreme caution is needed to insure that
  you do not create a query that performs
  unintended actions.
• It is a good idea to create your action query
  first as a select query.
• You can then view the results and determine if
  these are the records you want to alter.
• When you are sure the query will be applied to
  the records you really want, you can then run the
  query as an action query.

3
Data Import Exercise

• Purpose
• Add two additional tables to database
• Demonstrate how action queries can be used to
  split imported tables into normalized tables

4
Data Import Exercise contd

• Import the RecruiterbyNation.xls excel
  spreadsheet into the Recruits.mdb database as a
  table
• Select the New option on the Table tab, then
  select the Import option from the list
• Within the Import dialog window
• Change the Files of Type selector box to
  Microsoft Excel
• Navigate to the folder where you saved the
  RecruiterbyNation.xls file (in the Resources
  section above), and select the spreadsheet.

5
Data Import Exercise contd

• Within the Import Spreadsheet Wizard
• There is only one worksheet names Territory.
• The first row of the worksheet does contain the
  column headings, so check the box.
• Import into a new table.
• Import both fields without any changes to the
  Field Options.
• Select the no primary key option.
• Import into a table named RecruiterbyNation

6
Data Import Exercise contd

• Create a Make Table Query to create a
  tblTerritory table from the RecruiterbyNation
  table
• Create a new query in the Design view, add the
  RecruiterbyNation table.
• Add both the SSN and Nation fields to the query.
• Switch to the Datasheet View mode -- there should
  be 25 records in the table.
• Switch back to the Design view, and select the
  "Make Table" option using the Query Type icon. 
  Name the new table tblTerritory.
• Run the query using the run icon
• Save the query as qryMakeTerritory

7
Data Import Exercise contd

• Create a Make Table Query to create a tblCountry
  table from the RecruiterbyNation table
• Create a new query in the Design view, add the
  RecruiterbyNation table.
• Select the only the Nation field.
• Select the Totals icon and use the "Group-by"
  option (use view mode to confirm there are only
  14 records in the table).
• Select the "Make Table" option using the Query
  Type icon.
• Name the table tblCountry -- it should contain 14
  records.
• Save the query as qryMakeCountry

8
Data Import Exercise contd

• Modify design of tblTerritory table
• SSN field  Set the field size to 30.  Set the
  input mask for the SSN format (000\-00\-0000). 
  Since this field will be part of a concatenated
  primary key, it should be indexed with "Yes
  (Duplicates ok)" option.
• Nation field  should be a 30 character text
  field.  Since it is part of a concatenated
  primary key, it should be indexed with "Yes
  (Duplicates ok)" option.
• Select both SSN and Nation as the primary key.
• You should receive a warning that data may be
  lost.  This results from changing the field
  sizes.

9
Data Import Exercise contd

• Modify design of tblCountry table
• Nation field  should be a 30 character text
  field and set as the primary key.
• You should receive a warning that data may be
  lost.  This results from changing the field size

10
Review AssignmentStep 3

• Text defines many-to-many relationships
• A many-to-many relationship exists between tables
  when the tables involved have multiple matches in
  each of the tables.
• For example, if you have a table containing
  student data and another table containing course
  data, you could say that this is a (MN)
  relationship because a student can take many
  courses and a course can have many students
• Whenever there is a many-to-many relationship,
  you must provide a third table that will link
  the two tables together in a one-to-many
  relationship.
• You can also have a one-to-one relationship
  between tables, which means that there is exactly
  one record in the primary table that matches
  exactly one record in the related table.

11
Review AssignmentStep 3 (contd)

• The m-m relationship is implemented as two
  one-to-many relationships
• Between tblRecruiter and tblTerritory
• Between tblTerritory and tblCountry.

12
Review AssignmentStep 4

• A crosstab query performs aggregate function
  (arithmetic operations) calculations on the
  values of one database field and displays the
  results in a spreadsheet format.
• There are a variety of functions that can be used
  in crosstab queries including average, count,
  first field value, last field value, etc.

13
Review AssignmentStep 4 (contd)

• Crosstab Query Wizard
• First select the tblStudent table upon which you
  want to create the crosstab query.
• Second select the Nation field that will be used
  for the row headings (note could have multiple
  row fields)
• Third select the Gender field that will be used
  for the column headings.
• Fourth select the StudentID field as the basis
  for calculation, and count as the type of
  calculation.
• Save as tblStudent_Crosstab

14
Review AssignmentStep 4 (contd)

• Open tblStudent_Crosstab in design view
• Change the "Total of StudentID" label to "Total"
• View the query and resize the fields.
• Save the query

15
Review AssignmentStep 5

• Create a find duplicates query
• A find duplicates query is a select query that
  locates duplicate records in a table or query.
• The Find Duplicates Wizard will help you make
  selections about the field(s) for which you want
  to find duplicates.
• Use this type of query to locate duplicates that
  create potential problems in your database, such
  as assigning two different product numbers to the
  same product.

16
Review AssignmentStep 5 (contd)

• Use the Find Duplicates wizard
• Table tblStudent
• Duplicate fields City and Nation
• Additional fields FirstName and LastName
• Save Find Duplicate Student Locations

17
Review AssignmentStep 7

• Make Table Query
• Create a new query in the Design view, add the
  tblRecruiter and tblTerritory tables to the
  query.
• Add all fields from tblRecruiter and the Nation
  field from tblTerritory.
• Switch to the Datasheet View -- confirm there are
  25 records in the table.
• Switch back to the Design view, and select the
  "Make Table" option using the Query Type icon . 
  Name the new table tblRecruiterTerritory.
• Run the query using the run icon.
• Save the query as qryMakeRecruiterTerritory.

18
Review AssignmentStep 8

• Update Ireland to France
• Create a new query in the Design view, add the
  tblRecruiterTerritory table to the query.
• Add the Nation field to the design grid, and
  specify the criteria as "Ireland".  Check the
  Datasheet view -- there should be 4 records in
  the query.
• Select the "Update Query" option using the Query
  Type icon .
• Set the Update To field to the value of "France".
  
• Run the query
• Save the query as qryUpdateRecruiterTerritory

19
Review AssignmentStep 9

• Append Ireland records back into query
• Note The query structure must be similar to the
  structure of the table to which the records are
  appended
• Create a new query
• Add the tblRecruiter and tblTerritory tables to
  the query.
• Add all fields from tblRecruiter and the Nation
  field from tblTerritory.

20
Review AssignmentStep 9 (contd)

• Append query contd
• Add the criteria of "Ireland" for the Nation
  field.  Open the query in the datasheet view --
  there should be 4 records.
• Select the "Append Query" option using the Query
  Type icon.
• Select the tblRecruiterTerritory table as the
  Append table.
• Note that an Append To row appears in the design
  grid with the corresponding fields from the
  tblRecruiterTerritory table.

21
Review AssignmentStep 9 (contd)

• Append query contd
• Run the query using the run icon
• Save the query as qryAppendRecruiterTerritory

22
Review AssignmentStep 10

• Delete records where bonusquota is 40 or 50
• Create a new query in the Design view, add the
  tblRecruiterTerritory table to the query.
• Add the BonusQuota field to the design grid, and
  specify the criteria as 40 Or 50.  Check the
  Datasheet view -- there should be 10 records in
  the query.
• Select the "Delete Query" option using the Query
  Type icon
• Run the query using the run icon
• Save the query as qryDeleteRecruiterTerritory