en uk

Creating an unmatched query Microsoft Access 2007

    Access can store huge amounts of data, but the more data you store, the harder it can be to view your data. To help you organize your data, you can divide data into separate tables. One table may contain a list of customers, and a second table may contain a list of salespeople.

    When you store data in separate tables, each table may share one or more common fields. For example, a table containing customers may contain a SalesPerson field that shows which salesperson deals exclusively with which customer. A second table listing salespeople could contain the Customer field (along with additional information such as each salesperson's phone number, address, sales region, and so on).

    An unmatched query examines two (or more) database tables to look for missing information. For example, you could also use an unmatched query to find customers who haven't ordered anything in the past six months, sales regions that haven't ordered certain products, or salespeople who have not been assigned to a sales region. Basically, an unmatched query can help you find missing pieces or holes in your entire database file.

    To create an unmatched query, follow these steps:

    1. Click the Create tab.

    2. Click the Query Wizard icon in the Other group.

    The New Query dialog box appears (refer to Figure 17-8).

    3. Click Find Unmatched Query Wizard and then click OK.

    The Find Unmatched Query Wizard dialog box appears, asking you to choose a database table that contains the unmatched records you want to find.

    4. Click a database table and then click Next.

    Another dialog box appears, asking you to choose a database table that contains at least one field that also appears in the table you chose in Step 3.

    5. Click a second database table and then click Next.

    Another dialog box appears, asking you to identify the field that both database tables have in common, as shown in Figure 17-19.

    6. Click the common field that both database tables share.

    7. Click on the gray Creating an unmatched query Microsoft Access 2007Button that appears between the two fields and then click Next.

    Creating an unmatched query Microsoft Access 2007

    Figure 17-19:

    To find unmatched records, you must first identify the fields both database tables share.

    A dialog box appears, asking you to identify the fields you want to display from the database table you chose in Step 4, as shown in Figure 17-20.

    8. Click a field and then click the > button. Repeat this step for each additional field you want to display.

    9. Click Next.

    A dialog box appears, asking you to give your query a descriptive name.

    10. Type a descriptive name in the text box and then click Finish.

    Access displays your query results, which show you only the data in fields you selected in Step 8.

    11. Click the Office Button and choose Save to save your query.

    Creating an unmatched query Microsoft Access 2007

    Figure 17-20:

    You can choose which fields you want to display in your query.

    Viewing and deleting queries

    Each time you create and save a query, Access stores it for future use. After you create and save a query, you can add or delete data from your tables and then apply your queries on the newly modified data.

    To view a query, follow these steps:

    1. Click the downward-pointing arrow in the left pane.

    A pull-down menu appears, as shown in Figure 17-21.

    2. Click Queries.

    Access shows your list of queries.

    3. Double-click the query name you want to view. Access displays your chosen query.

    4. Right-click the query tab; when a pop-up menu appears, choose Close.

    In case you need to rename your query to give it a better descriptive name, follow these steps:

    1. Click the downward-pointing arrow in the left pane.

    A pull-down menu appears (refer to Figure 17-21).

    Creating an unmatched query Microsoft Access 2007

    Figure 17-21:

    You can view your queries through a pull-down menu in the left pane.

    2. Choose Queries.

    Access shows your list of queries.

    3. Right-click a query name; when a pop-up menu appears, choose Rename.

    Access highlights the query name.

    4. Type a new name and then press Enter.

    Eventually, you may no longer need a query. To delete it, follow these steps:

    1. Click the downward-pointing arrow in the left pane.

    A pull-down menu appears (refer to Figure 17-21).

    2. Choose Queries.

    Access shows your list of queries.

    3. Right-click a query name; when a pop-up menu appears, choose

    Delete.

    A dialog box appears, asking whether you really want to delete your query.

    4. Click Yes (or No).




BACK NEXT TOP

Site is a private collection of materials and is an amateur informational and educational resource. All information is obtained from public sources. The administration does not apply for authorship of the materials used. All rights belong to their owners.