Advance query and table operations

1. Review Table and object naming standards.
2. Use like, In, And not, and Or operators in queries
3. Filter data using autofilter.
4. Use the IIf function to assign a conditional value to a calculated field in a query.
5. Create a parameter query
6. Use query wizards to create a crosstab query, a find duplicates query, and find unmatched query
7. Create a top values query.
8. Modify table designs using lookup fields,input masks, and data validation rules.
9. Identify object dependencies.
10. Review a Memo field's properties
11. Designate a trusted folder.


Important Points.
1. Rules of Data Normalization 5th rule - Isolate Semantically Related Multiple Relationships. There may be practical constraints on information that justify separating logically related many to many relationships.
2. Object Nameing Standards - Tables -tbl prefex: Queires - qry prefix:Reports-rpt prefix: forms- frm prefix.
3. Object name should not have any spaces.
4. The caption property can have spaces and is used for all objects names that require spaces. 
5. For tables which have a one to many relationship, you can display records from the related table as a subdatasheet in the primary table's data sheet.
6. To display the position subdatasheet for a specific record, click the expand indicator + in the row of the record.
7. A pattern match select records with a value for the select field that matches the patter of the simple condition value.
8. The Like comparison operator selects records by matching field values to a specific pattern the includes the wildcard characters.
9. A list of values match selects records whose value for the selected field matches one of two or more simple condition values.
10. The In comparison operator lets you define a condition with two or more values.
11. A non matching value selects records whose value for the select field does not match the simple condition value.
12. The Not logical operator negates a criterion.
13. The & (ampersand) operator is a concatenation operator that joins text expressions. 
14 The Iif(condition=?,true,false ) function assigns one value to a calculated field or contro if a condition is trye and another value if the condition is false.
15. The IsNull function tests a field value or an expression for a null value.  If field or expression is null result will be true.
16. A parameter query prompts you for information when the query runs.
17. A crosstab query performs aggregate function calculations.
18. Use the crosstab query wizard for the quickest way to create a cross tab query.

Aggregate Function Definition
Avg Average of the Field Values
Count Number of non null field values
First First field value
Last Last field value
Max Highest field value
Min Lowest Field value
StrDev Standard deviation of the field values
Sum Total of the field values
Var Variance of the field values


19. Find duplicate query is a select query that finds duplicate records in a table or query.
20. Find unmatched query is a select query that finds all record in a table or query that have no related records in a second table or query.
21. Creating a  top Value query let you limit the number of records to be displayed.
22. The not logical operator negates a criterion or selects records for which the designated field does not match the criterion.
23. And logical operator requires all conditions to be true to retrieve a record.
24. Or logical operator requires only one of the conditions to be true to retrieve a record.
25. When every you create a query it is saved in Structured Query Language. 
26. A Lookup Wizard field lets the user select a value from a list of possible values.
27. Literal display characters are the special characters that automatically appear in specific positions of a field value.
28. An input mask is a predefined format used to enter and display data in a field.
29. The Input Mask Wizard tool guides you in creating a predefined format for a field.

Input Mask Character Description
0 digit only must be entered. Entry is required
9 digit or space can be entered. Entry is optional
# digiti, space, or a plus or minus sign can be entered.
L letter only can be entered, Entry is required
? letter only can be entered, entry is optional
A letter or digit must be entered. entry is required
a letter or digit can be entered. entry is optional
& any character or space must be entered. entry required
C any characterr or space can be entered. entry optional
> All characters that follow are dispalyed in uppercase
< all characters that follow are dispalyed in lowercase
" Enclosed characters treated as literal dispaly characters.
\ following characters are treated as literal display character.
! Input mask is displayed from right to left.
;; the character between the first and second semicolon determines wheteher to store in the database the literal display characters.

30. Clicking the Property Update Options button gives you the ability to Update Input Mask everywhere Phone phone field is used.
31. Property Propagation updates field properties in objects automatically when you modify a table field property.

32. Object Dependencies task pane allows up to identify the dependencies that any object we might modify will effect the rest of the database. 
33. The Validation Rule property value Specifies the valid values that users can enter in a field.
34. The Validation Text property will be displayed in a dialog box if the user enters a invalid value.
35. Table validation rule compares one field value in a table record to another field value in the same record
36. A trusted folder is a folder that you designate as trusted and where you place daabases you know are safe.
37. To designate a trusted folder: click office button, click acess options, click trust center, click trus center tettings, click trusted locations, click add new location, browse.
38. To compact and Repair: click office button, point to manage, click compact and repair database.

Best Practice:

1. Use as many lookup fields as you can to avoid data input error.