Chapter 5
Creating Advanced Queries and Enhancing Table Design.

Student Learning Outcomes
1. Review table and object nameing standards
2. Use the Like, In, Not and & operators
3. Filter data using an AutoFilter
4. Use the Iif function to assign a conditional value to a calculated field in a query.
5. Create a paramenter query
6. Use query wizards tocreate a crosstab query, a find duplicates query, and a find unmatched query.
7. Create a top values query
8. Modify table designs using lookup fields,inut masks, and data validiation rules
9. Identify object dependencies
10. Review a Memo fields properties
11. Designate a trusted folder.

Importand Points.
1. Review table and object nameing standards

Object Prefix Name Type of object
tbl table
frm form
qry query
rpt report
mac macro
mod module

2. Using Pattern Match Queries
a. A pattern match select records with a value for the designated field tht matchces the pattern of a simple condition value.
b. The like comparison operator. selects records by matching field values to a specific pattern tht includes one or more of the wild card cahracters.
    * - represents a string of characters
    ? - represents a single character.
    #- represents any single digit
    like"616*" in the phone field retirives all records beginning with the area code of 616.
c. In(list)
    In("Holland","Rockford","Saugatuck") in the city field retrives all records from Holland, Rockford, Saugatuck.
    Not In( "Holland","Rockford","Saugatuck") in the city field retrives all records but Holland, Rockford, Saugatuck.
3. Using AutoFilter
a. Open the qrySmallContractsOrWinterSignings in design view and run the query and then click the arrow on the city column to display the AutoFilter menu.  You will see:
Auto filter dialog box
b. Click select all to deselect all the check boxes, then click East Grand Rapids and Grand Rapids.  The autofilter will only include these citites. You will see:
Result of autofilter
4. Using the Iif function. Click create tab, click Query Design, click tblcustomer, click add, click close and drag all fields onto the query grid into the second column.  The first will be used for the calcualted field using the iif function.
a. Right click the blank field to the left of the CustomerId, click Build you will see:
Build Dialog box
b. In the box type Customer: and press the space bar - make sure the colon is there.
c. Double click functions in the expression element box, click built in functions, click program flow, double click Iif function.  You will see:
expression builder with parameters
d. Click <<Expr>> and press the Delete key to get rid of first place holder.
e. Click <<expressions>> in the expression box, click instpection in middle colum double click isNull in the expressoio value then type company You will see:
expession builder

f. Click on the truepart and type in LastName & " " &Firstname
g. Click on the falsepart and type in Company.  You will see:
expression builder with parameters
h. Click OK. and run the query.  You will see:
query datasheet view
i. Save the query as qryCustomkersByName.

5. Create a parameter Query - dispalys a dialog box that prompts the user to enter one or more cireterial values.
a. copy the qrycustomrbyname and rename it qrycustomerbynameparameter and open the qrycustomerbynameparameter.and in the city creterian type in [Type the city;] you will see:
parameter query by city
b. Save and run the query.  You will see:
parameter dialog box.
c. Type in Holland and press enter or click ok.  You will see:
using parameter in query for city
d. Modify the parameter query:  Switch to design view.
e. Go to the city field and type in Like[type in city] & "*" to modiy the query.  Run the query and put S in the parameter box.  You will see:
Parameter query using like operator
f.  All the records with a city field beginning with S are selected.
6. Create a cross tab query.find duplicates, unmatched query. Cross tab ueries use the aggregate functions shown below to perform arithmetic operations on selected records.l

Aggregate Functions Definition
Avg Average of the field values
Count Number of the nonnull field values
First First field value
Last Last field value
Max Highest field value
Min Lowest field value
StDev Standard deviation of the fild values
Sum Total of the fild values
Var Varience of the field values

a. Crosstab queries displays only one summarized record for each unique field value.
b. Click create, Click query Wizard, click cross tab query wizard, click  You will see:
Cross tab query wizard

c. Click ok you will see:
crosstab query dialog box
d. click queries  you will see:
Cross tab query dialog box
e. Click qrycustomerandinvoices and click next.  You will see:

crosstab query

f. Click city and send it across. Click next  Click invoice paid.You will see:
crosstab query

g.Click next: you will see:
crosstab query dialog box 5
h. Click InvoiceAmt, click sum, click yes, include row sum then click next you will see:
crosstab query nameing dialog box
i. remove the underline and click finish.  You will see:
Crosstab query results
j. Change crosstab query column headings:Go to design view. Click the invoicePaid Field box and open the zoom dialog box.  You will see:
Zoom dialog box
k. Delete the InvoicePaid and type Iif(InvoicePaid,"Paid","Unpaid") You  will see:
crosstab query change columns headings
l. Click ok, save and run the query you will see:
Crosstab query with column modifications
m. Find duplicates  Click create, query wizard, find duplicates , click ok.  You will see:
find duplicate query wizard
n. Select the tblcontract and click next:  You will see:
duplicates query
o. Select startdate and send it over, click next  and select all the fields and you will see:

duplicate query dialog box
p. Click next: you will see:|
duplicate query dialog box
q. Type in the name qryDuplicateContractStartDates and click finish you will see:
duplicate queries datasheet view
m Unmatched query -finds all records in  a table or query that have no related records in a second table or query. Click create, query wizard, unmatched records, click ok you will see:
unmatched query dialog box
n. Click queries, click qrycustomerbyname and click next you will see:
unmatched query
o. Select tblcontract and click next.  You will see:
unmatched query
p. Click CustomerId in the qry window and customerid in the tblcontract. and click next you will see:

unmatched query
q. Send all fields to the selected fields and click next you will see:
unmatched query final box
r. Type in qrinactivecustomers and click finish you will see:
query result of unmatched customers
7. Top values query- lets you limit the nuimber of records -Open the qrylargecontractamounts in design view.you will see:

top value query
a. Select the 25% and click run.  You will see:
top 25%
b. Close but do not save the query.
8. Creating a lookup field., input mask, and data validation -a lookup field lets  the user select a value from a list of values. Insures some data intergity.
a. Open the tblcontract in design view, click the right side of the CustomerID field to dispaly the data types  and click Lookup Wizard at which time a messge will pop up saying you have to delete  the relationship between tblcustomer and tblcontract. You will see:
error messge
b. click ok, close the table,open the  relationship window and delete the join  between  tblcustomer and tblcontract by right clicking the join line, click delete, click yes. and close the relationship window.
c. Reopen tblcontract in design view. cick on the right side ofthe customerid field and select Lookup Wizard.  you will see:
lookup wizard
d. Make sure I want the lookup fild toget values from anohter table or query and click next you will see:
lookup wizzrd step 3
e. click the queries radio button and select the qrycustomerbyname and click next you will see:

lookup wizard 4
f. Send customer and customerid accross and click next you will see:
lookup wizard 6
g. Select customer in ascending sort and click next you will see:
lookup wizard 7
h.  click between the column heading to adjust the with to accomidae the entire field.  You will have to scrool down to adjust all of the fields.
click next you will see:
lookup wizard 8
i. Select customerid and click next.  You will see:
lookup 9
j. accept the default customerId and click finish. Which will return to disign view.
k. save the table and go to datasheet view.
l. Go to contract num 3030 and click the arrow in the customer id field you will see:
lookup field 9


j. Select Ingram, Michael, save and close the table.
k. Input Mask Wizard -

Inut Mask Character Description
0 digit only must be  entered. Entry is required
9 digit or space canbe entered. Entry is optional
# digit, spac,or aplus or minus can be entered.  Entry is optional
L letter only, Entry is Required
? Letter only can be entered. Entry is optional
a letter or digit.  Entry is optionsl
& Any character or a space  must be entered.  Entry is required
C Any character or space can be entered.entry os optional
> All characters tht follow are displayed in uppercase
< all characters displayed are in lowercase
" Enclosed characters treated as literal display characters.
\ following character treated as a  literal display character. Thisis the same as enclosing a single cahracter in quotation marks
! Input maske  is displayed fromrightto left, riather thtn the fefault of left toright.
;; The character between  the first  and  second simicolon determines wheater to stroein the databasethe literal dispaly characters. bliank or 1 do not store, 0 store the literal dispaly characters.
   
l. Open the tblcustomer in design view, click the phone field, go to the input maks property click the ...  you will see:

inut mask

a. click phone number, click try it and type in 9876543210  you will see:
input mask2
b. click next:  you will see:
input box 3
c. click next you will see:
input mase 4
d. click finish you will see:
input mask
e. Change the input mask to 999\-000\-0000;;_ and press tab.  click the property update options and click update input mask  everwerer phone is used.
and click yes. Save the table and go to datasheet view.   You will see:
input mask 7

9. Identify object dependencies- exist between two objects when a change to the properties of data in one object affects the properties of datain the other object>
a. click database tools, click Object dependencies,   You will see:
object dependcies
b. here you see a list of the object dependencies.
10. Data validation rules. verifies a field value by comparing it to a constant or a set of constants. Validation Rule specifies the valid values. Validation Text specifice a message that informs the user of their error. Open the tblinvoice in design view and click the invoice amt field.  In the validation Rule type >10 and in validation text box type Invoiceamounts  must be greater than 10.   You will see:"
validation rule
a. save the table, and click yes when asded if you want to test the existing invoiceamt values. you will see:
change existing values
b. click yes. and then switch to datasheet view select $1500.00 and chane it  to 5 andjpress tabl  You will see:
validation rule box
c. click ok, hit esc to return to orginal value and close the tblinvoice.
11. Working with Memo Fields - Oepn tblcustomer in datasheet view.click the company column and press andhold down the shift key, click lastname and release the shiftkey to select company,  firstname and lastname columns in record grouup click more andthen  click frezze fields.  You  will see:
freeae fields
a. scroll to comments box for the first record and click in it.  Hold down the shift key and press f2 and release the shift key.  YOu will see the zoom box and all of the text in the comment field.
b. Save the table , switch to design view, click the comments field name, click text format box, click it arrow and sse ritch text format.  Click the append only box.  If you select yes, access will keep track of all of the revisions you make to the memo field.
 12. Designating a trusted folder.  Click file, click Options, You will see:
Trusted folder
a. Click Trust Center, click Trust center settings, click trustedd locations , click add new location you will see:

trust center settings

b. Browse to the folder you want to trust and click ok, click ok. click 0k.