Access 2010 More Query Design Options

Author: mety Labels::


Access 2010

More Query Design Options

Introduction

Access 2010
Access 2010 offers many options that let you design and run queries that return exactly the information you're looking for. For instance, what if you need to findhow many of something exists within your database? Or what if you would like your query results to automatically be sorted a certain way? If you know how to use Access's query options, you can design almost any query you want.
In this lesson, you'll learn how to modify and sort your queries within Query Design view. You'll also learn how to use the Totals function to create a query that can perform calculations with your data. You'll also learn about additional query-building options offered in Access.
We will be showing you how to design and run queries with examples from our sample database. If you would like to follow along, download example and use it to follow the procedures demonstrated in this lesson.

Modifying Queries

Launch video!Watch the video (2:09). Need help?
Access offers many options for making your queries work better for you. In addition to modifying your query criteria and joins after you build your queries, you can also choose to sort or hide fields in your query results.
Watch the video to learn more about modifying your query, including sorting it and hiding fields.

To Modify your Query:

When you open an existing query in Access, it is displayed in Datasheet View, meaning that you will see your query results in a table. To modify your query, you must enter Design View, the view you used when creating it. There are two ways to switch to Design View:
  • On the Home tab of the Ribbon, click the View command. Select Design View from the drop-down menu that appears.
    Switching to Design View with the View command on the RibbonSwitching to Design View with the View command on the Ribbon
  • In the bottom-right corner of your Access window, locate the small view icons. Click the Design View icon, which is the icon farthest to the right.
    Switching to Design View using the View IconSwitching to Design View using the View Icon
Once in Design View, make the desired changes, then select the Run command to view your updated results.
You may notice that Access offers other query views, like Pivot Table ViewPivot Chart View, and SQL View. You can ignore these-- these views permit advanced functions that you will not need to use for this tutorial or for most Access functions.

Sorting Queries

Access allows you to apply multiple sorts at once while you're designing your query.  This allows you to view your data exactly the way you want, every single time you view it.
A sort that includes more than one sorted field is called a multi-level sort. A multi-level sort allows you to apply an initial sort, then further organize that data with additional sorts.  For instance, if you had a table full of customers and their addresses, you might choose to first sort the records by city, then further sort them alphabetically by last name.
When more than one sort is included in a query, Access reads the sorts from left to right. This means that the leftmost sort will be applied first. So for instance, in the below example, the customers will be sorted first by the Citythey live in and then by the Zip Code within that city.
    A multi-level sort. The records will be sorted by City first.A multi-level sort. The records will be sorted by City first.

To Apply a Multi-Level Sort:

  1. Open the query, and switch to Design View.
  2. Locate the field you would like to sort first. In the Sort: row, click the drop-down arrow to select either anascending or descending sort.
    Applying an Ascending sort to a fieldApplying an Ascending sort to a field
  3. Repeat the process in the other fields to add additional sorts. Remember, the sorts are applied from left to right, so any additional sorts must be applied to fields located to the right of your primary sort. If necessary, you can rearrange the fields by clicking a field and dragging it to a new location.
    A multi-level sortA multi-level sort
  4. To apply the sort, click the Run command.
    The Run Query commandThe Run Query command
  5. Your query results will appear with the desired sort.
    The sorted query resultsThe sorted query results
You can also apply multi-level sorts to tables that don't have queries applied to them. On the Home tab on the Ribbon, select the Advanced drop-down command in the Sort & Filter group. Select Advanced Filter/Sort, and create the multi-level sort as you normally would. When you're finished, click the Toggle Filter command to apply your sort.
    The Advanced Filter/Sort commandThe Advanced Filter/Sort command

    Hiding Fields within Queries

    Sometimes you might have fields that contain important criteria, but you might not need to actually see the information from that field in the final results. For example, take one of the queries we built in our last lesson-- a query to find the names and contact information of customers who had placed orders. We included Order ID numbers in our query, since we wanted to make sure that we only pulled customers who had placed orders.
    However, we really didn't need to see that information in our final query results. In fact, if we were just looking for customer names and addresses, seeing the order number mixed in there too might have even been distracting. Fortunately, Access makes it very easy to hide fields while still including any criteria they contain.

    To Hide a Field within a Query: 

    1. Open the query, and switch to Design View.
    2. Locate the field you would like to hide.
    3. Click the checkbox in the Show: row to uncheck it.
      Unchecking a field to hide itUnchecking a field to hide it
    4. To see the updated query, select the Run command. The field will be hidden.
    To unhide a hidden field, simply return to Design View and click the checkbox in the field's Show: row again.

    More Types of Queries

    Launch video!Watch the video (2:09). Need help?
    By this point, you should understand how to create a simple one- or multi-table query using multiple criteria. Additional queries offer you the ability to perform even more complex actions with your database. One of these is the totals query, which lets you perform calculations with your data.
    Watch the video to learn how to create a totals query.

    Totals Queries

    Sometimes, setting simple criteria won't give you the results you need, especially when you're working with numbers. You may want to see your query results grouped or counted in some way. Access 2010 offers several options that make these functions possible. Perhaps the easiest of these is the Totals command.
    When you use the Totals function in your query, the data in your fields will be grouped by value, meaning that all items of one type are listed together. For instance, in a totals query about the items sold at our bakery, each type of item sold would be listed on a single row, no matter how many times that item had been sold.
    Once your records are grouped, you can perform calculations with them. These calculations include:
    • Count, which counts the number of the same items in a field
    • Sum, which adds the numbers in that field
    • Average, which finds the average of the numbers that occur in that field
    • Maximum, which returns the highest value that has been entered in that field
    • Minimum, which returns the lowest value that has been entered in that field
    • First, which returns the first, or earliest, value that has been entered in that field
    • Last, which returns the last, or most recent, value that has been entered in that field

    These calculations will apply to the rows containing your grouped items. For example, if you decided to use Sum to find out how many of each item on a menu has been ordered, you would get a subtotal for each item in your query, not a grand total of all the items combined. 
    f 
    To add a calculation like a grand total to your query or table, review the instructions for creating a Totals row in our Modifying Tables lesson.  

    To Create a Totals Query:

    1. Create or open a query you would like to use as a totals query. For our example, we want to find the total number we've sold of each of our menu items, so we'll use a query showing us all of the menu items we've sold. If you want to follow along in our database, open the Menu Items Ordered query.
    2. In the Query Design tab, locate the Show/Hide group and select the Totals command.
      The Totals CommandThe Totals Command
    3. A row will be added to the table in the Design Grid, with all values in that row set to Group By. Select the cell in the Total: row of the field you would like to perform a calculation on, and click the drop-down arrow that appears.
      Selecting the totals row of the field we want to perform a calculation onSelecting the totals row of the field we want to perform a calculation on
    4. Select the calculation that you would like to be performed in that field. In our example, we want to add the quantities of products we've sold, so we'll select the Sum option.
      Setting the Totals calculation to SumSetting the Totals calculation to Sum
    5. When you are satisfied with your query design, select the Run command on the Query Tools Design tab torun the query.
      The Run Query commandThe Run Query command
    6. The query results will be displayed in the query's Datasheet View, which looks like a table. If desired, saveyour query by clicking the Save command in the Quick Access Toolbar. When prompted to name it, type in the desired name and click OK.
      Saving the Totals query. Note the sums in the far-right field.Saving the totals query. Note the sums in the far-right field.

      More Query Options

      We offer mini-lessons on creating additional types of queries in our Extras section. Below is a list of the queries we currently cover.
      • Parameter Query
        parameter query allows you to create a query that can be updated easily to reflect a new criterion, orsearch term. When you open a parameter query, Access will prompt you for a search term, and show you  query results that reflect that search.

        How to Create a Parameter Query

        parameter query is one of the simplest and most useful advanced queries you can create. They allow you to create a query that can be updated easily to reflect a new search term. When you open a parameter query, Access will prompt you for a search term, and show you query results that reflect that search.
        When you’re running parameter queries, search terms act as variable criteria, which are query criteria thatchange each time you run the query. For instance, say that we own a bakery and want to create a query that will quickly look up orders that were placed on a certain date. We could create a parameter query with variable criteria in the Date field. That way, each time we run the query, a dialog box will appear to prompt us to enter the date we’d like our query to search for.
        A prompt in a parameter queryA prompt in a parameter query
        We’ll enter the date we want, then Access will run the query using the date we entered as a search term.

        To Create and Run a Parameter Query:

        1. Create a query as you normally would, modifying the table joins if necessary, selecting the fields to include in your query, and adding any non-variable criteria to the appropriate fields in the Criteria: row.
        2. Locate the field or fields that you would like to include variable criteria, and place your cursor in the Criteria:row.
        3. Type the phrase you would like to appear in the prompt that will pop up every time you run your query. Make sure to enclose the phrase in brackets [ ]. For example, in our parameter query that searches for orders placed on a certain date, we might type our criteria like this: [What date?].
        4. On the Query Design tab, click the Run command to run your query. A dialog box will appear with the prompt you specified. Enter your search term, and click OK to view your query results.
        5. Running a parameter criteriaRunning a parameter criteria
        To run an existing parameter query, simply open it.

        Tips for Writing Parameter Queries

        • Ideally, the prompt you create for your query should make it clear what type of information the search term should be, and what format it should be entered in. For example, to guarantee that people enter a search for a date in the format we use in our database, we could write the following in the Criteria: row of the Date field:[What date? (mm/dd/yy)] .
        • A more detailed parameter promptA more detailed parameter prompt
        • The simplest parameter query will give you an exact match criteria, meaning that the query will search for theexact text you enter in the prompt. However, you can turn any type of criteria into a variable criteria. Simply type your prompt text in brackets in the part of the criteria where you would normally put a search term.

          For example, in a normal query, we could find orders that were placed between two dates by using the criteriaBetween x AND y, and replacing the x and y with the first and second dates, respectively. To turn this into a parameter criteria, we would simply replace the x and y with the text we want to appear in the prompt. Our variable criteria might look like this: Between [Enter the start date:] AND [Enter the ending date:]. These prompts would appear:
        • A parameter query using more complex criteriaA parameter query using more complex criteria
  • Find Duplicates Query
find duplicates query lets you find all duplicate records in your database, so that you can delete them.  Duplicate records can negatively affect the integrity of your database.

How to Create a Find Duplicates Query

find duplicates query allows you to search for and identify duplicate records within a table or tables. A duplicate record is a record that refers to the same thing or person as another record.
Not all records containing similar information are duplicates. For instance, records of two orders that were placed on different dates but contained identical items would not be duplicate records. Likewise, not all duplicate records contain completely identical information. For example, two customer records could refer to the same person but include different addresses. The record with the out-of-date address would be the duplicate record.
Why is getting rid of duplicate records so important? Consider the example above. If we had multiple records for one customer, it would be difficult to view an order history for him, since that information would be spread across many unlinked records. We might even deliver his order to the wrong address if the person entering the order information selects an outdated record. It’s easy to see how having duplicate records can undermine the integrity and usefulness of your database.
Fortunately, Access makes it easy to search for and locate potential duplicate records. Note that Access won’t delete the records for you or help you figure out which one is current-- you’ll have to do those things for yourself. If you’re familiar with the data in your database, though, getting rid of duplicate records will be a manageable task.

To Create a Find Duplicates Query:

  1. Select the Create tab on the Ribbon and locate the Queries group.
  2. Click the Query Wizard command. 
  3. The Query Wizard CommandThe Query Wizard Command
  4. The New Query dialog box will appear. Select Find Duplicates Query from the list of queries and click OK.
  5. Choosing to create a find duplicates queryChoosing to create a find duplicates query
  6. Select the table you want to search for duplicate records and click Next. We’re searching for duplicate customer records, so we’ll select the Customers table.
  7. Selecting the table to search for duplicatesSelecting the table to search for duplicates
  8. Choose the fields you wish to search for duplicate information by selecting them, then clicking the right arrow button . Only select fields that should not be identical in non-duplicate records. For instance, since we’re searching for duplicate customers, we’ll only select the First Name and Last Name fields, as it’s unlikely that multiple people with the exact same first and last name would place orders at our bakery. When you’ve added the desired fields, click Next.
  9. Selecting the fields to search for duplicate informationSelecting the fields to search for duplicate information
  10. Select additional fields to view in your query results. Choose fields that will help you distinguish between the duplicate records and choose which one you want to keep.In our example, we’ll add all of the fields relating to customer addresses, plus the email address and phone number fields, as records with identical customer names might contain non-identical information in these fields. When you’re satisfied with the fields you’ve chosen, click Next.
  11. Selecting the other fields to view in our resultsSelecting the other fields to view in our results
  12. Access will suggest a name for your query, but if you’d like, you can type in a different name. When you’re satisfied with the query name, click Next to run your query.
  13. Naming the queryNaming the query
  14. If Access found any duplicate records in your query, they will be displayed in the query results. Review the records and delete any outdated or incorrect records as needed.
  15. Duplicate records in the query resultsDuplicate records in the query results

Tips for Resolving Duplicate Records

  • Save your duplicate records queries and run them often.
  • Investigate potential duplicate records by looking at linked data in other tables. You can do this by searching for these records’ record ID numbers in related tables. Is one record linked to mostly old orders while another contains recent ones? The latter is likely to be the current one.
  • Once you decide which record to delete, make sure you won’t be losing any information you might need. In our example, before we deleted our duplicate record, we found all of the orders linked to that record’s ID numberand replaced it with the ID number of the record we decided to keep.

    Other Query-Building Resources

0 comments |

Labels

Blog Archive

Powered by Blogger.

I made these pages for me and my friends to help solving the problem we face regarding Computer & internet, if anyone wants me to answer a question or find out about some information please send me email and I will try to reply.*P.S. some of the article I wrote and the other I found on the internet I posted them in sprit of learning and shearing, please forgive me if you found something you don’t want to be in my blog, email me and I will delete them. Thank you for your interest in my pages.امل نجم Amal Nagm

banner 1 banner 2