TeachUcomp, Inc.

P: 877-925-8080

  • HOME
  • VIDEO COURSES
    • All-Access Subscriptions
    • Business Licensing
    • Course List
      • Adobe
        • Acrobat
        • Lightroom Classic
        • Photoshop
        • Photoshop Elements
      • Crystal Reports
      • Employment Skills
        • Interview Skills
        • Resume Skills
      • For Lawyers
        • Excel for Lawyers
        • Outlook for Lawyers
        • QuickBooks for Lawyers
        • Word for Lawyers
      • HTML
      • JavaScript
      • Microsoft Office
        • Access
        • Excel
        • Microsoft Office Suite
        • OneNote
        • Outlook
        • Outlook on the Web
        • PowerPoint
        • Publisher
        • Word
      • Microsoft Project
      • Microsoft Teams
      • QuickBooks Online
      • QuickBooks Pro
      • Sage
        • Peachtree
        • Sage 50
      • SQL
      • Windows
    • Video Course Options
  • SUBSCRIPTIONS
  • MANUALS
    • Adobe
      • Acrobat
      • Lightroom Classic
      • Photoshop
      • Photoshop Elements
    • Crystal Reports
    • For Lawyers
      • Excel for Lawyers
      • Outlook for Lawyers
      • QuickBooks for Lawyers
      • Word for Lawyers
    • HTML
    • JavaScript
    • Microsoft Office
      • Access
      • Excel
      • OneNote
      • Outlook
      • Outlook on the Web
      • PowerPoint
      • Publisher
      • Word
    • Microsoft Project
    • Microsoft Teams
    • QuickBooks Online
    • QuickBooks Pro
    • Sage
      • Peachtree
      • Sage 50
    • SQL
    • Windows
  • REFERENCE CARDS
    • Adobe
      • Acrobat DC
      • Lightroom Classic CC 2018
      • Photoshop 2021
      • Photoshop 2020
      • Photoshop CC 2018
      • Photoshop Elements 2022
      • Photoshop Elements 2021
      • Photoshop Elements 2020
      • Photoshop Elements 2019
      • Photoshop Elements 2018
      • Photoshop Elements 15
    • Google
      • Google Classroom for Teachers
      • Google Docs
      • Google Drive
      • Google Forms
      • Google Sheets
      • Google Slides
    • Microsoft Office
      • Microsoft Office 365
        • Access for Office 365
        • Excel for Office 365
        • Microsoft Office 365
        • Outlook for Office 365
        • PowerPoint for Office 365
        • Publisher for Office 365
        • Word for Office 365
      • Microsoft Office 2021
        • Access 2021
        • Excel 2021
        • Microsoft Office 2021
        • Outlook 2021
        • PowerPoint 2021
        • Publisher 2021
        • Word 2021
      • Microsoft Office 2019
        • Access 2019
        • Excel 2019
        • Microsoft Office 2019
        • Outlook 2019
        • PowerPoint 2019
        • Publisher 2019
        • Word 2019
      • Microsoft Office 2016
        • Access 2016
        • Excel 2016
        • Microsoft Office 2016
        • OneNote 2016
        • Outlook 2016
        • PowerPoint 2016
        • Publisher 2016
        • Word 2016
      • Microsoft Office 2013
        • Excel 2013
        • Microsoft Office 2013
        • OneNote 2013
        • Outlook 2013
        • PowerPoint 2013
        • Word 2013
      • Microsoft Office for iPad
        • Microsoft Office for iPad
    • Microsoft Teams
      • Microsoft Teams
    • QuickBooks Online
      • QuickBooks Online
    • QuickBooks Pro
      • QuickBooks Pro 2022
      • QuickBooks Pro 2021
      • QuickBooks Pro 2020
      • QuickBooks Pro 2019
      • QuickBooks Pro 2018
      • QuickBooks Pro 2017
      • QuickBooks Pro 2016
      • QuickBooks Pro 2015
      • QuickBooks Pro 2014
    • Sage
      • Sage 50 2019
      • Sage 50 2018
    • SQL
      • SQL
    • Windows
      • Windows 11
      • Windows 10
  • STICKERS
    • Chrome OS
      • Chrome OS for Chromebooks
    • Mac OS
      • Mac OS Monterey-Mojave
    • Microsoft Office
      • Excel and Word 2021-2016 and 365
    • QuickBooks Desktop
      • QuickBooks Desktop 2022-2015
    • Windows
      • Windows 11 and 10
  • SUPPORT
    • Company
      • About Us
      • Blog
      • Contact Us
      • Our History
      • Our Philosophy
        • Customer Service
        • Mission
      • Policies
      • Testimonials
    • Contact Us
    • FAQ
    • Help
    • Lost Password
    • Register a Product
  • LOGIN
    • Manage My Account
    • Member Login
    • My Courses
  • CART
Shop Now

Advanced Queries in Microsoft Access 2010

by Keeley Byrnes / Tuesday, February 18 2014 / Published in Access 2010, Latest

Make Table Queries

Have you ever run a query and wished that you could save the result set of the query as a permanent table? In Access, that is exactly what the “Make Table” queries do. A “Make Table” query creates a new table as the output of a query, instead of simply displaying a query result set. This new permanent table can be useful in a variety of ways: you could use it as a basis for other queries; it could serve to backup important data; or it could show selected information from multiple underlying tables in a single table which you could then export to Microsoft Excel for spreadsheet-style analysis. Since the basis of the make table query is a standard ‘select’ query-type, you can restrict the columns and rows that are displayed in the new table by picking just the fields you wish to view and applying query criteria.

The fields that comprise the new table created from the make table query’s result set retain the data type and field size properties that were assigned to the original fields. However, other properties that you may have set for the base table’s fields will not be inherited by the new fields. So, you may need to re-enter validation rules, and other properties in the new table, if needed.

To create a make table query, first create a standard query in design view. Select only the fields that you want to include in your new table, and use criteria to restrict the rows selected, if needed. Then click the “Make Table” button in the “Query Type” group of the “Design” tab in the “Query Tools” contextual tab in the Ribbon to change the query type.

In the “Make Table” dialog box that appears, you can type a name for the new table that will be created when you run the query. You can also select whether to place the new table into the current database, or into another database by selecting the desired option button. If you select “Another Database:,” then you will have to click the “Browse…” button and use the dialog box that appears to select the database file into which you want to place the new table. When you have selected where to place the new table, click “OK” in the “Make Table” dialog box.

Once you are ready, you can run the query to create a new table. When you run a “Make Table” query that was based on a “Select” query, Access will delete the “Select” query. So be sure to save a copy before running the “Make Table” query. A dialog box will remind you of this if you forget. Next, you will see a dialog box appear that confirms how many rows you are about to paste into a new table. Click the “Yes” button in the dialog box to paste the selected rows and fields into a new table.

 

Update Queries

If you want to make large-scale updates to the data in your Access tables based on a specified criteria, you can create “Update” queries to update selected field values based on whether or not the record matches a specified criteria. For example, if you needed to update area codes in a phone number field based on whether or not the phone number contained a specified exchange, you could accomplish that with an update query.

Unlike the ‘select’ queries that we have created so far, when creating update queries all that you need to enter into the QBE grid are the actual fields that you wish to update and the fields that are needed for criteria purposes.

If you do not want to update all of the records in the selected table when you run an update query, then you must specify criteria by which to update the records. That way, when you run the update query you will apply the specified updates to only those selected records that match the given criteria.

To create an update query, first create a new query in design view. Then click the “Update” button in the “Query Type” group of the “Design” tab in the “Query Tools” contextual tab. When you do this, the layout of the QBE grid will change slightly. You will now see a new row appear in the QBE grid named “Update To:.” You then add only the fields from the selected table that you need to update and any fields needed for criteria purposes, and place them into the QBE grid. After that, you enter the value to which you want to update the desired field into the “Update to:” row of the QBE grid, under the field which you wish to update.

Next, you apply the criteria by which you wish to select the records to be updated into the other field or fields, as needed. You can then run the query to begin the update.

 

Append Queries

You can use append queries as a way of “copying and pasting” records from one table to another table, based on whether or not the records match a specified criteria. You can only append data from table fields to other table fields that share the same (or a compatible) “data type.” So, for example, you may append a “number” field into another “number” field. However, you cannot append a “number” field into a “text” field. The more similar the two fields are, the easier it will be for you to append the data.

If the fields have the same field name, the data is automatically selected to be appended into the field with the same name in the destination table. However, as long as the fields have common “Field Sizes” so you don’t lose appended data, and share the same data types, you can easily specify to which fields the copied data will be appended, even if they don’t share a common name. You can specify into which fields in the destination table the fields from which you are appending will be placed when you create the append query in design view.

To append only selected records from the original table into the destination table, you must use selection criteria in the appropriate fields of the QBE grid when you are creating this query. That way, only the records that meet the specified criteria will be appended to the destination table.

To create an append query, first create a query in design view. Then click the “Append” button in the “Query Type” group of the “Design” tab in the “Query Tools” contextual tab in the Ribbon. Doing this will launch the “Append” dialog box where you can select the name of the table into which you want to append the data from the “Table Name:” drop-down. You can also select whether to append the new data into a table in the current database, or another database by selecting the desired option button. If you select “Another Database:,” then you will have to click the “Browse…” button and use the dialog box that appears to select the database file which contains the table into which you will append the data. When you have selected the table into which to append the data, click “OK” in the “Append” dialog box.

You can then add the fields into the QBE grid that you wish to append into the table that you just selected. Next, click into the “Append to:” row in the QBE grid and select the field in the destination table into which you wish to append the selected field’s data from the drop-down menu of available field choices. In the “Criteria:” row, enter the criteria by which you wish to filter the records which are appended.

After you have select the field to append and into which fields they should be appended, you can run the query to append the data. You will see a dialog box appear, telling you how many rows you are about to append. If this is correct, click “Yes” to continue to append the selected records. If this looks incorrect, you can click “No” to abandon the appending and double-check your criteria.

 

 

 

For more query help, check out our Access tutorial.

Tagged under: 2010, 2013, access, access 2010, access 2013, class, course, database, help, how-to, microsoft, queires, query, relational, training, tutorial

About Keeley Byrnes

Keeley is our Director of Marketing and has been with TeachUcomp since 2010. Keeley manages all aspects of our Marketing Department, acts as liaison for our reseller and affiliate partners and also authors courses in software and taxation.

What you can read next

Introduction to Financial Statements- Tutorial: A picture from our video lesson, titled
Introduction to Financial Statements- Tutorial
The Smudge Tool in Photoshop Elements- Instructions: A picture of a user applying the Smudge Tool in Photoshop Elements.
The Smudge Tool in Photoshop Elements- Instructions
Change the Theme in Outlook on the Web- Instructions: A picture of a user changing the theme in Outlook on the Web.
Change the Theme in Outlook on the Web- Instructions

 

Recent Posts

  • A picture showing how to use comments in HTML code.

    Comments in HTML – Instructions

    Overview of How to Add Comments in HTML        ...
  • A picture showing how to use the Pre tag in HTML.

    Pre Tag in HTML – Instructions

    Overview of How to Use the Pre Tag in HTML     ...
  • A picture showing the Start Personalization settings that let you customize the Start menu in Windows 11.

    How to Customize the Start Menu in Windows 11 – Instructions

    Overview of How to Customize the Start Menu in ...

Categories

Archives

  • GET SOCIAL

© 2001-2022 Copyright TeachUcomp, Inc. All rights reserved.

TOP
Sale! $49 All-Access 1 Days 7 Hours 58 Minutes 45 Seconds      $199 $49 Entire Library!
See Deal