Exploring Microsoft Access 2016 Series Editor Mary Anne

Exploring Microsoft® Access® 2016 Series Editor Mary Anne Poatsy Cameron |Williams Series Created by Dr. Robert T. Grauer

Chapter 6 Action and Specialized Queries Moving Beyond the Select Query Copyright © 2017 Pearson Education, Inc.

Objectives • Determine When to Use an Action Query • Update Data with an Update Query • Add Records to a Table with an Append Query • Create a Table with a Make Table Query • Delete Records with a Delete Query • Summarize Data with a Crosstab Query • Find Duplicate Records with a Query • Find Unmatched Records with a Query Copyright © 2017 Pearson Education, Inc.

Objective 1: Determine When to Use an Action Query Skills: Back Up a Database Copyright © 2017 Pearson Education, Inc.

Determine when to Use an Action Query • Action query—changes data • Action query types: §Update—changes data automatically based on criteria §Append—adds records to a table from external sources §Make table—creates a new table from existing data §Delete query—removes records from a table based on criteria Copyright © 2017 Pearson Education, Inc.

Objective 2: Update Data with an Update Query Skills: Create an Update Query Test an Update Query Copyright © 2017 Pearson Education, Inc.

Update Data with an Update Query Click Update to today Missing Order. Date Copyright © 2017 Pearson Education, Inc.

Update Data with an Update Query Required field Copyright © 2017 Pearson Education, Inc.

Objective 3: Add Records to a Table with an Append Query Skills: Create an Append Query Copyright © 2017 Pearson Education, Inc.

Add Records to a Table with an Append Query Click Append Select destination table Show row Copyright © 2017 Pearson Education, Inc.

Add Records to a Table with an Append Query Append To row Field names in source and destination Copyright © 2017 Pearson Education, Inc.

Objective 4: Create a Table with a Make Table Query Skills: Create a Make Table Query Copyright © 2017 Pearson Education, Inc.

Create a Table with a Make Table Query Click Make Table Enter table name Copyright © 2017 Pearson Education, Inc.

Objective 5: Delete Records with a Delete Query Skills: Create a Delete Query Copyright © 2017 Pearson Education, Inc.

Delete Records with a Delete Query Click Delete Order Data table Delete row Copyright © 2017 Pearson Education, Inc.

Objective 6: Summarize Data with a Crosstab Query Skills: Use the Crosstab Query Wizard Modify a Crosstab Query Copyright © 2017 Pearson Education, Inc.

Summarize Data with a Crosstab Query Zak Behnke Copyright © 2017 Pearson Education, Inc.

Summarize Data with a Crosstab Query Copyright © 2017 Pearson Education, Inc.

Summarize Data with a Crosstab Query Copyright © 2017 Pearson Education, Inc.

Summarize Data with a Crosstab Query Copyright © 2017 Pearson Education, Inc.

Summarize Data with a Crosstab Query Copyright © 2017 Pearson Education, Inc.

Objective 7: Find Duplicate Records with a Query Skills: Create a Find Duplicate Records Query Using the Wizard Copyright © 2017 Pearson Education, Inc.

Find Duplicate Records with a Query Copyright © 2017 Pearson Education, Inc.

Find Duplicate Records with a Query Duplicate records Copyright © 2017 Pearson Education, Inc.

Objective 8: Find Unmatched Records with a Query Skills: Create a Find Unmatched Query Using the Wizard Copyright © 2017 Pearson Education, Inc.

Find Unmatched Records with a Query Copyright © 2017 Pearson Education, Inc.

Find Unmatched Records with a Query Copyright © 2017 Pearson Education, Inc.

Find Unmatched Records with a Query Copyright © 2017 Pearson Education, Inc.

Summary • Access provides action queries, which change the data • You should always backup a database before performing these queries • Access has specialized queries that help to analyze and improve the integrity of the data in a database Copyright © 2017 Pearson Education, Inc.

Questions ? Copyright © 2017 Pearson Education, Inc.

Copyright All rights reserved. No part of this publication may be reproduced, stored in a retrieval system, or transmitted, in any form or by any means, electronic, mechanical, photocopying, recording, or otherwise, without the prior written permission of the publisher. Printed in the United States of America. Copyright © 2017 Pearson Education, Inc.