1. Open the file "JSP Recruiters".
2. Click Create / Query Wizard
Click OK
Click Next
Click Finish
You will get
Close the query.
Access Chapter 2
Querying a Database
I. Display only three fields, Client Number, Client Name, and Recruiter Number from Client table.
1. Open the file "JSP Recruiters".
2. Click Create / Query Wizard
You will get
Close the query.
II. Using a Criterion in a Query: Display the Client Numbers and Client Names whose Recruiter Number is 24.
1. Right-Click Client Query / click Design View
set 24 for criteria of Recruiter Number
Click
to get
III. Create a Query in Design View:
1. Display Client Number, Client Name, Amount Paid and Current Due whose Client Number is "FD89".
a.. Click Create / Query Design / Add /Close
b. Drag the lower edge of the field box down to see all fields
c. Double -Click the selected fields.
Type "FD89" for criteria.
View it.
2. Display Client Number, Client Name, Amount Paid and Current Due whose Client Name starts with "Be".
Back to Design View and type Be* (Wildcard) for criteria
View it
3. Display Client Number, Client Name, Amount Paid and Current Due whose City is "Berridge".
(Using Criteria for a Field Not Included in the Results)Back to Design View
View it
4. Create a Parameter Query.
Type [Enter City] as the new criteria.
View it
Click OK.
Close and save.
5. Find all records with 0 at Current Due.
6. Find all records with Amount Paid >20000.
7. Find all records with Amount Paid >20000 and Recruiter Number 21.
8. Find all records with Amount Paid >20000 or Recruiter Number 21.
9. Delete all fields in the Design View of a Query.
Back to Design View. Highlight all column fields by clicking just above the field name with SHIFT + click for more columns.
Hit Delete key to clear all fields.
10. Display City in ascending order
Omit Duplicates:
Click on the second field which is empty.
Under the Design Tab, click Property Sheet.
Change Unique Value to yes.
You will get
11. Find the list with Client Number, Client Name, Recruiter Number and Amount Paid.
You have to sort both Recruiter Number and Amount Paid in ascending order.
12. Create a Top-Values Query
You get
Close query without saving.
13. Join Tables
Click Create / Design View / Add both table in.
You will get
Save the query.
14. List recruiter's all clients included none.
Right-Click on the join line / click Join Properties
Click Option 2.
View it
Close and Save Query.
Click Create / Report Wizard / Send all fields.
15.Create a query with calculation field.
Right-Click on the first open column / Zoom / Type the following information.
Click OK.
View it
16. Change Captions
Back to Design View / Right-Click on Amount Paid / Property/ change caption.
17. Calculate Statistics:
a. Click Create / Query Design View / Add Client table / Double click Amount Paid
b. Click Total button
c. Click Total List
d. Click Avg
e. View it
18. Using Criteria in Calculating Statistics
a. Find the average amount payments of recruiter number21.
View it
b. Find the average amount payments of each recruiter
View it
19. Create a Crosstab Query
Click Create / Query Wizard / Crosstab Query Wizard /OK
Click Next
Click Next
Click Finish
