Exploring Microsoft Office 2016 Series Editor Mary Anne

Exploring Microsoft® Office 2016 Series Editor Mary Anne Poatsy Mulbery|Krebs|Hogan|Cameron |Davidson|Lau|Lawson|Williams Series Created by Dr. Robert T. Grauer

Exploring Access 2016 Chapter 2 Tables and Queries in Relational Databases Designing Databases and Extracting Data Copyright © 2017 Pearson Education, Inc.

Objectives • Design a Table • Create and Modify Tables and Work with Data • Share Data • Establish Table Relationships • Create a Single-Table Query • Use the Query Wizard Copyright © 2017 Pearson Education, Inc.

Objectives • Specify Query Criteria for Different Data Types • Understand Query Sort Order • Run, Copy, and Modify a Query • Create a Multitable Query • Modify a Multitable Query Copyright © 2017 Pearson Education, Inc.

Objective 1: Design a Table Skills: Design a Table Copyright © 2017 Pearson Education, Inc.

Design a Table Rough draft of a table showing relationships Copyright © 2017 Pearson Education, Inc.

Objective 2: Create and Modify Tables and Work with Data Skills: Create a Table in Datasheet View Delete a Field Set a Table’s Primary Key Work with Field Properties Create a New Field in Design View Modify the Table in Datasheet View Copyright © 2017 Pearson Education, Inc.

Create and Modify Tables and Work with Data Table name Data typed directly into the table Copyright © 2017 Pearson Education, Inc.

Create and Modify Tables and Work with Data Delete Rows Copyright © 2017 Pearson Education, Inc.

Create and Modify Tables and Work with Data Primary key Copyright © 2017 Pearson Education, Inc.

Create and Modify Tables and Work with Data Field properties Copyright © 2017 Pearson Education, Inc.

Create and Modify Tables and Work with Data New field Copyright © 2017 Pearson Education, Inc.

Create and Modify Tables and Work with Data New blank record New record Copyright © 2017 Pearson Education, Inc.

Objective 3: Share Data Skills: Import Excel Data Import Data from an Access Database Modify an Imported Table’s Design Add Data to an Imported Table Copyright © 2017 Pearson Education, Inc.

Share Data Wizard used to import Excel data Copyright © 2017 Pearson Education, Inc.

Share Data Import & Link group Data from Access Copyright © 2017 Pearson Education, Inc.

Share Data Modified field name Copyright © 2017 Pearson Education, Inc.

Share Data New data added to imported table Copyright © 2017 Pearson Education, Inc.

Objective 4: Establish Table Relationships Skills: Establish Table Relationships Enforce Referential Integrity Copyright © 2017 Pearson Education, Inc.

Establish Table Relationships Primary key Join line Foreign key Copyright © 2017 Pearson Education, Inc.

Establish Table Relationships Referential integrity violation error message Copyright © 2017 Pearson Education, Inc.

Objective 5: Create a Single-Table Query Skills: Create a Single-Table Query Copyright © 2017 Pearson Education, Inc.

Create a Single-Table Query Tools Table Field criterion Copyright © 2017 Pearson Education, Inc.

Objective 6: Use the Query Wizard Skills: Use the Query Wizard Copyright © 2017 Pearson Education, Inc.

Use the Query Wizard Copyright © 2017 Pearson Education, Inc.

Use the Query Wizard Copyright © 2017 Pearson Education, Inc.

Objective 7: Specify Query Criteria for Different Data Types Skills: Specify Query Criteria Copyright © 2017 Pearson Education, Inc.

Specify Query Criteria for Different Data Types • Query criterion § Delimiters • Wildcards • Comparison operators § =, <> § >, < § >=, <= • Null • AND, OR, NOT conditions Copyright © 2017 Pearson Education, Inc.

Objective 8: Understand Query Sort Order Skills: Specify Query Sort Order Copyright © 2017 Pearson Education, Inc.

Understand Query Sort Order Sort by ascending order Copyright © 2017 Pearson Education, Inc.

Objective 9: Run, Copy, and Modify a Query Skills: Run, Copy, and Modify a Query Change Query Data Copyright © 2017 Pearson Education, Inc.

Run, Copy, and Modify a Query Saving a copy of a query Copyright © 2017 Pearson Education, Inc.

Run, Copy, and Modify a Query Account balance changed New record added Copyright © 2017 Pearson Education, Inc.

Objective 10: Create a Multitable Query Skills: Additional Tables to a Query Create a Multitable Query Copyright © 2017 Pearson Education, Inc.

Create a Multitable Query Join line for added table Copyright © 2017 Pearson Education, Inc.

Create a Multitable Query Related tables Copyright © 2017 Pearson Education, Inc.

Objective 11: Modify a Multitable Query Skills: Modify a Multitable Query Summarize Data Using a Multitable Query Copyright © 2017 Pearson Education, Inc.

Modify a Multitable Query Added criterion Modified criterion Copyright © 2017 Pearson Education, Inc.

Modify a Multitable Query Datasheet results with the count of accounts per customer Copyright © 2017 Pearson Education, Inc.

Summary • Design a table § Necessary data § For now and the future § Primary keys § Common fields § Accommodate calculations • Share data and establish table relationships • Single-table queries • Query criteria and sort order • Multitable queries 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.