Compound Queries and Database Utilities

1. Compound Queries and Database Utilities Chapter Four

2. Using Queries to Analyze Data • Queries provide the ability to perform complex analysis on table data • Queries allow subsets of data to be retrieved for use in reports • Queries can provide calculation and selection abilities when user is restricted to a subset of the data • The ability of queries to draw data from several related tables makes the query a powerful database tool

3. Specifying Complex Query Criteria • Compound Queries – multiple conditions specified for data retrieval • Conditions can be simple or complicated Using Conditional operators Between, In and Like • Between – specifies a range for comparison. All records with values falling in the range will be selected • In – specifies a list of values, all records with values in the list are chosen • Like – uses pattern matching with wildcards

4. Examples of the use of Conditional Operators - Between Query created with all fields from the CustomerOrdersJoin query Range defined using Between Operator Result of the Query

5. Examples of the use of Conditional Operators - In Query created with all fields except CstmrID and Notes from the Customer table Range defined using In Operator Result of the Query

6. Examples of the use of Conditional Operators - Like Query created with LastName, FirstName and City from the Customers Table Range defined using Between Operator Result of the Query

7. OrderDate and QuantityOrdered criteria joined with And Using Logical Operators – The And Operator Results of Query – selected records have order dates between 1/1/2001 and 3/31/2001, AND Order Quantities of greater than 5 3 records

8. OrderDate and QuantityOrdered criteria joined with Or Using Logical Operators – The Or Operator Results of Query – selected records have order dates between 1/1/2001 and 3/31/2001, OR Order Quantities of greater than 5, or both 35 records

9. Using Logical Operators – the Not Operator City criteria specifies all cities except Coatesville, Greencastle and Monroe Results of Query – selected records contain all cities except those specified in the Criteria

10. Analyzing Data Using Crosstab Queries • Select queries allow you to group data by values in one or more fields, and calculate summary statistics for those groups This select query groups values by State, then Product, and calculates the number of orders in each subgroup This select query groups values by Product, then State, and calculates the number of orders in each subgroup

11. Creating A Crosstab query • Select the Query object, then click New and select Crosstab query Wizard • Select the table or query that contains the data you wish to work with • Select the fields to be displayed as row and column headings • Select the field and function to be included in the body of the table

12. Creating a Crosstab Query (continued) State field used as column heading Product description field used as row heading Sums of Quantity ordered will be calculated for each cell

13. Results of the Crosstab query States as column headings Products as row headings Summary column, giving totals of Product categories Design grid for the query shows a new design criteria has been added, the Crosstab criteria

14. Modifying Table Definitions Regardless of how well a database has been designed, some modifications may be necessary. Modifications include: • Adding a field • Deleting a field • Moving a field • Changing field attributes • Building Lookup fields • Creating Input Masks

15. Clicking or selecting the State field and selecting Rows from the Insert menu will insert a new field between City and State You can enter a new field at the end of this field list Adding a Field • Done in Design View • Can be added at the end of the list (simply enter field) or between two existing fields. • To add a new field between two existing fields: • Click on the field that currently has the desired location of the new field • From the Insert Menu, select Rows

16. Deleting a Field • Deleting a field is very simple – simply select the field in design view using the record selector, and press the Delete key on your keyboard • Repercussions of deleting a field • Deletes all data that was held in the field • Once you switch out of Design view, the Delete is permanent, cannot be Undone Back up Tables before you delete fields from the table design!

17. Moving a Field • Changing the order of fields does not impact data functionality • Usually the Primary Key is located in the leftmost column of a table. • To move a field, select the field to be moved in design view by clicking the record selector, then drag the selected field to the desired location.

18. Changing Field Attributes • Has the potential to destroy data or invalidate other database objects • Changing field name can affect other objects such as queries that refer to the field • Reducing field size will result in truncated data if existing data exceed the new size

19. Changing Field Attributes (concluded) • Changing data type can lead to Conversion errors FirstName field changed to number from text – Access alerts the user to a conversion error when a save is attempted on the table

20. Building Lookup Fields Lookup field – provides users a list of possible values to choose from for a field, rather than requiring the user to enter the data themselves. • Reduces data entry error by eliminating typing errors • Set up through use of a wizard

21. Select the source of lookup data Open table in design view, change data Type of field to Lookup Wizard Adjust column width and hide key column if desired Select fields to be used in lookup Specify whether you will enter the lookup values yourself, or get them from a list Using the Lookup Wizard

22. Using the Lookup Wizard • The last wizard step has you name the lookup column and click the Finish button. The result is shown below: CstmrID field is now a lookup field. Clicking gives the user a dropdown list with values as set up in the Wizard

23. Creating Input Masks • Input masks are used to improve data entry for Text, Date, Number and Currency fields, by providing a pattern of input for the user to follow • Input masks are strings of characters which specify what is displayed to the user, and what is accepted as data input • Input masks can either be created using the Input Mask Wizard (text and data fields) or typed in manually (numeric and currency)

24. Input Masks using the Input Mask Wizard Select field Click ellipis to initiate wizard Click Input Mask Text box

25. Input Masks using the Input Mask Wizard (concluded) Format controls display of existing data Make changes to input mask and placeholder characters if desired Select the input mask, click Next Input mask for data entry

26. Compacting and Repairing a Database • Compacting – the process of releasing unused space from a database • Access can also detect and repair many of the errors resulting from normal operations, such as corruption to tables, forms, reports and modules • Access checks for file corruptions when loading a database, and will prompt if problems are found

27. Compacting and Repairing a Database • If the database is open: • Select Database Utilities from the Tools menu, then click Compact and Repair Database • If the database is closed: • Select Database Utilities from the Tools menu, then click Compact and Repair Database Select database to compact and repair, and Click Compact Specify a name and location for the compacted database, then click Save

28. Backing up and Restoring a Database • Backing up is a critical activity if an organization relies in any way on the contents of the database. • Frequency of database backups should be determined by how critical the data is, and how much the data changes from day to day, week to week. • It is best to use tools specifically designed for backup, such as Windows 2000 Backup and Recovery tools, rather than simply making copies of the database using other Windows tools

29. Backing up and Restoring a Database System Tools Start Programs Accessories Backup

30. Backing up and Restoring a Database Select Backup Wizard, and then follow steps to specify file to backup, and location for backup To restore a file, Select Restore Wizard, and then follow steps to specify file to restore.

31. Converting Databases Converting an Access97 database