Tutorial 3
Maintaining and Querying a Database

Objectives:

a. Find, Modify, and delete records in a table
b. Learn how to use the query window in Design view
c. Create, run, and save queries
d. Update data using a query datasheet
e. Crate a query based on multiple tables
f. Filter data in a query
g. Specify an exact match condition in a query
i. Change the font size and alternate row color in a datasheet
j. Use a comparison operator in a query to match a range of values
k. Use the And and Or logical operators in queries
l. Create and format a calculated field in a query
m. Perform calculations in a query using aggregate functions and record group calculations
n. Change the dispaly of database objects in the Navigation Pane

Important Points:
1. Updating or maintaing a database is the process of adding, modifying, and deleting records in tables.
2. Navigation mode- selects an entire field value.
3. Editing mode - you can insert or delete characters in a field value.

Navigation mode and editing mode keystroke techniques
Press To move the Selection in Navigation Mode To move the insertion point in editing mode
Left Arrow left one field value a atime Left on character at a time
Right Arrow Right one field value at a time Right one character at a time
Home Left to the first field value in the record To the left of the first character in therr field value
End Right to the last field value in the record To the right of the last character in the fileld value
down or up arrow Up or down one record at a time Up or down one record a a time and switch tonavigation mode
Tab or Enter Right one field value at atime Right one field value at atime and switch to navigation mode
Ctrl-Home To  the first field value in the first record To the left of the first character in the field value
Ctrl-End To  the last field value in the last recofd To the right ofthe last character in the  field value

4. Find command allows you t search a table or queery datasheet. or a form to locate a specific field value or part of a field value. Open the contract table in datasheet view, in the find group, click the Find button.  You will see:
Find and Replace dialog box

a. In the find what type in the record number you are looking for, and click find next.  You will be taken to that record.
5. Deleting a record.  You can not delete a record in the primary table if it has releated records.  You must first delete the releated records before you can delete the record.  Go to datasheet view, select the record by click the row selector and then click the delete button in the records group or right click the record and click delete record.
6. Queries
a. Dispaly selected fields and records from a table
b. Sort records
c. Perform Calcualtions
d. Generate data for forms, reorts, and other queries
e. Update data in the tables in a database
f. Find and display data from two or more tables
e. Create paramter queries that as for input from the user
f. Query returns a record set in datasheet view, but saves in Structrued Query Language (SQL).
7. Creating Query in Design View.
a. Click create tab, click query design, You will see:
Show table dialog box
b. double click customer, and then close the show table dialog box.  You will see:
query by example grid
c. Double click customer, first, last, city, and e-mail in the customer field list.  You will see:
Query by example with selected fields

d. Run the query: You will see after adjusting the column width in the datasheet view.
Datasheet view of query
e. Click on SQL view:  you will see:
Query SQL view

f. Save the query as CustomerEmail and return to datasheet view.
g. In datasheet view you can change the contents of the table by changing the data.
8. Multitable Query:
a. Click create, Query design, double click customer and contract in the show table box, close the show table box. You will see after adjusting the length of the table display.
Multitable query\

b. Add city, company, first and last fields from the customer table and add SigningDate and ContractAmt from the contrat table. You will see:
Multitable query

c. Click on SQL view: you will see:
SQL view of customercotracts

d. Save the query as customercontracts.
9. Sorting data in a query:

Data Type Ascending Descending
Text a-z z-a
Number lowest to highest numeric value highest to lowest numeric value
Date?time oldest tomost recent date most recent to oldest date
Currency lowest to highest numeric hightest to lowest numeric value
AutoNumber lowest to highest numeric value lowest to highest numeric value
Yes/No yes(check markin check box) then no values no then yes values

a. in datasheet view you can sort on one field. In design view you can sort on multiple fields>
b. Datasheet view. click the arrow on the field name which drops down the sort list,  Choose A-Z on the city field.  You will see:
datasheet view sort on single field

c. Design View: Click on the sort arrow in the design grid for the City field you will see:
Sorting in the query design grid

d. Do the same thing to the contractamt field in descending order.Then run the query.  You will see:
query sorted on city and contractamt fields

10. filtering data- used to temporarily isolate a subset of the records.Filter by selection lets you select all or part of a field value in datasheet or form view.  filter by form which changes your datasheet to display blank fields,then you select the values. display query in datasheet view, select the first occurance of grand rapids in the city field, then click the selection button.  You will see:
filter sort dialog box
a. select equals grand rapoids.  You will see:

filter on city field

 

11. Defining Record Selection Criteria  for Queries

Access Comparison Operators
Operator Meaning Example
= equal to ="Hall"
<> not equal to <>"Hall"
< less than <#1/1/99#
<= less than or equyal to <=100
> greater than >"C400"
>= greater than or equal to >=18.75
Between... And... between two values (inclusive) Between #1/1/99# and #1/30/99#
In () in a list of values In("Hall","Wylie")
Like matches a pattern that includes wildcards like "706*"
a. Specifying an exact match in a query:  Click create, click Qyery Design button, add the customer and contract table to the grid, add company, first,last,phone,address,city and email from the company table then add the contractnum, contractamt, signingdate, and contractype from the contract table. and type Holland for the city critera.  You will see:
Exact match query

b. Run the query. You will see:
Holland city customers

c. Only holland city customers are selected.
12. Changing a datasheet's appearance.
a. click the font size arrow and click 9. 
b. in the text formatting group click alternate row color.you will see:
Alternate row colors dialog box
c. Pick the color you want:  You will see:

Alternate row colors applied to datasheet.

13. Using a comparison Operator to Match a range of values.  Make of copy of holland customers and save it as largecontractamunts and open it up in design view.
Click the contractamt creteria box and type in>=25000.  You will see:
large contracts amount query
a. Run the query.  You will see:
Large contract amounts >=25000

14. The and logical operator -  When using the and for multiple criteria all of the criteria must be true in order to select the desired record(s).
a. create a new query from the customer and contract tables.  Select the company,first, last, phone and city from the customer table and cotractamt and singningdate from the contract table.  Put lansing inthe citycriteria box and in the contractamt criteria box type>25000.  You will see:
Multiple criteria query

a. Run the query, You will see:
Key Lansing Customers query

b. Only the records that meet both criteria are selected.
15. The or logical operator: Click create, click query design, add the customer and contract table,  Add first,last,company, and city from customer and Contractamt, signingdate, and contractype from contract table.In contractAmt creteria box type <1000, in the signing date field place on the or critera line Between 1/1/2014 and 3/1/2014, click the right side of the signing date field sort box and click descending.  You will see:
Using the or operator in a query
a. Run the query.  You will see:
query using or logical operator

16. Creating a caculated field:  to perform calculations you define an expression consisting of database fields, constants, and operators.

Comparison Operators

> greater than A > B
< less than A < B
>= greater than or equal to A >= B
<= less that or equal to A <= B
= equal to A = B
<> not <> A
Between..And between two values (inclusive) Between 50 and 325
In( ) in a list of values In("Hall","Seeker")
Like matches a pattern that includes wildcards *, ? like "707*"

Logical Operators
AND, OR, XOR, NOT

AND

A B C Result
True True True True
False True True False
False False True False
False False False False

OR

A B C Result
True True True True
False True True True
False False True True
False False False False


XOR

A

B C Result
True True True False
False True True True
False False True True
False False False False


Not

A Result
True False
False True

Operator Precedence used in evaluating expressions

High ^ Arithmetic
Unary + or -
* / \ mod
+ -
<, >, <=, >=, =, <> Relational
NOT Logical
AND
Low OR and XOR
( ) can be used to overide the order of operations. The intermost () is executed first  

  Create a new query using the contract and invoice tables.  Put contactnum and contractamt from the contract table and inviceitem, invoicepaid and invoiceamt from the invoice table.Type no in the invoicpaid criteria box, remove the check mark from the invoicepaid show check box, click the blank field to the right of the invoiceamt field.  Clickthe builder button in qyery setup group on the design tab. You will see:
Expression builder dialog box

a. double click the Invoiceamt field, the type *, then type .03.  You will see:
Expression builder for invoiceamt
b. Click ok. You will see:

Query with caculated field

c. Delete Expr1 from the caculated field leaving the colon and then type Late Fee.  You will see:
Late fee

d. Formatting a calculated field.- Right cick the late fee calculated field in the design grid you will see:

calculated field properties dialog box

e.  then click properties, you will see:
properties dialog box for calculated field

e. right click the format box and then click currency and close the property box.

17. Access 2010 does have the new data type of Calculated field which allows you to stroe the results of a expression in a table.  Use caution using this field type.
a. Consumes valuable space and increases the size of the database.
b. Calculated field data type porvides limited options for creating a calcualtions.
c. If you have to upgrade you database to sql or orcal they do not support the data type and you would need to redisign your database.
d. Best to create calculated fields in a query.
18. Aggregate functions perform arithmetic operations on selected records in a database.

Aggregate Function Determines Data Type Supported
AVG Average of field values Autonumber,Currency, Date/Time, Number
Count Number of records selected Autonumber,Currency, Date/Time, Number, Memo, OLE,Text, Yes/No
Max Highest field value Autonumber,Currency, Date/Time, Number,Text
Min Lowest field value Autonumber, Currency, Date/Time, Number, Text
Sum Total of field values Autonumber, Currency, Date/Time, Number
StDev Standard Deviation of Variance Number, Currency
Var Variance of Data Number, Currency

19. Working with Aggregate Functions in table or query datasheet view.
 a. Open the contract table in datasheet view, in the records group on the home tab, click the totals button which will add a row called total at the bottom of the data sheet.Scroll to the bottom of the datasheet and go to the contractamt column and click on the arrow and you will see:

datasheet view of aggregate functions

a. click sum and you will see:
Sum aggregate function in datasheet view

20. Creating Queries with Aggregate Functions.  Create a new query in design view with the contract table.  Click the contract amount field three times, then click the totals button in the show/hide group.  You will see:
Aggregate total group in query window

a. click the right side of the total group for the 1st contractamt field and select Min, the 2sd contractamt field and select average, 3rd cotractamt select Max.  Then go to the field name contractamt for the min function and touch the home key and then type in Minimum Contract Amount: making sure that you include the colon, and do the same for Average Contract Amt:, and Miximum Contract Amt:  You will see:
aggregate funcitons deployed in query

b. Run the query you will see:
Aggregate Query
c. Save the query as contractamtstatistics

21. Using record group calcualations: Open contracamtstatistics in desing mode, cick file, click save object as and give it the name contractamtstatisticsbycity.  Retun to design view, add the customer table to the design grid.  Drag the city field from the customer table to the first column in the design grid.  You will see:
Group by on city

a. Run the query you will see:
contractamtstaticisticsbycity

b. The sql view.
SQL view of contractamtstatisticsbycity