Using action queries and defining table relationships
This presentation is the property of its rightful owner.
Sponsored Links
1 / 27

Using Action Queries, and Defining Table Relationships PowerPoint PPT Presentation


  • 94 Views
  • Uploaded on
  • Presentation posted in: General

Using Action Queries, and Defining Table Relationships. Create action queries. You can create other types of queries that make changes to the data in the underlying table(s). Action queries can: Create a new table (make-table query) Add records to a table (append query)

Download Presentation

Using Action Queries, and Defining Table Relationships

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


Using action queries and defining table relationships

Using Action Queries, and Defining Table Relationships


Create action queries

Create action queries

  • You can create other types of queries that make changes to the data in the underlying table(s).

  • Action queries can:

    • Create a new table (make-table query)

    • Add records to a table (append query)

    • Delete records from a table (delete query)

    • Update the data in a table (update query)


Action query considerations

Action query considerations

  • When creating a query that actually makes changes to your database, take extreme care 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.


Use make table and append query queries

Use make-table and append query queries

  • To change a select query to a Make Table query, choose Make-Table Query from the Query Type button's list.

  • This will open the Make Table dialog box, in which you will specify the name of the new table.

  • To change a select query to an append query, choose Append Query from the Query type button's list.

  • In the Append Query dialog box, you will specify to which table you want to append records.

  • The Append To row will be displayed in the design grid indicating the fields that the query will append to the table.


The make table dialog box

The Make Table dialog box


The query window for an append query

The query window for an append query


Creating a delete query

Creating a Delete Query

  • A delete query is used to delete records you no longer need in your database.

  • To create a delete query, first create and run a select query in Design View, selecting the items you wish to delete.

  • If the select query displays only the records you wish to delete, click the list arrow for the Query Type button in design view and then click Delete Query.

  • Run the query to delete the records you previously selected.


Delete query dialog box

Delete Query Dialog Box


Creating an update query

Creating an Update Query

  • Create a select query with the necessary fields and selection criteria and click the Run button to preview the results.

  • Click the list arrow for the Query Type button and select Update Query.

  • Type the update values for the fields you need to update in their Update To text boxes.

  • Click the Run button on the Query Design toolbar.

  • Click the Yes button to confirm updating the records.


Update query dialog box

Update Query Dialog Box


One to many relationships

One-to-Many Relationships

  • A one-to-many (1:M) exists between two tables when each record in the primary table matches zero, one, or many records in the related table, and when each record in the related table matches at most one record in the primary table.

  • Referential integrity is the rule that requires each nonnull foreign key value to match a primary key value


A one to many relationship

A one-to-many relationship


A query datasheet for a one to many relationship

A query datasheet for a one-to-many relationship


Define many to many and one to one relationships between tables

Define many-to-many and one-to-one relationships between tables

  • 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 (M:N) relationship because a student can take many courses and a course can have many students


Define many to many and one to one relationships between tables1

Define many-to-many and one-to-one relationships between tables

  • 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.


A many to many relationship

A many-to-many relationship


A query datasheet for a many to many relationship

A query datasheet for a many-to-many relationship


The relationships window

The Relationships Window


View and create indexes to tables

View and create indexes to tables

  • When you establish a primary key for a table, you are actually creating an index for the table.

  • An index is a list that relates field values to the records that contain those field values.

  • The index makes it faster to search for records and to sort the records in a table.

  • You can view existing indexes in table design view by clicking on the Indexes button on the Table Design toolbar.


How indexing works

How indexing works

  • You can create an index on any field in a table.

    • However, each index actually is a new file and takes up space

  • When records are entered into a table they are assigned an internal record number in the order they were entered.

  • The index created a different ordering of the records but keeps track of the number originally assigned to the record.


An illustration of an index

An illustration of an index


The indexes window

The Indexes window


Join a table using a self join

Join a table using a self-join

  • There are three types of joins:

    • The inner join selects records from two tables only when the records have the same value in the common field that links the tables.

    • A left outer join selects all records from the first, or left, table and only those records from the second, or right, table that have matching common field values.

    • A right outer join selects all records from the second, or right, table and only those records from the first, or left, table that have matching common field values.

  • A self-join can be either an inner or outer join and is used to join a table to itself.


An example of an inner join

An example of an inner join


An example of a left outer join

An example of a left outer join


An example of a right outer join

An example of a right outer join


An example of a self join

An example of a self-join


  • Login