CIF 102FIF102 Session 10 APPEND Action Queries

1
CIF 102/FIF102Session 10APPEND Action Queries
2
APPEND query

• Outcomes
• Able to use the QBE tool to build a query by
  typing SQL directly
• Able to use the QBE to write an Append action
  query

3
We will use the Rent database to illustrate an
APPEND query
4
Entity Relation Diagram (ERD) for rent database
5
The Rent Database

• Each property may have more than 1 rental
  agreement associated with it
• Each student may have more than one rental (e.g.
  in different years)
• The landlord buys another property. He needs to
  add this new record to the property table.

6
Adapting the query wizard to write SQL

• We are going to use 2 methods to achieve the same
  result-appending 2 rows of data to an existing
  table
• One way to do this is to write the SQL statement
  directly into the SQL view of a query in design
  view.
• We will first use the query wizard and adapt the
  QBE interface to enter data directly

7
Writing SQL directly

• Open a new query in design view
• Close the Show tables dialog box without adding
  any tables
• Go to View/SQL View

8

• Delete SELECT you will be left with a blank
  screen
• Type the following exactly into this view
• INSERT INTO Property (PropertyNo, Street, Town,
  TelephoneNo, No_of_Bedrooms)
• VALUES ('P04', '23, Percy St', 'Middlesbrough',
  '01642 897654' , 4)
• Close and save the query as Insertprop

9

• 7. Run the Insertprop query. You will receive two
  warnings
• The first warning

10

• Click Yes when you receive the warning notice
• Click yes at the warning that to proceed will
  permanently modify the data
• 2nd warning

11
Appended data

• 10. Check that the new property P04 has been
  added to the Property table as follows

12
2nd method Using the wizard to append data

• We can use an Append action query to insert
  records
• First we must create a new table called
  new_property and add the same fields as Property
  and add 2 new sets of data
• Data from new_property will be appended to
  Property table

13
Append action query using the wizard

• Create a new query in design view
• Select new_property from Show table
• Add it to the QBE
• Select all the fields from new_property
• Go to the menu item Query
• Select Append (green cross)
• Type in Property in the dialog box

14
Type property into the Table name

• Click OK (for the current database)

• .

Notice that Append to has been added to the left
hand labels
15
Running theb query

• Save the query
• Run and check the property table to see if the
  fields have been added

16
Tutorial

• This is extension work-only the first part of the
  slide material is in the tutorial booklet
• You can try creating the new_property table,
  entering your own data and creating the append
  action query if you want to try it out
• See section 7.4 Connolly and Begg Database
  Systems for more examples.